Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    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.

    Sorry for the long text. Thank you.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Looking at the criteria on the query qrycompare it is looking for Mikestone date that is both less than 4/20/03 and also greater than 4/20/03

    When changing the greater than to 4/10/03 it did give me just the one match you are looking for

  3. #3
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Heres the SQL code for it:
    Code:
    SELECT qryLatestDates.ActionID, qryLatestDates.ActionName, qryLatestDates.MilestoneID, qryLatestDates.MilestoneTypeID, qryLatestDates.MilestoneType, qryLatestDates.MikestoneDate
    FROM qryLatestDates
    WHERE (((qryLatestDates.MilestoneTypeID)=2 Or (qryLatestDates.MilestoneTypeID)=3) AND ((qryLatestDates.MikestoneDate)<#4/20/2003# And (qryLatestDates.MikestoneDate)>#4/10/2003#));

  4. #4
    Join Date
    Jun 2007
    Posts
    5
    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.

  5. #5
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    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

  6. #6
    Join Date
    Jun 2007
    Posts
    5
    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.
    Attached Files Attached Files

  7. #7
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Quote Originally Posted by NOVAdude
    I think if you open directly from zip it’ll be read only.
    Boy do I feel dumb. I should have known that

  8. #8
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    Ok, SQL for FinalQuery

    Code:
    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#));

  9. #9
    Join Date
    Jun 2007
    Posts
    5
    Ah ok, thanks for your help Blade.

  10. #10
    Join Date
    Dec 2004
    Location
    Coon Rapids, Minnesota
    Posts
    41
    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
    Attached Files Attached Files

  11. #11
    Join Date
    Jun 2007
    Posts
    5
    My next step is the prompt, which is what you did. But I'm glad I got the hard part (comparing Milestone dates for Actions) taken care of. Thanks again blade.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •