Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access 2003 - Recaps without details

Discussion in 'Information Technology' started by Bob Watson, Nov 19, 2005.

  1. Bob Watson

    Bob Watson
    Expand Collapse
    Guest

    I have a report that gives name and age range.
    Such as

    Bob 30-35
    Bill 30-35
    Tom 36-40
    Betty 36-40
    Mary 36-40

    What I want are recaps and no details.
    Such as

    30-35 2
    36-40 3

    I cannot figure out how. "Access For Dummies"
    did not come thru in this instance.

    TIA,
    Bob
     
  2. Loading...


  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    You want to count the number of Records by AgeRange so you need to use an
    Aggregate Query (with Group By) clause on your Table with SQL String like:

    SELECT Count([YourTable].[Name])
    FROM [YourTable]
    GROUP BY [YourTable].[AgeRange]

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Bob Watson" <RMWatson3rd@bellsouth.net> wrote in message
    news:u4rf$hI7FHA.636@TK2MSFTNGP10.phx.gbl...
    >
    > I have a report that gives name and age range.
    > Such as
    >
    > Bob 30-35
    > Bill 30-35
    > Tom 36-40
    > Betty 36-40
    > Mary 36-40
    >
    > What I want are recaps and no details.
    > Such as
    >
    > 30-35 2
    > 36-40 3
    >
    > I cannot figure out how. "Access For Dummies"
    > did not come thru in this instance.
    >
    > TIA,
    > Bob
    >
     
  4. Bob Watson

    Bob Watson
    Expand Collapse
    Guest

    Thanks - that helped tremendously ... my
    actual query looks like this:

    PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    SELECT Registration.Age, Count(Registration.Age)
    FROM Registration
    WHERE (((Registration.[Date Attended])<=[End Date] And (Registration.[Date
    Attended])>=[Begin Date]))
    GROUP BY Registration.Age;


    with this query output:

    Age Expr1001

    0
    20-24 29
    25-34 125
    35-44 116
    45-64 55


    Couple of observations/questions:
    1 - the count of a "Blank Age" is not 0. Why is
    the "blank count" not working?

    2 - I suppose this output could be used as a
    "Record Source" of a Report. Can I change the
    column name of Expr1001 to "Number" ??

    Thanks in advance,
    Bob



    "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
    news:%23V%23zGzI7FHA.808@TK2MSFTNGP09.phx.gbl...
    > You want to count the number of Records by AgeRange so you need to use an
    > Aggregate Query (with Group By) clause on your Table with SQL String like:
    >
    > SELECT Count([YourTable].[Name])
    > FROM [YourTable]
    > GROUP BY [YourTable].[AgeRange]
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Bob Watson" <RMWatson3rd@bellsouth.net> wrote in message
    > news:u4rf$hI7FHA.636@TK2MSFTNGP10.phx.gbl...
    >>
    >> I have a report that gives name and age range.
    >> Such as
    >>
    >> Bob 30-35
    >> Bill 30-35
    >> Tom 36-40
    >> Betty 36-40
    >> Mary 36-40
    >>
    >> What I want are recaps and no details.
    >> Such as
    >>
    >> 30-35 2
    >> 36-40 3
    >>
    >> I cannot figure out how. "Access For Dummies"
    >> did not come thru in this instance.
    >>
    >> TIA,
    >> Bob
    >>

    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Try:

    PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    SELECT Nz(Registration.Age, "Blank") AS RegistrationAge,
    Count(Registration.Age) AS RegistrationCount
    FROM Registration
    WHERE (((Registration.[Date Attended])<=[End Date]
    And (Registration.[Date Attended])>=[Begin Date]))
    GROUP BY Registration.Age;

    Presumably for the records where Age isn't filled in, the field actually
    contains Null. To be honest, though, I would have expected them to appear on
    your report, since you're not filtering on Age (filtering on Null values
    requires slightly different syntax). Are you sure there are records with no
    age in the date range you're looking at?

    Using AS after a field name allows you to use whatever alias you want for
    the field.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Bob Watson" <RMWatson3rd@bellsouth.net> wrote in message
    news:%23pn23vL7FHA.3276@TK2MSFTNGP15.phx.gbl...
    >
    > Thanks - that helped tremendously ... my
    > actual query looks like this:
    >
    > PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    > SELECT Registration.Age, Count(Registration.Age)
    > FROM Registration
    > WHERE (((Registration.[Date Attended])<=[End Date] And (Registration.[Date
    > Attended])>=[Begin Date]))
    > GROUP BY Registration.Age;
    >
    >
    > with this query output:
    >
    > Age Expr1001
    >
    > 0
    > 20-24 29
    > 25-34 125
    > 35-44 116
    > 45-64 55
    >
    >
    > Couple of observations/questions:
    > 1 - the count of a "Blank Age" is not 0. Why is
    > the "blank count" not working?
    >
    > 2 - I suppose this output could be used as a
    > "Record Source" of a Report. Can I change the
    > column name of Expr1001 to "Number" ??
    >
    > Thanks in advance,
    > Bob
    >
    >
    >
    > "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
    > news:%23V%23zGzI7FHA.808@TK2MSFTNGP09.phx.gbl...
    >> You want to count the number of Records by AgeRange so you need to use an
    >> Aggregate Query (with Group By) clause on your Table with SQL String
    >> like:
    >>
    >> SELECT Count([YourTable].[Name])
    >> FROM [YourTable]
    >> GROUP BY [YourTable].[AgeRange]
    >>
    >> --
    >> HTH
    >> Van T. Dinh
    >> MVP (Access)
    >>
    >>
    >>
    >> "Bob Watson" <RMWatson3rd@bellsouth.net> wrote in message
    >> news:u4rf$hI7FHA.636@TK2MSFTNGP10.phx.gbl...
    >>>
    >>> I have a report that gives name and age range.
    >>> Such as
    >>>
    >>> Bob 30-35
    >>> Bill 30-35
    >>> Tom 36-40
    >>> Betty 36-40
    >>> Mary 36-40
    >>>
    >>> What I want are recaps and no details.
    >>> Such as
    >>>
    >>> 30-35 2
    >>> 36-40 3
    >>>
    >>> I cannot figure out how. "Access For Dummies"
    >>> did not come thru in this instance.
    >>>
    >>> TIA,
    >>> Bob
    >>>

    >>
    >>

    >
    >
     
  6. Bob Watson

    Bob Watson
    Expand Collapse
    Guest

    Thanks - I understand all of this except the
    call to Nz () ... what does that do?
    Bob

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:utsk48P7FHA.1000@tk2msftngp13.phx.gbl...
    > Try:
    >
    > PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    > SELECT Nz(Registration.Age, "Blank") AS RegistrationAge,
    > Count(Registration.Age) AS RegistrationCount
    > FROM Registration
    > WHERE (((Registration.[Date Attended])<=[End Date]
    > And (Registration.[Date Attended])>=[Begin Date]))
    > GROUP BY Registration.Age;
    >
    > Presumably for the records where Age isn't filled in, the field actually
    > contains Null. To be honest, though, I would have expected them to appear
    > on your report, since you're not filtering on Age (filtering on Null
    > values requires slightly different syntax). Are you sure there are records
    > with no age in the date range you're looking at?
    >
    > Using AS after a field name allows you to use whatever alias you want for
    > the field.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Bob Watson" <RMWatson3rd@bellsouth.net> wrote in message
    > news:%23pn23vL7FHA.3276@TK2MSFTNGP15.phx.gbl...
    >>
    >> Thanks - that helped tremendously ... my
    >> actual query looks like this:
    >>
    >> PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    >> SELECT Registration.Age, Count(Registration.Age)
    >> FROM Registration
    >> WHERE (((Registration.[Date Attended])<=[End Date] And
    >> (Registration.[Date Attended])>=[Begin Date]))
    >> GROUP BY Registration.Age;
    >>
    >>
    >> with this query output:
    >>
    >> Age Expr1001
    >>
    >> 0
    >> 20-24 29
    >> 25-34 125
    >> 35-44 116
    >> 45-64 55
    >>
    >>
    >> Couple of observations/questions:
    >> 1 - the count of a "Blank Age" is not 0. Why is
    >> the "blank count" not working?
    >>
    >> 2 - I suppose this output could be used as a
    >> "Record Source" of a Report. Can I change the
    >> column name of Expr1001 to "Number" ??
    >>
    >> Thanks in advance,
    >> Bob
    >>
    >>
    >>
    >> "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
    >> news:%23V%23zGzI7FHA.808@TK2MSFTNGP09.phx.gbl...
    >>> You want to count the number of Records by AgeRange so you need to use
    >>> an Aggregate Query (with Group By) clause on your Table with SQL String
    >>> like:
    >>>
    >>> SELECT Count([YourTable].[Name])
    >>> FROM [YourTable]
    >>> GROUP BY [YourTable].[AgeRange]
    >>>
    >>> --
    >>> HTH
    >>> Van T. Dinh
    >>> MVP (Access)
    >>>
    >>>
    >>>
    >>> "Bob Watson" <RMWatson3rd@bellsouth.net> wrote in message
    >>> news:u4rf$hI7FHA.636@TK2MSFTNGP10.phx.gbl...
    >>>>
    >>>> I have a report that gives name and age range.
    >>>> Such as
    >>>>
    >>>> Bob 30-35
    >>>> Bill 30-35
    >>>> Tom 36-40
    >>>> Betty 36-40
    >>>> Mary 36-40
    >>>>
    >>>> What I want are recaps and no details.
    >>>> Such as
    >>>>
    >>>> 30-35 2
    >>>> 36-40 3
    >>>>
    >>>> I cannot figure out how. "Access For Dummies"
    >>>> did not come thru in this instance.
    >>>>
    >>>> TIA,
    >>>> Bob
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page