Count of Members by Age

[laura]

I need to run a query on a database of Members ranging from babies to
elderly people, (names, addresses and date of birth), where it shows me all
members who are older than 18 years old as of "today's date". Can someone
help me to define the query please?

Laura TD

 

[laura]

what I really need is to be able to COUNT how many of the members are over
18, not have a list of them.

Laura

 

[Douglas J. Steele]

Assuming you have a Birthday field of type Date/Time in your table, the SQL
would look like this:

SELECT Count(*) AS NumberOfMembers
FROM MyTable
WHERE Birthday <= DateAdd("yyyy", -18, Date())

 

[laura]

Douglas,

This works for me as long as I cut and paste your code into the Query Design
window, but if I try and build it from scratch in the Query Design window,
adding the MEMBERS table and then trying to create the 'count' on a
'derived' field (if I understand your SQL correctly), I do not get the right
result. My SQL ends up looking like this:-

SELECT [NumberOfMembers] AS Expr1
FROM MEMBERS
WHERE (((MEMBERS.[Date of Birth])<=DateAdd("yyyy",-18,Date())));

which does not work

I don't understand how you got the "SELECT Count(*) As NumberOfMembers

even though it works, I need to understand how and why..
Laura TD

 

[Douglas J. Steele]

In the list of fields in the table, drag the Date of Birth field and any
other field into the grid (probably best to make it a field that doesn't
have Null values)

Either click on the Totals icon on the toolbar (it's the Sigma character),
or else select View | Total from the menu bar. A new row will appear on your
grid, labelled Total:

Change Group By under the Date of Birth field to Where, and change it to
Count under the other field. Put NumberOfMembers: (including the colon) in
front of the other field name if you want (if you don't, it'll come back as
CountOfMyField, where the actual name of the field will be used instead of
MyField), and put <=DateAdd("yyyy",-18,Date()) as the Criteria under the
Date of Birth field. Uncheck the Show check box under the Date of Birth
field.

On the other hand, what's wrong with just working in the Query Design
window?

--
Doug Steele, Microsoft Access MVP

Douglas,

This works for me as long as I cut and paste your code into the Query Design
window, but if I try and build it from scratch in the Query Design window,
adding the MEMBERS table and then trying to create the 'count' on a
'derived' field (if I understand your SQL correctly), I do not get the right
result. My SQL ends up looking like this:-

SELECT [NumberOfMembers] AS Expr1
FROM MEMBERS
WHERE (((MEMBERS.[Date of Birth])<=DateAdd("yyyy",-18,Date())));

which does not work

I don't understand how you got the "SELECT Count(*) As NumberOfMembers

even though it works, I need to understand how and why..
Laura TD

 

[laura]

Doug,

Many thanks and well, perhaps I was not clear, being quite new to all of
this. I tend to work in the query design window (if that's the right phrase)
where I add my table, then drag my fields to the columns below, more or less
as you described below.

I had spent most of the afternoon yesterday trying to achieve the total
result you provided for me, by doing just that, but obviously I was doing
something wrong because I was not achieving the total number of members
under 18 that I needed. Your code was easy to copy and paste into the SQL
view and it worked like magic, but I did not understand how to do it in the
Query Design Window.. in other words, I did not understand the process you
explained below. I'll never learn unless I understand each process, it's not
enough to always just accept a solution and move forward.

Anyway, hope I have not been confusing, hope you see this and once again,
thanks for the detailed explanation and for the solution.

Laura TD

In the list of fields in the table, drag the Date of Birth field and any
other field into the grid (probably best to make it a field that doesn't
have Null values)

Either click on the Totals icon on the toolbar (it's the Sigma character),
or else select View | Total from the menu bar. A new row will appear on your
grid, labelled Total:

Change Group By under the Date of Birth field to Where, and change it to
Count under the other field. Put NumberOfMembers: (including the colon) in
front of the other field name if you want (if you don't, it'll come back as
CountOfMyField, where the actual name of the field will be used instead of
MyField), and put <=DateAdd("yyyy",-18,Date()) as the Criteria under the
Date of Birth field. Uncheck the Show check box under the Date of Birth
field.

On the other hand, what's wrong with just working in the Query Design
window?

--
Doug Steele, Microsoft Access MVP

Douglas,

This works for me as long as I cut and paste your code into the Query Design
window, but if I try and build it from scratch in the Query Design window,
adding the MEMBERS table and then trying to create the 'count' on a
'derived' field (if I understand your SQL correctly), I do not get the right
result. My SQL ends up looking like this:-

SELECT [NumberOfMembers] AS Expr1
FROM MEMBERS
WHERE (((MEMBERS.[Date of Birth])<=DateAdd("yyyy",-18,Date())));

which does not work

I don't understand how you got the "SELECT Count(*) As NumberOfMembers

even though it works, I need to understand how and why..
Laura TD

Assuming you have a Birthday field of type Date/Time in your table,

the
SQL

would look like this:

SELECT Count(*) AS NumberOfMembers
FROM MyTable
WHERE Birthday <= DateAdd("yyyy", -18, Date())


--
Doug Steele, Microsoft Access MVP



what I really need is to be able to COUNT how many of the members

are
over

18, not have a list of them.

Laura