Using Totals in Query

[eebabe]

These are my Data:

ID Name Enroll Stats Stat Date
00001 Babe Ruth 1- Enrolled 10/13/07
00001 Babe Ruth 7 - Disenrolled 02/20/07
00001 Babe Ruth 0 - Referred 06/01/06

I want to show the the data that correspont to the last date in the list ie. 00001 Babe Ruth 1- Enrolled 10/13/07. Using query and total I group ID, Name, Enroll Stats and Last for stat date. The query result still shows 3 entries and here's the sql query:

SELECT [tbl Enrollment History].[PC ID], [tbl Enrollment History].Name, [tbl Enrollment History].[Enroll Stats], Last([tbl Enrollment History].[Stat Date]) AS [LastOfStat Date]
FROM [tbl Enrollment History]
GROUP BY [tbl Enrollment History].[PC ID], [tbl Enrollment History].Name, [tbl Enrollment History].[Enroll Stats];

Thanks for your expertise.

 

[PWise]

Select ID, Name, [Enroll Stats], [Stat Date]
From [tbl Enrollment History]

inner join
(Select id,max(Stat Date) as MaxStateDate
From [tbl Enrollment History]
Group by Id)MaxDateById
on MaxDateById.id=[tbl Enrollment History].id
And MaxDateById.MaxStateDate=[tbl Enrollment History].[Stat Date]

 

[eebabe]

pwise:

Thanks for the reply. I pasted the query is MS Access and it there's a syntax error (missing operator) in query expression 'max(Stat Date)'.

Do I need to include [tbl Enrollment History].[stat date]?

Thanks.

 

[PWise]

try putting brakkets around Stat date

Max([COLOR=red][[/color]Stat Date[COLOR=red]][/color])

 

[eebabe]

pwise:

I'm sorry.

After I put the Max([Stat Date]) another error comes; Syntax error in From clause.

Not sure what's the problem.

Thanks for your help.

 

[lespaul]

can you post the SQL that's giving you the error?

Leslie

 

[eebabe]

Leslie:

Thanks for your help. I went the long way. I queried (qry_MaxDate)and then link this query to enroll_hist to find the enroll status that connected to the maxDate.

Maybe there's a simpler way but working with access - it's finicky!!

Thanks again.