Find oldest of duplicate records

[skwilliams]

I have a table with Order, Activity, and dtDate fields.

I might have multiple records with the same order and activity but different dates.

I'd like to pull just the record with the oldest date.

Any ideas?

 

[jzwp22]

You'll need an aggregate query that utilizes the Min() function. Somthing like this:

SELECT yourtablename.order, yourtablename.activity, Min(yourtablename.dtDate) AS MinOfdtDate
FROM yourtablename
GROUP BY yourtablename.order, yourtablename.activity;

 

[MarkK]

SELECT TOP 1 *
FROM yourTable
ORDER BY dtDate

 

[SteveSchapel]

Skwilliams,

Yes, a Totals Query as suggested by Jzwp will do it for you.

Note that "Order" is a Reserved Word (i.e. has a special meaning) in Access, and as such is a good idea to avoid using it for the name of a field or control.

 

[MarkK]

OP wants the record with the oldest dtDate. How is it not satisfactory to simply sort by dtDate and select the first record?

 

[SteveSchapel]

Lagbolt,

OP wants the record with the oldest dtDate

... for each Order + Activity.

That's my reading of the question anyway.

 

[vbaInet]

I'm not so sure if Min() would work in this case. Wouldn't you get duplicate records if two records have the same min dates? I'm thinking something more restrictive along the lines of:

SELECT yourtablename.order, yourtablename.activity, yourtablename.dtDate 
FROM yourtablename 
WHERE yourtablename.order In (SELECT TOP 1 Q.order FROM yourtablename AS Q WHERE Q.activity = yourtablename.activity ORDER BY Q.activity, Q.dtDate DESC)
GROUP BY yourtablename.order, yourtablename.activity;

 

[jzwp22]

vbaInet,

Yes, if the dates are the same as well, the Min() would pull duplicate records. The OP will have to provide more input to see which would be the best approach based on the data.

 

[MarkK]

SELECT TOP 1 *
FROM someTable
WHERE Order = <someOrder> 
  AND Activity = <someActivity>
ORDER BY dtDate

 

[SteveSchapel]

vbaInet,

I'm not so sure if Min() would work in this case. Wouldn't you get duplicate records if two records have the same min dates?

No, it wouldn't. The Totals Query using Min will do the trick just fine.

 

[SteveSchapel]

Yes, if the dates are the same as well, the Min() would pull duplicate records.

No it won't!

 

[SteveSchapel]

I have re-read this thread. Unless Skwilliams pops back in to clarify, I think we are stuck. I initially read, and still do read, the original question to be asking for the earliest date for each combination of Order and Activity. If this is the case, then Jzwp's initial response was the ticket. However, if this is an incorrect interpretation of the original question, then I am unable to imagine what the correct interpretation of the original question could be.

 

[skwilliams]

I do also have a DtTime field. I need the earliest date and time for each order and activity.

I tried the first suggestion with Min(), but it didn't work.

 

[jzwp22]

How didn't it work? Do you have a group of records where all three fields are identical including the date field?

 

[skwilliams]

I've attached a list of data that I'm pulling from. Note I've highlighted a couple records that have duplicate order numbers and activities. But the highlighted ones are the earliest dates and times. Does this make sense?

 

[jzwp22]

Your dtDate field can also be duplicated, so you need to apply the Min() to the TmTime field. Now, if you can have records where order,activity, desc, dtDate and tmtime are the same, you only have the pkID field left in order to distinguish the records.

 

[skwilliams]

Here's my SQL statement

SELECT qryProofFinal2.pkID, qryProofFinal2.Order, qryProofFinal2.Activity, qryProofFinal2.DESC, qryProofFinal2.DtDate, Min(qryProofFinal2.TmTime) AS MinOfTmTime
FROM qryProofFinal2
GROUP BY qryProofFinal2.pkID, qryProofFinal2.Order, qryProofFinal2.Activity, qryProofFinal2.DESC, qryProofFinal2.DtDate;


I still get duplicates even though the two times are different.

 

[skwilliams]

pkID Order Activity DESC DtDate MinOfTmTime
114881 101243775 PROOFSNDCS 6/16/2010 13.6833333333333
114883 101243789 PROOFSNDCS 6/1/2010 12.2666666666667
114887 101243797 PROOFSNDCS 6/1/2010 10.3333333333333
114888 101243797 PROOFSNDCS 6/1/2010 10.8666666666667
114892 101243852 PROOFSNDCS 6/1/2010 9.15
114893 101243852 PROOFSNDCS 6/1/2010 9.35
114902 101243952 PROOFSNDCS 6/1/2010 9.16666666666667
114910 101243967 PROOFSNDCS 6/1/2010 9.16666666666667
114914 101243985 PROOFSNDCS 6/1/2010 9.05
114916 101244002 PROOFSNDCS 6/1/2010 9.83333333333333
114921 101244034 PROOFSNDCS 6/1/2010 9.25
114923 101244058 PROOFSNDCS 6/1/2010 11.2166666666667
114931 101244140 PROOFSNDCS 6/2/2010 10.0333333333333
114932 101244140 PROOFSNDCS 6/2/2010 10.05

 

[jzwp22]

Try the query without the pkID field. That field will always be unique so all records will be returned


SELECT qryProofFinal2.Order, qryProofFinal2.Activity, qryProofFinal2.DESC, qryProofFinal2.DtDate, Min(qryProofFinal2.TmTime) AS MinOfTmTime
FROM qryProofFinal2
GROUP BY qryProofFinal2.pkID, qryProofFinal2.Order, qryProofFinal2.Activity, qryProofFinal2.DESC, qryProofFinal2.DtDate;

 

[skwilliams]

I think that may have done it.

Thanks for the help!