Return Record Matching Latest Date

[BlackEyedPea]

Hi there,
I have a table, which has an order number, a comment & a date the comment was added.

Therefore you could have 3 or 4 comments against one order number. All I want to do is create a table via a query which takes my existing table and shows me the latest comment based on the date. So for example, if there are two comments for an order, one done today and one yesterday I want my query to show me only the comment done today.

I thought this would be easy, but when I use the MAX on the date on the query designer I am still getting two rows of data for some orders.

I just can't seem to get my head around what I am doing wrong.

[Stewart Ross]

Please post the SQL for the query concerned - we can't answer you without having the details of the tables and fields concerned in front of us.

-Stewart

[NeoPa]

First of all let me warn you against duplicating the pre-existing data into a separate table. This is very rarely a good idea (See Database Normalisation and Table Structures). Instead, just use the SELECT query when that specific data is required.

Next, please check the new title of the thread. This is not only a valid title, but it also gives you clues as to what you're actually trying to do. Please remember in future to use a sensible title for any new question threads.

Now let's look at your actual issue. You're not looking to return the value of the maximum date, but actually you want the record associated with that maximum. This can be done in the following format :

SELECT *
FROM   [YourTable] AS [YTo]
WHERE  [CommentDate] In(SELECT Max([CommentDate])
                        FROM   [YourTable] AS [YTi]
                        WHERE  YTi.OrderNo = YTo.OrderNo)

Does that make sense?

[BlackEyedPea]

Thank you very much for your help, I've tried what you said above and it worked straight away.

Thanks again.

[NeoPa]

I'm very pleased to help, and almost more pleased even to hear that what I knocked up worked first time. Very gratifying :-)