Using subquery to return top 1 record within groups of records (1 Viewer)

Weegee71

New member
Local time
Today, 07:49
Joined
Mar 17, 2022
Messages
4
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?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:49
Joined
May 7, 2009
Messages
19,169
you may try this Query:

SELECT tDespatch.*
FROM tDespatch
INNER JOIN
(SELECT tDespatch.Sales_Order_Number, Min(tDespatch.Despatch_Date) AS MinOfDespatch_Date
FROM tDespatch
GROUP BY tDespatch.Sales_Order_Number) AS T1
ON tDespatch.Sales_Order_Number = T1.Sales_Order_Number And tDespatch.Despatch_Date = T1.MinOfDespatch_Date
 

Weegee71

New member
Local time
Today, 07:49
Joined
Mar 17, 2022
Messages
4
you may try this Query:

SELECT tDespatch.*
FROM tDespatch
INNER JOIN
(SELECT tDespatch.Sales_Order_Number, Min(tDespatch.Despatch_Date) AS MinOfDespatch_Date
FROM tDespatch
GROUP BY tDespatch.Sales_Order_Number) AS T1
ON tDespatch.Sales_Order_Number = T1.Sales_Order_Number And tDespatch.Despatch_Date = T1.MinOfDespatch_Date
Hi there

I tried using that but got an error message 'Syntax Error in FROM Clause'
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:49
Joined
May 7, 2009
Messages
19,169
query.jpg


result.jpg
 

Weegee71

New member
Local time
Today, 07:49
Joined
Mar 17, 2022
Messages
4
OK Im not getting that.
Please see attached screenshot
NB Im using Access 97
 

Attachments

  • Access_From.PNG
    Access_From.PNG
    19.5 KB · Views: 140

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:49
Joined
May 7, 2009
Messages
19,169
i don't have 97 so i cannot test on that environment.
 

Weegee71

New member
Local time
Today, 07:49
Joined
Mar 17, 2022
Messages
4
Can you confirm that the table is called "tDespatch".
Can you post a copy of the db.
Hi Bob Sure please find a copy of the database attached.
 

Attachments

  • SerialNumberDB2.mdb
    90 KB · Views: 147

Users who are viewing this thread

Top Bottom