query trouble (MS Access)

Posted on 2013-05-15
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
Question by:drelinger
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 39167964
Try this -
SELECT t.OpenDt, t.Serno,t.WONumber
FROM YourTable t
SELECT SerNo, Max(OpenDt) AS MaxDT
FROM YourTable
) q
ON t.SerNo = q.SerNo AND t.OpenDT = q.MaxDT

Open in new window


Expert Comment

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.
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39168070

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, " 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.

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Author Closing Comment

ID: 39168075
worked perfectly. thank you for the help.

Expert Comment

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

Expert Comment

by:Patrick Matthews
ID: 39168817

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

14 Experts available now in Live!

Get 1:1 Help Now