We did not get down to discussing your
table structures, but I might suggest
adding a "tblAgeGrp"
Tom Ellison looks at similar situation
of Allen Browne's website:
http://allenbrowne.com/ser-58.html
So...if I understood correctly
(will be doing "divide-and-conquer"
so things may be clearer):
tblAgeGrp
MinMnths AgeGrp
0 0-2 mos
3 3-11 mos
12 1-5 years
72 6-13 years
168 14 and above
where MinMnths is type Long
(and primary key)
and represents the minimum
age *in months* for an AgeGrp
(so you could expand further
if you want....I just arbitrarily stopped
adding groups at 14 for brevity)
Then, make a query to produce
Min and Max Mnths for an AgeGrp
qryMinMaxMnths
SELECT
t.MinMnths As MinMnth,
(SELECT
Nz(MIN(s.MinMnths),2401) - 1
FROM
tblAgeGrp As s
WHERE
s.MinMnths > t.MinMnths)
AS MaxMnth,
t.AgeGrp
FROM
tblAgeGrp As t;
which would produce from our table above:
MinMnth MaxMnth AgeGrp
0 2 0-2 mos
3 11 3-11 mos
12 71 1-5 years
72 167 6-13 years
168 2400 14 and above
if you think you will have anyone
older than 200 years, you should
change "2401" in the null-to-zero
function to something larger.
you already know how to find the age
of your clients/customers/repondants?
in months based on DOB
qryAges
SELECT
y.DOB,
(DateDiff("m",y.DOB,Date())
+(Day(y.DOB)>Day(Date()))) As MnthAge,
[MnthAge]\12 & "yrs "
& ([MnthAge] MOD 12) & " mos" As Age
FROM
yurtable As y;
of course, replace "yurtable"
with actual name of your table
in query above...
so...
join qryMinMaxMnths with qryAges
and count groups
SELECT
G.AgeGrp,
G.MinMnth,
G.MaxMnth,
COUNT(A.MnthAge) AS AgeGrpCnt
FROM
qryAges AS A
RIGHT JOIN
qryMinMaxMnths AS G
ON
(A.MnthAge >= G.MinMnth)
AND
(A.MnthAge <= G.MaxMnth)
GROUP BY
G.AgeGrp,
G.MinMnth,
G.MaxMnth
ORDER BY
G.MinMnth;
Ess said:
I'm not really sure what happened but the query works like a charm now.
Next issue is, now that I have the age, I have to create age categories
based on age (e.g. age 0 - 2 mos; 3 - 11 mos; 1 - 5 years; 6 - 13 yrs;
etc.)
then do a count of each age within that particular age category.
Should I create this within the same query or create a table identifying
which category an age should fall under then do a count?
What's the most effecient way of obtaining this information?
--
If you never attempt anything, you will never make any mistakes...thus
never
enjoying the fruits of accomplishment.
Gary Walter said:
"works" here in Debug's Immediate Window
DOB=#6/1/2005#
?(DateDiff("m",DOB,Date())+(Day(DOB)>Day(Date())))\12 & " yrs " &
(DateDiff("m",DOB,Date())+(Day(DOB)>Day(Date()))) Mod 12 & " mos"
1 yrs 1 mos
what is your system date?
did it work properly for other [DOB]'s?
is [DOB] for sure a Date/Time field?
are you working with only an Access table?
are your references okay?
