Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: newest date query

    I have a querry named "reminder labels" that get data from "Service tabel" & "Customer Tabel'what i need to do is Have the user select between dates and have the querry get the newest service date for for each customers, every customer has multiple dates. I have everything working except the part were the user select the starting and end dates

    Here is my SQL So far

    SELECT Customers.TankID, Customers.[First Name], Customers.[Last Name], Customers.Address, Customers.City, Customers.State, Customers.[Zip Code], Customers.[Filter?], S1.[Date of Service]FROM Customers INNER JOIN Service AS S1 ON Customers.TankID = S1.TankIDWHERE (((S1.[Date of Service]) In (SELECT Max([Date of Service]) FROM Service WHERE Service.TankId = S1.TankId)));

  2. #2
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    Let me make sure I understand:

    The user will select a starting point and ending point from a range of dates (for example 1/1/04 and 2/1/04).

    From there, you want the query to:

    1) Dynamically read the user selected criteria for a starting and stopping point, incorporating this into the WHERE clause of the query

    2) Display the most recent service date for each customer that has had at least one service date for the timeframe indicated by the user

    Is this correct?

  3. #3
    Join Date
    Jul 2004
    Posts
    5
    yes that what i want it to do

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Put your date parameters inside the subselect where you're pulling the date:


    SELECT Customers.TankID, Customers.[First Name], Customers.[Last Name], Customers.Address, Customers.City, Customers.State, Customers.[Zip Code], Customers.[Filter?], S1.[Date of Service]FROM Customers INNER JOIN Service AS S1 ON Customers.TankID = S1.TankIDWHERE (((S1.[Date of Service]) In (SELECT Max([Date of Service]) FROM Service WHERE Service.TankId = S1.TankId AND [Date of Service] BETWEEN [start date parameter goes here] AND [end date parameter goes here])));

    alternately you could use a DLookup in the main select statement itself and avoid pulling a whole dataset for a single value.

    Provided your db isn't massive it's purely a matter of style.
    Last edited by Teddy; 07-13-04 at 10:18.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2004
    Posts
    5

    Smile

    Thank thats what i wanted

Posting Permissions

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