Query not doing what I want it to

[zipaway]

I now have another issue



So I have a table:

ShipDeliveryDates
ID (Primary Key)
Project_Number
Component_Number
Sched_Ship_Date
Sched_Delivery_Date





And I had wanted to create a query to get all the current dates for each project. I had wanted both the 'upcoming' Sched_Ship_Date and the 'upcoming' Sched_Delivery_Date relative to today's date for each project.The query below only works when you don't try to get both the current Sched_Ship_Date and the Sched_Delivery_Date at the same time (works if ignoring one of the two). I can understand what is causing this problem by reading the query but I can't come up with the solution to make the query below work like I want it to.

Query

SELECT ShipDeliveryDates.Project_Number, Min(ShipDeliveryDates.Sched_Ship_Date) AS Sched_Next_Ship_Date, Min(ShipDeliveryDates.Sched_Delivery_Date) AS Sched_Next_Delivery_Date
FROM ShipDeliveryDates
WHERE (((ShipDeliveryDates.Sched_Ship_Date)>=Date()) And ((ShipDeliveryDates.Sched_Delivery_Date)>=Date()))
GROUP BY ShipDeliveryDates.Project_Number;

[June7]

Options:

1. two queries to retrieve the two dates then join the queries in a third query

2. domain aggregate function (DLookup, DSum, DAvg, DMin, etc), beware - these functions can perform slowly in queries and on forms/reports; I presume you have a table of unique project numbers called maybe Projects

SELECT Project_Number,
DMin("Sched_Ship_Date", "ShipDeliveryDates", "Project_Number=" & [Project_Number] & " AND Sched_Ship_Date>=Date()") AS Sched_Next_Ship_Date,
DMin("Sched_Delivery_Date", "ShipDeliveryDates", "Project_Number=" & [Project_Number] & " AND Sched_Delivery_Date>=Date()") AS Sched_Next_Delivery_Date
FROM Projects;

[zipaway]

Options:

1. two queries to retrieve the two dates then join the queries in a third query

2. domain aggregate function (DLookup, DSum, DAvg, DMin, etc), beware - these functions can perform slowly in queries and on forms/reports; I presume you have a table of unique project numbers called maybe Projects

SELECT Project_Number,
DMin("Sched_Ship_Date", "ShipDeliveryDates", "Project_Number=" & [Project_Number] & " AND Sched_Ship_Date>=Date()") AS Sched_Next_Ship_Date,
DMin("Sched_Delivery_Date", "ShipDeliveryDates", "Project_Number=" & [Project_Number] & " AND Sched_Delivery_Date>=Date()") AS Sched_Next_Delivery_Date
FROM Projects;

Wow I never thought about merging two queries together, thank you so much for your help.