Solved

query trouble (MS Access)

Posted on 2013-05-15
6
204 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
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 92

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

 

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 92

Expert Comment

by:Patrick Matthews
ID: 39168817
:)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

813 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

8 Experts available now in Live!

Get 1:1 Help Now