Solved

query trouble (MS Access)

Posted on 2013-05-15
6
177 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
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
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
Comment Utility
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 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Closing Comment

by:drelinger
Comment Utility
worked perfectly. thank you for the help.
0
 
LVL 3

Expert Comment

by:pjevin
Comment Utility
Sorry I typed that too fast, yes, store it as a date, not a a string, heh.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
:)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now