Grouping ages together

[Tony Wainwright]

Hi guys

I am writing a query that displays the age of a candidate at the start of a course and the date that they achieved each of 3 qualifications, the query looks something like this:

SELECT [FirstName] & " " & [LastName] AS Name, tblCourse.StartAge, luScheme.SchemeName, luQualification.Qualification, tblProgress.L2CompApp, tblProgress.L34CompApp, tblProgress.FrameCompApp
FROM (tblCandidate INNER JOIN ((tblCourse INNER JOIN luQualification ON (tblCourse.QualificationID = luQualification.QualificationNumber) AND (tblCourse.Scheme = luQualification.SchemeID)) INNER JOIN luScheme ON luQualification.SchemeID = luScheme.SchemeID) ON tblCandidate.CandidateID = tblCourse.CandidateID) INNER JOIN tblProgress ON (tblCourse.Scheme = tblProgress.Scheme) AND (tblCourse.QualificationID = tblProgress.QualificationID) AND (tblCourse.CandidateID = tblProgress.CandidateID)
WHERE (((tblProgress.L2CompApp) Between #1/1/2003# And #12/31/2003#)) OR (((tblProgress.L34CompApp) Between #1/1/2003# And #12/31/2003#)) OR (((tblProgress.FrameCompApp) Between #1/1/2003# And #12/31/2003#))
ORDER BY [FirstName] & " " & [LastName];

What I want to do is group the ages together as in all those 16 to 18 and those over 19. Is there anyway I can do this? It maybe that I have to do this in the report that is based on this query. If so any ideas as to how.

Cheers
Tony

 

[Dale Fye]

Actually Tony, your criteria are incomplete or confusing.

1. Is there any chance they could be under 16(child prodigy)? If so, you need to include an option for that.
2. Does 16-18 mean 16 yrs, 0 days thru 18yrs, 364 days?
3. Do you mean 19 and over?

Add another column to your query

Switch(tblCourse.StartAge < 16, "under 16", tblCourse.StartAge >= 16 And tblCourse.StartAge < 19, "16-18", True, "19 and over") as AgeGroup

--
HTH

Dale Fye


Hi guys

I am writing a query that displays the age of a candidate at the start of a course and the date that they achieved each of 3 qualifications, the query looks something like this:

SELECT [FirstName] & " " & [LastName] AS Name, tblCourse.StartAge, luScheme.SchemeName, luQualification.Qualification, tblProgress.L2CompApp, tblProgress.L34CompApp, tblProgress.FrameCompApp
FROM (tblCandidate INNER JOIN ((tblCourse INNER JOIN luQualification ON (tblCourse.QualificationID = luQualification.QualificationNumber) AND (tblCourse.Scheme = luQualification.SchemeID)) INNER JOIN luScheme ON luQualification.SchemeID = luScheme.SchemeID) ON tblCandidate.CandidateID = tblCourse.CandidateID) INNER JOIN tblProgress ON (tblCourse.Scheme = tblProgress.Scheme) AND (tblCourse.QualificationID = tblProgress.QualificationID) AND (tblCourse.CandidateID = tblProgress.CandidateID)
WHERE (((tblProgress.L2CompApp) Between #1/1/2003# And #12/31/2003#)) OR (((tblProgress.L34CompApp) Between #1/1/2003# And #12/31/2003#)) OR (((tblProgress.FrameCompApp) Between #1/1/2003# And #12/31/2003#))
ORDER BY [FirstName] & " " & [LastName];

What I want to do is group the ages together as in all those 16 to 18 and those over 19. Is there anyway I can do this? It maybe that I have to do this in the report that is based on this query. If so any ideas as to how.

Cheers
Tony

 

[[MVP] S. Clark]

I would use an update query, to write to the data table, the 'age category'
of the person. (<18, >18) Then you can easily group by that field in the
query.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


Hi guys

I am writing a query that displays the age of a candidate at the start of a
course and the date that they achieved each of 3 qualifications, the query
looks something like this:

SELECT [FirstName] & " " & [LastName] AS Name, tblCourse.StartAge,
luScheme.SchemeName, luQualification.Qualification, tblProgress.L2CompApp,
tblProgress.L34CompApp, tblProgress.FrameCompApp
FROM (tblCandidate INNER JOIN ((tblCourse INNER JOIN luQualification ON
(tblCourse.QualificationID = luQualification.QualificationNumber) AND
(tblCourse.Scheme = luQualification.SchemeID)) INNER JOIN luScheme ON
luQualification.SchemeID = luScheme.SchemeID) ON tblCandidate.CandidateID =
tblCourse.CandidateID) INNER JOIN tblProgress ON (tblCourse.Scheme =
tblProgress.Scheme) AND (tblCourse.QualificationID =
tblProgress.QualificationID) AND (tblCourse.CandidateID =
tblProgress.CandidateID)
WHERE (((tblProgress.L2CompApp) Between #1/1/2003# And #12/31/2003#)) OR
(((tblProgress.L34CompApp) Between #1/1/2003# And #12/31/2003#)) OR
(((tblProgress.FrameCompApp) Between #1/1/2003# And #12/31/2003#))
ORDER BY [FirstName] & " " & [LastName];

What I want to do is group the ages together as in all those 16 to 18 and
those over 19. Is there anyway I can do this? It maybe that I have to do
this in the report that is based on this query. If so any ideas as to how.

Cheers
Tony