Grouping ages together

  • Thread starter Thread starter Tony Wainwright
  • Start date Start date
T

Tony Wainwright

Hi guys

I am writing a report 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 the rport is based on 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?

Cheers
Tony
 
I would include a field in the query identifying the age
goup and than sort on this field.

Hope this helps
-----Original Message-----
Hi guys

I am writing a report that displays the age of a
Click to expand...
candidate at the start of a course and the date that they
achieved each of 3 qualifications, the query the rport is
based on looks something like this:
SELECT [FirstName] & " " & [LastName] AS Name,
Click to expand...
tblCourse.StartAge, luScheme.SchemeName,
luQualification.Qualification, tblProgress.L2CompApp,
tblProgress.L34CompApp, tblProgress.FrameCompApp
FROM (tblCandidate INNER JOIN ((tblCourse INNER JOIN
Click to expand...
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
Click to expand...
#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
Click to expand...
those 16 to 18 and those over 19. Is there anyway I can
do this?
 
Back
Top