Unanswered: More complex query for data statistics
I’m creating an Access 2003 DB that tracks Actions. Each Action has Milestone dates. There are several types of Milestone. For each Milestone type, there can be more than 1 date because we track previous revisions of the dates.
This is a little confusion so I posted the DB file. Take a look at query “qryVehiclePurchase.” For Action “Vehicle Purchase” it has a total of 5 Milestone dates, with 1 being type 1, 3 being type 2, and 1 being type 3. So Action to Milestones is basically a 1-to-many relationship.
Here is the hard part of what I’m trying to do. I have to find Actions and compare the latest Milestone (Ms) dates of type 2 and type 3 to a “date x”. So it is:
Ms date Type 2 < date x < Ms date Type 3 (using latest Ms dates for each type)
I’m able to find the latest dates in query “qryLatestDates.” But I can’t create a query to display Action(s) of the above comparison. I created queries “qryCompare” and “qryCompare2” and I used 4/20/2003 as the “date x” comparison. The 2 queries don’t display the correct result. The correct query should show Action “SEAS.” How can I create a query (that’ll eventually be the source of a report) that is right? I’m not an advanced programmer but I tried to use SQL but couldn’t get it right.
SELECT qryLatestDates.ActionID, qryLatestDates.ActionName, qryLatestDates.MilestoneID, qryLatestDates.MilestoneTypeID, qryLatestDates.MilestoneType, qryLatestDates.MikestoneDate
WHERE (((qryLatestDates.MilestoneTypeID)=2 Or (qryLatestDates.MilestoneTypeID)=3) AND ((qryLatestDates.MikestoneDate)<#4/20/2003# And (qryLatestDates.MikestoneDate)>#4/10/2003#));
Basically, what I have to do is create a form with a textbox. The requirement from user is “Give me all the Actions where “date x” is between the Action’s latest MS date Type 2 and MS date Type 3. Therefore, users will only enter ONE date in the textbox.
With your SQL it does give the correct record. But how will I know to use 4/10/2003 for “date x” = 4/20/2003? Keep in mind user only enters ONE date. The SQL works when Action with MS date Type 2 falls between 4/10/2003 and 4/20/2003 but that’s not always the case.
Ok, i think I got it now. There is probably a way of doing it with just one query, but I like breaking it down into easier queries and then making one final query. So basically you would want a query based on the qryLatestDates that selects only type 2s. You would then want another query based on qryLatestDates that selects only type 3s. Then your final query would look up from those 2 queries where the date provided is less than one query and greater than the other.
If the file wasnt read only I would create the queries for you and post the SQL, but I cant create queries in a readonly file
Here is another file with 3 more queries “qryNewCompare1”, “qryNewCompare2", and “qryNewFinal.” I created them based on your advice. But I don’t think my final query, qryNewFinal, is the same as what you stated but so far it looks ok.
It’ll be nice to be able to write 1 query to find the solution but I think that’s beyond me.
By the way, I didn’t make the file read only. I think if you open directly from zip it’ll be read only. But if you extract the file to your pc you should be able to edit.
SELECT tblAction.ActionName, qryNewCompare1.MikestoneDate, qryNewCompare2.MikestoneDate
FROM qryNewCompare2 INNER JOIN (qryNewCompare1 INNER JOIN tblAction ON qryNewCompare1.ActionID = tblAction.ActionID) ON qryNewCompare2.ActionID = tblAction.ActionID
WHERE (((qryNewCompare1.MikestoneDate)<#4/20/2003#) AND ((qryNewCompare2.MikestoneDate)>#4/20/2003#));
Ok, here is the final file. I have changed the final query to prompt for a date so that you can key in any date you want. I tested it with both 4/20/03 and 3/18/04 and it worked, pulling up SEAS for the first date and Vehicle Purchase for the second date