Show Only Latest Note Entry in Query

[Xcalibur]

Two of the fields in my query are for Progress Note and Progress Note Date. Each client has several progress notes. How can I have the query show only the Progress Note with the latest date?

 

[ajetrumpet]

Two of the fields in my query are for Progress Note and Progress Note Date.

So, we're dealing with two fields here

How can I have the query show only the Progress Note with the latest date?

As in, the most recent date?? If that's what you mean, write this:

SELECT TOP 1 [whatever], [progress note], [progress date]

FROM [what]

ORDER BY [progress date] DESC

 

[Xcalibur]

So, we're dealing with two fields hereAs in, the most recent date?? If that's what you mean, write this:

SELECT TOP 1 [whatever], [progress note], [progress date]

FROM [what]

ORDER BY [progress date] DESC

I need it to return all the different clients only showing the one progress note for each client. How would I incorporate this into the rest of the query.

Thanks.

 

[ajetrumpet]

I need it to return all the different clients only showing the one progress note for each client. How would I incorporate this into the rest of the query.

OK then, sorry. Maybe you should write MAX instead...

SELECT [client], , [progress note], Max([progress note date])

FROM []

GROUP BY [client], [progress note];

See this too:

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

 

[Xcalibur]

OK then, sorry. Maybe you should write MAX instead...

SELECT [client], , [progress note], Max([progress note date])

FROM []

GROUP BY [client], [progress note];

See this too:

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

Hi Adam,

This format didn't work for me. It gave me this error: "You tried to execute a query that does not include the specified expression 'query name' as part of an aggregate function. "query name" is the name of one of the queries it pulls from. It works fine when I remove the MAX part.

I'm done for today but will be back at it tomorrow.

Thanks.

 

[ajetrumpet]

Well,

From the error message, it sounds like you copied my SQL from the post. Did you? MAX is an aggregate. If an aggregate exists in a SELECT clause, all of the other fields listed in the clause that are not attached to aggregate functions have to be grouped. Did you read the FAQ from that link I gave?

Is there more to the puzzle here (as in, "stacked queries", or more than one table involved)?

 

[Xcalibur]

Well,

From the error message, it sounds like you copied my SQL from the post. Did you? MAX is an aggregate. If an aggregate exists in a SELECT clause, all of the other fields listed in the clause that are not attached to aggregate functions have to be grouped. Did you read the FAQ from that link I gave?

Is there more to the puzzle here (as in, "stacked queries", or more than one table involved)?

Sorry, I didn't realize all the fields needed to be grouped. I did so and it produced all the same records, showing the max dates, and there are chinese symbols in the notes field.

This query does have three queries joined and there is a subquery.

 

[ajetrumpet]

Maybe you can post it?

There is getting to be a lot of information involved here...

 

[Xcalibur]

Maybe you can post it?

There is getting to be a lot of information involved here...

Sorry, I can't post it due to confidentiality reasons. I realize it is difficult to resolve something when you can't see the whole picture.

Thanks for trying

 

[Xcalibur]

I resolved my problem by creating a query with only the clientID and the progress note date and then did a MAX for the progress note date. I then created another query which included all the other info from the original progress note query by creating equal joins between the client id and note date fields.