Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

query trouble (MS Access)

Posted on 2013-05-15
6
Medium Priority
?
244 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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