Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Next Date Query (A2k)

    If in a table we have an item which is associated to several dates.ie:

    Item1| 11/12/2002 | 12/12/2002 | 16/12/2002 | 18/12/2002 etc etc etc

    I can create a query to capture the date if it's today. ie =Date()
    or a further date >Date()

    What I want to do is to capture the next date only and ignoring the rest.
    Considering this example above has four dates associated, If 11/12/2002 was selected, I would want the second date 12/12/2002 showing along side the current.

    Is this possible to accomplish in a query.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Next Date Query (A2k)

    Yes, but the kind if table you have is very suspect from a design point of view. It means you have to have lots of empty fields for items that only have a few dates, and in the limit, you hit the 255 column constraint for tables. What you should be looking at is a table with |ITEM|DATE| and nothing more. The query to do what you propose is more straighforward with this design (hint - use the MIN aggregate function).
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Date Query (A2k)

    Not sure if this is what I was looking for.
    The table contains dates already as entered from a previous form.
    The dates don't have to be sucessive. but just show the next date related to the item

    On the above example I had four dates.
    These are bookiking dates.

    I use a query in a form to show a date when the item is due back:

    SELECT qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate, qrySelectCourtesy.FromDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)>=[forms]![frmcourtesycarplanner]![txtDate]));

    This item may have many dates after, but I just want to show the next date it is booked.
    Thanks for the input.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Next Date Query (A2k)

    Maybe use the Top 1 predicate and descending for sort order.
    No I am wrong here, Wendell has the right idea. Ignore this comment, Wendell had the right idea about the min and combined with the Top 1 predicate it should return the record you want.

    I have just tried the following on my database and it seems to work.
    SELECT TOP 1 Min(Bookings.DateFrom) AS fromdate, Bookings.SiteNo, Bookings.DateTo
    FROM Bookings
    WHERE (((Bookings.DateFrom)>#9/1/1997#))
    GROUP BY Bookings.SiteNo, Bookings.DateTo
    HAVING (((Bookings.SiteNo)=3));

    I think if you change yours from:
    SELECT qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate, qrySelectCourtesy.FromDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)>=[forms]![frmcourtesycarplanner]![txtDate]));

    This should then work, I hope.

    to:
    SELECT Top 1 qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate, Min(qrySelectCourtesy.FromDate) as FromDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)>=[forms]![frmcourtesycarplanner]![txtDate]))
    GROUP BY qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Date Query (A2k)

    Hmmmmmmmmmm

    Thanks Patt. It's not working how I want it to. I'll have to do some experimenting when I find 5

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Next Date Query (A2k)

    Dave, what exactly do you want?

    1. <LI>For each unit individually, find the first ThruDate that falls after the selected date.
      <LI>For each unit individually, find the first date (which can be FromDate or ThruDate) that falls after the selected date.
      <LI>For a specific unit, find the first ThruDate that falls after the selected date.
      <LI>For a specific unit, find the first date (which can be FromDate or ThruDate) that falls after the selected date.
      <LI>Find the first ThruDate for any unit that falls after the selected date.
      <LI>Find the first date (which can be FromDate or ThruDate) for any unit that falls after the selected date.
      <LI>Something else - if so, what?
    If you try to explain precisely what you want to accomplish, you may get more specific help.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Date Query (A2k)

    Hans
    This query returns the date due back on a date that equals a temp date on the main form.Notice => removed)
    The temp date [txtDate] is determine from a calender control.

    SELECT qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate
    FROM qrySelectCourtesy
    GROUP BY qrySelectCourtesy.Unit, qrySelectCourtesy.ThruDate
    HAVING (((qrySelectCourtesy.ThruDate)=[forms]![frmcourtesycarplanner]![txtDate]));

    I'm trying to show the next [fromDate] the vehicle is booked out.

    Sorry if my explanation is vague.

    In the "Cars Due Back" form we see the [Due Back] field showing the dates as selected by the cal control.
    The field to the right.[FromDate], I want to change to [Next Booking] and show the next booking after that selected date.
    Attached Files Attached Files

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Date Query (A2k)

    Thanks all.

    Habs, option 2 is working perfectly.

    Thanks again.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Next Date Query (A2k)

    How about one of these possibilities

    <img src=/w3timages/blueline.gif width=33% height=2>

    1. Two queries:

    SQL for qrySelectThruDate:

    SELECT qrySelectCourtesy.UnitID, qrySelectCourtesy.FromDate, qrySelectCourtesy.ThruDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)=[Forms]![frmCourtesyCarPlanner]![txtDate]));

    SQL for qrySelectNextDate:

    SELECT qrySelectThruDate.UnitID, qrySelectThruDate.ThruDate, Min(qrySelectCourtesy.FromDate) AS MinVanFromDate
    FROM qrySelectCourtesy INNER JOIN qrySelectThruDate ON qrySelectCourtesy.UnitID = qrySelectThruDate.UnitID
    WHERE (((qrySelectCourtesy.FromDate)>[qrySelectThruDate].[ThruDate]))
    GROUP BY qrySelectThruDate.UnitID, qrySelectThruDate.ThruDate;

    <img src=/w3timages/blueline.gif width=33% height=2>

    2. Alternative in one query:

    SQL for qrySelectNextDate:

    SELECT qrySelectCourtesy.UnitID, qrySelectCourtesy.ThruDate, DMin("FromDate","qrySelectCourtesy","UnitID=" & [UnitID] & " And FromDate>#" & Format([ThruDate],"mm/dd/yy") & "#") AS NextDate
    FROM qrySelectCourtesy
    WHERE (((qrySelectCourtesy.ThruDate)=[Forms]![frmCourtesyCarPlanner]![txtDate]));

Posting Permissions

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