select unqiue records by datetime stamp

Posted on 2006-05-17
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


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
Question by:jerrylmclaughlin
    LVL 65

    Accepted Solution

    SELECT ordernum, product, price, max(datetime_stamp)
    from table
    group by ordernum, product, price

    LVL 65

    Expert Comment

    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

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    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…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    755 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

    25 Experts available now in Live!

    Get 1:1 Help Now