How do i eliminate rows of data after using distinct keyword.

[valeron777]

hi all,
I am working on outputting data to be read into excel from access DB.
The situation now is that im having problems filtering off rows of data which are the same but only having 1 field indifferent from the rest.
Example : SELECT DISTINCT Orders.No, PartNo, CustName, CustAdd, DateReceived FROM Orders;

OrderNo PartNo CustName CustAdd DateReceived
1 FF1 ABC ABCADD 14/7/2007
1 FF1 ABC ABCADD 15/7/2007

how do i write an SQL to remove the later date because i only want the row of data from the earlier date(14/7/2007)

i cant change the db design to help me so is there anyway?

[Delerna]

if the only difference is the date then this should work for you
[code=sql]
SELECT OrderNo, PartNo, CustName, CustAdd, min(DateReceive d) as DteReceived
FROM Orders
GROUP BY OrderNo, PartNo, CustName, CustAdd;

[/code]

[valeron777]

yes date is the only difference. i am using query builder in access and it doesnt seem to be able to compile the min() function

[valeron777]

i still get the same result set after running that line :(

[valeron777]

ok it worked!
thanks!

[Delerna]

You're welcome, what was wrong, if you don't mind posting it