?
Solved

query trouble (MS Access)

Posted on 2013-05-15
6
Medium Priority
?
237 Views
Last Modified: 2013-05-15
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
0
Comment
Question by:drelinger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39167964
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
 
LVL 3

Expert Comment

by:pjevin
ID: 39168004
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39168070
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:drelinger
ID: 39168075
worked perfectly. thank you for the help.
0
 
LVL 3

Expert Comment

by:pjevin
ID: 39168140
Sorry I typed that too fast, yes, store it as a date, not a a string, heh.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39168817
:)
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question