Filtering for Lowest Date out of Duplicate records

[homeguard]

I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.

 

[pbaldy]

2 queries. The first gets the earliest date:

SELECT OrderField, Min(DateField) AS FirstOrder
FROM TableName
GROUP BY OrderField

The second query would join the original table to the first query on those 2 fields, enabling you to return the other information from that record.

 

[homeguard]

Here is what I have:

SELECT tblPSC.OrderNum, Min( tblPSC.Date) AS FirstOrder
FROM tblPSC;
GROUP BY tblPSC.OrderNum

I got this error Msg
"The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect"

 

[homeguard]

I figured it out, thanks for the help


SELECT Min(tblPSC.Date) AS MinOfDate, tblPSC.OrderNum
FROM tblPSC
GROUP BY tblPSC.OrderNum;

http://office.microsoft.com/en-us/access/HA010345581033.aspx

 

[pbaldy]

Sorry, I was sound asleep when you posted. Glad you figured it out. By the way, "date" is not a good field name, as it can conflict with the built in Date() function:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187