Access 2003 - Recaps without details

B

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
 
V

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]
 
B

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



Van T. Dinh said:
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 said:
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
Click to expand...
Click to expand...
 
D

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!)



Bob Watson said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Watson

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

Douglas J. Steele said:
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!)



Bob Watson said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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!)



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

Douglas J. Steele said:
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!)



Bob Watson said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top