query trouble (MS Access)

I have a table with three fields OpenDt, Serno, and WONumber.  I need to select only the most recent record (OpenDt) for each Serno, while still displaying all three fields.

sample data:
OpenDt        04/23/2013
Serno           1231565483
WONumber 123456
drelingerAsked:
Who is Participating?
 
mbizupCommented:
Try this -
SELECT t.OpenDt, t.Serno,t.WONumber
FROM YourTable t
INNER JOIN
(
SELECT SerNo, Max(OpenDt) AS MaxDT
FROM YourTable
GROUP BY SerNo
) q
ON t.SerNo = q.SerNo AND t.OpenDT = q.MaxDT

Open in new window

0
 
pjevinCommented:
Make sure OpenDt is a date field and not a string when doing the above or it will give you the max alphabetically (which would meant 2/1/2013 is less than 10/1/2013).  You could also convert it to a date in the query but it would be much more efficient to have the date stored as a string.
0
 
Patrick MatthewsCommented:
pjevin,

You could also convert it to a date in the query but it would be much more efficient to have the date stored as a string.

I presume you meant to say, "...it would be much more efficient to have the date stored as a date", no?

There is no performance advantage to storing dates as strings, and there are numerous deleterious effects of doing so.

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
drelingerAuthor Commented:
worked perfectly. thank you for the help.
0
 
pjevinCommented:
Sorry I typed that too fast, yes, store it as a date, not a a string, heh.
0
 
Patrick MatthewsCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.