Hi there
I have an Access 97 database with a table called tDespatch
Now this contains group of records which have a Order_Number
Columns in the table are as follows:
Serial_Number
Sales_Order_Number
Despatch_Date
RepCalNumber
Now there are repeating groups of records which contain Sales_Order_Number
so for example you would have :
Serial_Number Sales_Order_Number Despatch_Date RepCalNumber
3 36671 16/02/2012 2047312
7 36671 20/03/2012 2077358
3 52064 16/02/2012 2067339
3 52064 17/02/2012 2067339
3 52064 18/02/2012 2067339
7 52510 20/03/2012 2033939
7 52510 23/03/2012 2033939
7 52510 24/05/2012 2033939
What I want to be able to do , is for each group of records (by Sales_Order_Number),
take the top 1 record (Order by Despatch_Date)
so I would have something like, ie selecting the top 1 record (Despatch_Date Ascending)
Serial_Number Sales_Order_Number Despatch_Date RepCalNumber
3 36671 16/02/2012 2047312
3 52064 16/02/2012 2067339
7 52510 20/03/2012 2033939
I had written the following query but its only yielding me 1 record
SELECT top 1 tDespatch.Serial_Number, tDespatch.Sales_Order_Number, tDespatch.Despatch_Date, tDespatch.RepCalNumber
FROM tDespatch
WHERE (((tDespatch.Sales_Order_Number) In (SELECT distinct Gemini_Order_Number
FROM tDespatch
)))
GROUP BY tDespatch.Serial_Number, tDespatch.Gemini_Order_Number, tDespatch.Despatch_Date, tDespatch.RepCalNumber;
What am I doing wrong here?
I have an Access 97 database with a table called tDespatch
Now this contains group of records which have a Order_Number
Columns in the table are as follows:
Serial_Number
Sales_Order_Number
Despatch_Date
RepCalNumber
Now there are repeating groups of records which contain Sales_Order_Number
so for example you would have :
Serial_Number Sales_Order_Number Despatch_Date RepCalNumber
3 36671 16/02/2012 2047312
7 36671 20/03/2012 2077358
3 52064 16/02/2012 2067339
3 52064 17/02/2012 2067339
3 52064 18/02/2012 2067339
7 52510 20/03/2012 2033939
7 52510 23/03/2012 2033939
7 52510 24/05/2012 2033939
What I want to be able to do , is for each group of records (by Sales_Order_Number),
take the top 1 record (Order by Despatch_Date)
so I would have something like, ie selecting the top 1 record (Despatch_Date Ascending)
Serial_Number Sales_Order_Number Despatch_Date RepCalNumber
3 36671 16/02/2012 2047312
3 52064 16/02/2012 2067339
7 52510 20/03/2012 2033939
I had written the following query but its only yielding me 1 record
SELECT top 1 tDespatch.Serial_Number, tDespatch.Sales_Order_Number, tDespatch.Despatch_Date, tDespatch.RepCalNumber
FROM tDespatch
WHERE (((tDespatch.Sales_Order_Number) In (SELECT distinct Gemini_Order_Number
FROM tDespatch
)))
GROUP BY tDespatch.Serial_Number, tDespatch.Gemini_Order_Number, tDespatch.Despatch_Date, tDespatch.RepCalNumber;
What am I doing wrong here?