[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

select unqiue records by datetime stamp

Posted on 2006-05-17
2
Medium Priority
?
361 Views
Last Modified: 2009-01-08
I have a simple Access table with duplicate records dumped from Oracle Financials

these order records have duplicate order numbers but may have changes so the entire record other than order number may not be unique.
I want only one record per order number and I want it to be the record with the latest date time stamp

Example

Ordernum, product, price, datetime stamp
1001, candy, $.50, 01012006 08:00:00
1001, candy, $.54, 01022006 07:50:00
1001, candy, $.52, 01032006 06:05:00
1002, milk, $5.00, 01012006 09:00:00

The result I want is:

1001, candy, $.52, 01032006 06:05:00
1002, milk, $5.00, 01012006 09:00:00

How do I do this access? What is the sql statement
0
Comment
Question by:jerrylmclaughlin
  • 2
2 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16699224
SELECT ordernum, product, price, max(datetime_stamp)
from table
group by ordernum, product, price

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16699293
no, hold on, that may not work, as I just looked at your examples

you may need a subquery

select a.ordernum, a.product, a.price, a.datetime_stamp
from table a where a.datetimestamp = (select max(b.datetimestamp) from table b where b.ordernum = a.ordernum)

put a index on datetimestamp

note if u have a primary key already, then use that
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 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