Last Date

[hockeylvr]

I'm trying to run qryNotes to just show the latest records for clients. Everything I've looked at points me to using "Last" or "Max" but I always get the error "You tried to execute a query that does not include the specified expressions expression 'clientid' as part of an aggregate function" Seems simple enough but can't figure out where I'm going wrong.
Thanks a lot for any help.


SELECT tblnotes.clientid, tblclients.lname, Last([tblnotes].[notedate]) As NoteDate, tblnotes.note
FROM tblclients INNER JOIN tblnotes ON tblclients.clientid = tblnotes.clientid;

 

[PHV]

What about this ?

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C
INNER JOIN tblnotes AS N ON C.clientid = N.clientid)
INNER JOIN (
SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid
) AS M ON N.clientid = M.clientid AND N.notedate = M.MaxDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

Thanks for the quick response. I see that you've added table "M" do I need to create a new table to accommodate MaxDate? I'm getting an error message as written and did build another table M but getting same error. "...cannot find the input table or query "SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP by 'clientid'. Make sure it exists...

I never thought getting back into Access would be so hard....

Thanks!

 

[PHV]

you've added table "M"
No, I've simply used alias ...

I'm getting an error message
Which error ? When ? Which REAL SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

Error message "...cannot find the input table or query "SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP by 'clientid'. Make sure it exists...

I'm getting the same error message using the alias as well.

Thanks

 

[PHV]

Again, Which REAL SQL code ?
BTW, which version of access ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

I guess I'm not understanding your question "real SQL code", sorry. I'm using Access 2002 and Vista

 

[PHV]

I ask for the contents of the SQL view pane of your query when the error raises.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

This?

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C INNER JOIN tblnotes AS N ON C.clientid=N.clientid) INNER JOIN [SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid ] AS M ON (N.clientid=M.clientid) AND (N.notedate=M.MaxDate);

 

[PHV]

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C INNER JOIN tblnotes AS N ON C.clientid=N.clientid) INNER JOIN [!]([/!]SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid[!])[/!] AS M ON (N.clientid=M.clientid) AND (N.notedate=M.MaxDate)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

When I type it into the SQL window as written it runs correctly the first time but, when I save it and try to run it again, I get the same error message and the SQL has changed line format. Here is how it looks when getting the error on the second example you sent.

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C INNER JOIN tblnotes AS N ON C.clientid = N.clientid) INNER JOIN [SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid]. AS M ON (N.clientid = M.clientid) AND (N.notedate = M.MaxDate);

The FROM statement ends up all on one line.

And on the first it goes from what you sent:

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C
INNER JOIN tblnotes AS N ON C.clientid = N.clientid)
INNER JOIN (
SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid
) AS M ON N.clientid = M.clientid AND N.notedate = M.MaxDate

And changes to:

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C INNER JOIN tblnotes AS N ON C.clientid = N.clientid) INNER JOIN [SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid
]. AS M ON (N.notedate = M.MaxDate) AND (N.clientid = M.clientid);

Weird but I'll keep working with it as I know it's what I'm looking for. Thanks a lot for you attention I appreciate it.

 

[PHV]

OK, so create a named, say, qryMaxDate:

SELECT clientid, Max(notedate) AS MaxDate FROM tblnotes GROUP BY clientid;

And now your query:

SELECT N.clientid, C.lname, M.MaxDate AS NoteDate, N.note
FROM (tblclients AS C INNER JOIN tblnotes AS N ON C.clientid = N.clientid) INNER JOIN qryMaxDate AS M ON (N.clientid = M.clientid) AND (N.notedate = M.MaxDate);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hockeylvr]

Perfect timing? I just finished creating the first qryMaxDate so I had the right idea! Thank you so much for the help it works perfectly!