Find oldest of duplicate records

[skwilliams]

What if I wanted to add another unique field beside the time field. Is that possible?

 

[jzwp22]

You have a unique field already with the pkID field. If there is a chance that the value in all fields can be the same then your query would have to be altered to find the min(pkID)

SELECT qryProofFinal2.Order, qryProofFinal2.Activity, qryProofFinal2.DESC, qryProofFinal2.DtDate, qryProofFinal2.TmTime, min(qryProofFinal2.pkID) as MinOfPkID
FROM qryProofFinal2
GROUP BY qryProofFinal2.pkID, qryProofFinal2.Order, qryProofFinal2.Activity, qryProofFinal2.DESC, qryProofFinal2.DtDate, qryProofFinal2.TmTime

 

[skwilliams]

The field I'd like to add to the query is a fixed decimal field. But it won't necessarily be the lowest amount. I still need to just retrieve the records for the earliest date and time but also display this field (TotDiff). I don't need the pkID field.

Thanks.

 

[jzwp22]

What fields are used to determine the DiffTot?

 

[skwilliams]

Not any of the fields listed. The TotDiff values are already in the table.

 

[jzwp22]

So you want to display the value in TotDiff for the records returned by the earlier query?

To do that create a new query, put both qryProofFinal2 plus the query that returns the min() that we have been working on. Join the two queries via the following fields :

Order
Activity
DESC
DtDate
MinOfTmTime to tmtime

Then select the fields you want in your result set including the TotDiff field

 

[skwilliams]

Your a genius!!

Thanks.

 

[jzwp22]

You're welcome. Glad we could all help out.

 

[SteveSchapel]

Phew!! Nice.