Extra records when used as a sub query: Access

Question

I'm a rookie developer with basic SQL experience and this problem has been 'doing my head in' for the last couple of days. I've gone to ask a question here a couple times and thought... not yet... keep trying.

I have a table:

• ID
• Store
• Product_Type
• Delivery_Window
• Despatch_Time
• Despatch_Type
• Pallets
• Cartons
• Many other columns (start_week and day_num are two of them)

My goal is to get a list of of store by product_type with the minimum despatch_time with all the other column information.

I've tested the base query.

SELECT Product_Type, Store, Min(Despatch_Time) as MinDes
FROM table
GROUP BY Store, Product_Type

Works well, I get 200 rows as expected.

Now I want those 200 rows to have the other related record information : Delivery_Window, start_week, etc

I've tried the following.

SELECT * FROM Table WHERE EXISTS
(SELECT Product_Type, Store, Min(Despatch_Time) as MinDes
FROM table
GROUP BY Store, Product_Type)

I've tried doing inner and right joins all returned more than 200 records, my original amount.

I inspected the additional records and it is where there is the same despatch time for a store and product type but for a different despatch type.

So I need a hand in creating a query where I limit it by the initial sub query but even if there is matching minimum despatch times it will still limit the count to one record by store and product type.

Current Query is:

SELECT *
FROM table AS A INNER JOIN 
(Select Min(Despatch_Time) as MinDue, store, product_type
FROM table
WHERE day_num = [Forms]![FRM_SomeForm]![combo_del_day] AND start_week =[Forms]![FRM_SomeForm]![txt_date1]
GROUP BY store, product_type) AS B 
ON (A.product_type = B.product_type) AND (A.store = B.store) AND (A.Despatch_Time = B.MinDue);

Answer 1

I think you want:

SELECT t.*
FROM table as t
WHERE t.Dispatch_Time = (SELECT MIN(t2.Dispatch_Time)
                         FROM table as t2
                         WHERE t2.Store = t.Store AND t2.Product_Type = t.Product_Type);

The above will return duplicates. In order to avoid duplicates, you need a key to provide uniqueness. Let me assume you have a primary key pk:

SELECT t.*
FROM table as t
WHERE t.pk = (SELECT TOP (1) t2.pk
              FROM table as t2
              WHERE t2.Store = t.Store AND t2.Product_Type = t.Product_Type
              ORDER BY t2.Dispatch_Time, t2.pk
             );