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

    Re: Filtering in Query (2002)

    Please mind your language in the Lounge <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If I understand correctly, your table can contain several records with the same order number. If so, you can do what you want in a series of steps:

    - Create a new query based on the table. Add the order number and the relevant date/time field to the query design grid.
    - Click the Totals button on the toolbar or select View | Totals.
    - The Total option for the order number should remain "Group By".
    - Change the Total option for the date/time field to Max.
    - Switch to datasheet view. You should see each order number just once, with the most recent date for that order number.
    - Close and save this query, as say, qryMaxDate.

    - Create a new query based on the table and on qryMaxDate. Join them on the date field vs the MaxOf... field.
    - Add the order number and the status field from the table to the query grid.
    - Add criteria for the status field to select only non-filled orders.
    - Clear the Show check box for the status field.
    - Switch to datasheet view. The query should only return order numbers for which the most recent status is non-filled.
    - Close and save this query.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Query (2002)

    Hope thats just a typo error... <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    Regards,
    Rudi

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Query (2002)

    Hi Hans,

    I don't think my last post wen through. Anyway, that was a typo, but the way today is going, it could've been a freudian slip! Sorry!

    Thanks for the advise. I'll try it and let you know if it gives me what I need!

    Thanks,
    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Query (2002)

    WOW! As always I'm in awe! Worked as I hoped it would.

    Thanks,
    Leesha

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filtering in Query (2002)

    Hi,

    I have a table that stores data based on shifts that are filled, unfilled, canceled etc. Each order number may or may not have more than one status. For example, order 001 may have been "filled" then "canceled" then "filled" or not filled again. The query needs to show only the order numbers that remained unfilled. If I filter simply by "unfilled" I have no way of knowing whether the order did get filled at some point. How would I set up the query so it searches each order number to determine this info. If it helps, each order number has a "care date " and "time Stamp" associated with it. I'm assuming the time stamp could be used to determine which status was the last one entered.

    Thanks,
    Leesha

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

    Re: Filtering in Query (2002)

    Just to preserve the family nature of the lounge, I added the "f" character so it now reads shifts.
    Wendell

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

    Re: Filtering in Query (2002)

    There is nothing worse than when something gives you the shifts.

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering in Query (2002)

    Hi Pat!

    The day would not have been complete without some ribbing on this one from you. You guys have certainly had fun with me today!

    Leesha

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

    Re: Filtering in Query (2002)

    Hi Leesh

    Always glad to help <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Posting Permissions

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