Access 2003 - Recaps without details

[Bob Watson]

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

 

[Van T. Dinh]

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]

 

[Bob Watson]

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

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)

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

 

[Douglas J. Steele]

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

(no e-mails, please!)

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

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)

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

 

[Bob Watson]

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

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

(no e-mails, please!)

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

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)




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

 

[Douglas J. Steele]

The Nz function returns the value of the first argument if the first
argument is not Null, or the value of the second argument if the first
argument is Null.

In other words, Nz(1, 0) returns 1, while Nz(Null, 0) returns 0.

The only reason I put it in the SQL is to display the word "Blank", rather
than nothing, for those records that did not have an Age specified.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)

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



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)




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