Selection Query not working properly

[zipaway]

Hello I have the following query:





SELECT ShipDeliveryDates.Project_Number, Min(ShipDeliveryDates.Sched_Delivery_Date) AS Sched_Next_Delivery_Date, ShipDeliveryDates.Act_Delivery_Date
FROM ShipDeliveryDates
GROUP BY ShipDeliveryDates.Project_Number, ShipDeliveryDates.Act_Delivery_Date;

Using this query I would like to pick out all the projects with their upcoming delivery dates. The query is not working properly, all the delivery dates show up for each project.
The query works properly if I delete the selection of Act_Delivery_Date, but I need it there.

Any help would be welcomed.

[June7]

They show up because you are grouping on the date field. Instead of GROUP BY under that field maybe use Min.

Maybe you need criteria under one of the date fields: >=Date()

[zipaway]

They show up because you are grouping on the date field. Instead of GROUP BY under that field maybe use Min.

Maybe you need criteria under one of the date fields: >=Date()

In case something hasn't been shipped I don't want to have >=Date().

If I use Min under that field a date that has an Act_Delivery_Date not corresponding to the Sched_Delivery_Date can show up. The two showing up in this query must always be under the same record in the ShipDeliveryDates table.

[June7]

Doesn't this table have only 1 record for each project? If so, grouping is meaningless. Need to refine the filter criteria.

Do you want all records with an upcoming Sched_Delivery_Date?

Do you want all records where the Act_Delivery_Date is blank?

Do you want both criteria?

[zipaway]

Doesn't this table have only 1 record for each project? If so, grouping is meaningless. Need to refine the filter criteria.

Do you want all records with an upcoming Sched_Delivery_Date?

Do you want all records where the Act_Delivery_Date is blank?

Do you want both criteria?

The ShipDeliveryDates table can have more than 1 record for each project:

Project_Number	Shipment_Number	Sched_Ship_Date	Sched_Delivery_Date	Actual_Ship_Date	Actual_Delivery_Date
1020	1	30/07/2014	02/08/2014
1020	2	31/07/2014	03/08/2014

[June7]

So what records do you want? The latest Shipment_Number for each Project_Number? There is an example of that in post 24 of your other thread https://www.accessforums.net/program...71/index2.html

Assuming Shipment_Number is unique, try:

SELECT * FROM ShipDeliveryDates WHERE Shipment_Number IN (SELECT Max(Shipment_Number) AS SN FROM ShipDeliveryDates GROUP BY Project_Number);

[zipaway]

Ok so I just thought about it. What I want is a query that will give me the upcoming shipment for each project. From the table below it would select:

1020, 30/07/2014, ""

Project_Number	Shipment_Number	Sched_Ship_Date	Sched_Delivery_Date	Actual_Ship_Date	Actual_Delivery_Date
1020	1	30/07/2014	02/08/2014
1020	2	31/07/2014	03/08/2014

However from the table below it would select:

1020, 31/07/2014, "" ---> It selected the second one because the first one has already been shipped (has actual ship date)

Project_Number	Shipment_Number	Sched_Ship_Date	Sched_Delivery_Date	Actual_Ship_Date	Actual_Delivery_Date
1020	1	30/07/2014	02/08/2014	30/07/2014
1020	2	31/07/2014	03/08/2014

[June7]

So you want only the first scheduled shipment where Actual_Ship is blank?

SELECT * FROM ShipDeliveryDates WHERE Shipment_Number IN (SELECT Min(Shipment_Number) AS SN FROM ShipDeliveryDates WHERE Actual_Ship_Date Is Null GROUP BY Project_Number);

[zipaway]

So you want only the first scheduled shipment where Actual_Ship is blank?

SELECT * FROM ShipDeliveryDates WHERE Shipment_Number IN (SELECT Min(Shipment_Number) AS SN FROM ShipDeliveryDates WHERE Actual_Ship_Date Is Null GROUP BY Project_Number);

Hmm, this doesn't seem to work properly. If the first one has an actual_ship_date it still picks it up.

[zipaway]

Oh I figured it out. This is what I should use.

SELECT ShipDeliveryDates.Project_Number, Min(ShipDeliveryDates.Sched_Shed_Date) AS Sched_Next_Ship_Date
FROM ShipDeliveryDates
GROUP BY ShipDeliveryDates.Project_Number, ShipDeliveryDates.Act_Ship_Date
HAVING (((ShipDeliveryDates.Act_Ship_Date) Is Null));