SQL Statement Help

V

Vayse

I have an Access table, NewExamHeader.
Fields as follows:
HeadRef: Primary Key
Ident_No: String to Identify the Student
Exam_Yr: The year, e.g. 2005
Exam_Mt: Exam month - string, e.g. "05", "09"
Exam_Lvl: Exam Level - a string. e.g. "LAW1", "PHY3"

Now, I wish to get the date of when a student last sat an exam, so I
created a group query as below.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

How should I change the statement to also include the Exam_Lvl of the last
sitting?

Thanks
Vayse
 
G

Guest

This should work for you.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate,
Max(NewExamHeader.Exam_Lvl) as [Exam Level]
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

Throwing the Examl_Lvl in a "pseudo" Max aggregation takes care of you.

-Ted
 
D

Duane Hookom

I would be concerned about this SQL since the Exam_Lvl returned would be the
Max in alpha order and not depend at all on the exam date. The results might
be correct if the most recent exam was also the last in the alpha sort of
exam levels.

--
Duane Hookom
MS Access MVP
--

tfossum said:
This should work for you.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate,
Max(NewExamHeader.Exam_Lvl) as [Exam Level]
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

Throwing the Examl_Lvl in a "pseudo" Max aggregation takes care of you.

-Ted


Vayse said:
I have an Access table, NewExamHeader.
Fields as follows:
HeadRef: Primary Key
Ident_No: String to Identify the Student
Exam_Yr: The year, e.g. 2005
Exam_Mt: Exam month - string, e.g. "05", "09"
Exam_Lvl: Exam Level - a string. e.g. "LAW1", "PHY3"

Now, I wish to get the date of when a student last sat an exam, so I
created a group query as below.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

How should I change the statement to also include the Exam_Lvl of the
last
sitting?

Thanks
Vayse
Click to expand...
Click to expand...
 
G

Guest

Duane is right. My bad.

I think this will do the trick though:

SELECT neh1.Ident_No, neh1.[Exam Date], neh1.Exam_Lvl
FROM
(SELECT neh.Headref, neh.Ident_No, neh.Exam_Lvl, neh.[Exam_YR] &
neh.[Exam_MT] as [Exam Date]
FROM NewExamHeader as neh) as neh1,

(SELECT neh.Ident_No, max([Neh.Exam_YR]&[neh.Exam_MT]) as [Exam Date]
FROM NewExamHeader as neh
GROUP BY neh.Ident_No) as neh2

WHERE neh1.Ident_No = neh2.Ident_No
and neh1.[Exam Date] = neh2.[Exam Date]
ORDER By neh1.Ident_No, neh1.Exam_Lvl DESC;

If the same student takes more than one exam in the same month, and that
month is the last month they took an exam, all occurences will be returned.
Not sure if there is a way to get around this considering the level of detail
of the data is to the month.

Duane: Does it pass the test?

Sorry about the earlier snafu.

Cheers,

-Ted

Duane Hookom said:
I would be concerned about this SQL since the Exam_Lvl returned would be the
Max in alpha order and not depend at all on the exam date. The results might
be correct if the most recent exam was also the last in the alpha sort of
exam levels.

--
Duane Hookom
MS Access MVP
--

tfossum said:
This should work for you.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate,
Max(NewExamHeader.Exam_Lvl) as [Exam Level]
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

Throwing the Examl_Lvl in a "pseudo" Max aggregation takes care of you.

-Ted


Vayse said:
I have an Access table, NewExamHeader.
Fields as follows:
HeadRef: Primary Key
Ident_No: String to Identify the Student
Exam_Yr: The year, e.g. 2005
Exam_Mt: Exam month - string, e.g. "05", "09"
Exam_Lvl: Exam Level - a string. e.g. "LAW1", "PHY3"

Now, I wish to get the date of when a student last sat an exam, so I
created a group query as below.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

How should I change the statement to also include the Exam_Lvl of the
last
sitting?

Thanks
Vayse
Click to expand...
Click to expand...
Click to expand...
 
V

Vayse

Looks right. Thanks!

tfossum said:
Duane is right. My bad.

I think this will do the trick though:

SELECT neh1.Ident_No, neh1.[Exam Date], neh1.Exam_Lvl
FROM
(SELECT neh.Headref, neh.Ident_No, neh.Exam_Lvl, neh.[Exam_YR] &
neh.[Exam_MT] as [Exam Date]
FROM NewExamHeader as neh) as neh1,

(SELECT neh.Ident_No, max([Neh.Exam_YR]&[neh.Exam_MT]) as [Exam Date]
FROM NewExamHeader as neh
GROUP BY neh.Ident_No) as neh2

WHERE neh1.Ident_No = neh2.Ident_No
and neh1.[Exam Date] = neh2.[Exam Date]
ORDER By neh1.Ident_No, neh1.Exam_Lvl DESC;

If the same student takes more than one exam in the same month, and that
month is the last month they took an exam, all occurences will be
returned.
Not sure if there is a way to get around this considering the level of
detail
of the data is to the month.

Duane: Does it pass the test?

Sorry about the earlier snafu.

Cheers,

-Ted

Duane Hookom said:
I would be concerned about this SQL since the Exam_Lvl returned would be
the
Max in alpha order and not depend at all on the exam date. The results
might
be correct if the most recent exam was also the last in the alpha sort of
exam levels.

--
Duane Hookom
MS Access MVP
--

tfossum said:
This should work for you.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate,
Max(NewExamHeader.Exam_Lvl) as [Exam Level]
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

Throwing the Examl_Lvl in a "pseudo" Max aggregation takes care of you.

-Ted


:

I have an Access table, NewExamHeader.
Fields as follows:
HeadRef: Primary Key
Ident_No: String to Identify the Student
Exam_Yr: The year, e.g. 2005
Exam_Mt: Exam month - string, e.g. "05", "09"
Exam_Lvl: Exam Level - a string. e.g. "LAW1", "PHY3"

Now, I wish to get the date of when a student last sat an exam, so I
created a group query as below.

SELECT NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) AS ExamDate
FROM NewExamHeader
WHERE (((NewExamHeader.ExamSit)=True))
GROUP BY NewExamHeader.IDENT_NO
ORDER BY NewExamHeader.IDENT_NO, Max([EXAM_YR] & [EXAM_MT]) DESC;

How should I change the statement to also include the Exam_Lvl of the
last
sitting?

Thanks
Vayse
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need help with a sql statement 6
Return MAX record in SQL statement HELP 2
tscmd35.zip Useful NT/2000/XP script tricks and tips 0
HELP! Lost DNS zones in Win2000 1
History of Xbox 360 defects - Dean Takahashi has the inside story(long) 2
FWT Newsletter - Weekly - February 2, 2004 1
http://drpcdr.ca 4
FWT Newsletter - Weekly - September 6, 2004 1

Top