Help needed with filter in query...

[rashar via AccessMonster.com]

Hello,

I have a table that stores student id's, name, class type, and class date.

The data is laid out as such:

12345 John Smith Math Update 1/21/09
67891 Mark John English 7/10/08
84535 Karen Jones Science 4/11/09
12345 John Smith Math Masters 7/20/08
43454 June Shay Math Update 5/20/08
54545 Jared Allan English 5/20/09
43454 June Shay Math Masters 9/21/07

I would like a query that will search through the recordset and return all
the Math Masters who have not taken a Math Update class between Jan 1, 09 and
July 30, 09.

How could I construct a query that would return the results set that I'm
looking for? So based on the table, I'd like to return 43454 June Shay Math
Update 5/20/08

Thanks in advance.

 

[Dale Fye]

You could do this by creating three separte queries (qryMathMasters,
qryMathUpdates, and then a find unmatched query), but I'd probably just to
this all as part of a single query:

SELECT MM.ID, MM.[Student Name], MM.[Class_Date],
MU.[Class_Date]
FROM (SELECT ID, [Student Name], [Class Type], [Class_Date]
FROM YourTable
WHERE [Class_Type] = "Math Master") as MM
LEFT JOIN (SELECT ID, [Student Name], [Class Type], [Class_Date]
FROM YourTable
WHERE [Class_Type] = "Math Update"
AND [Class_Date] BETWEEN #1/1/09# AND #7/30/09#) as MU
ON MM.ID = MU.ID
WHERE MU.ID IS NULL

 

[KARL DEWEY]

Try this --
SELECT [student id], [name], [class type], Max([class date]) AS Last_Class
FROM YourTable
ORDER BY [student id], [name], [class type]
HAVING Max([class date]) Not Between #1/1/2009# and #7/30/2009#;

 

[rashar via AccessMonster.com]

Perfect! This solution worked great. Thanks!

You could do this by creating three separte queries (qryMathMasters,
qryMathUpdates, and then a find unmatched query), but I'd probably just to
this all as part of a single query:

SELECT MM.ID, MM.[Student Name], MM.[Class_Date],
MU.[Class_Date]
FROM (SELECT ID, [Student Name], [Class Type], [Class_Date]
FROM YourTable
WHERE [Class_Type] = "Math Master") as MM
LEFT JOIN (SELECT ID, [Student Name], [Class Type], [Class_Date]
FROM YourTable
WHERE [Class_Type] = "Math Update"
AND [Class_Date] BETWEEN #1/1/09# AND #7/30/09#) as MU
ON MM.ID = MU.ID
WHERE MU.ID IS NULL

----
HTH
Dale

Hello,

[quoted text clipped - 19 lines]

Thanks in advance.

 

[rashar via AccessMonster.com]

This worked great too! Thanks guys!

Try this --
SELECT [student id], [name], [class type], Max([class date]) AS Last_Class
FROM YourTable
ORDER BY [student id], [name], [class type]
HAVING Max([class date]) Not Between #1/1/2009# and #7/30/2009#;

Hello,

[quoted text clipped - 19 lines]

Thanks in advance.