Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    8

    Red face Unanswered: Where's my brain! This should be easy!?!

    I have a query result that looks like this:

    Resource Date Out Date In
    Laptop1 12/12/04 12/15/04
    Laptop2 12/13/04 12/17/04
    Laptop2 1/23/05 1/27/05
    Laptop2 2/10/05 2/13/05
    Laptop3 1/2/05 1/5/05
    Laptop3 1/24/05 1/28/05

    What I need is just the last date out of each item:

    Resource Date Out Date In
    Laptop1 12/12/04 12/15/04
    Laptop2 2/10/05 2/13/05
    Laptop3 1/24/05 1/28/05


    Any help would be appreciated.

    Bob

  2. #2
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Create a query and select your fields.

    Put a MAX on the Out date and this should do it!

  3. #3
    Join Date
    Feb 2005
    Posts
    8
    Do I do this in the totals row or specify something in criteria row?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Where's your brain? Didn't I just see that run screaming from the building???

    Answer: You need to use a subquery for this ... Check out this thread (and specifically Rudy's response): http://www.dbforums.com/t802112.html
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by BobMcNeill
    I have a query result that looks like this:

    Resource Date Out Date In
    Laptop1 12/12/04 12/15/04
    Laptop2 12/13/04 12/17/04
    Laptop2 1/23/05 1/27/05
    Laptop2 2/10/05 2/13/05
    Laptop3 1/2/05 1/5/05
    Laptop3 1/24/05 1/28/05

    What I need is just the last date out of each item:

    Resource Date Out Date In
    Laptop1 12/12/04 12/15/04
    Laptop2 2/10/05 2/13/05
    Laptop3 1/24/05 1/28/05


    Any help would be appreciated.

    Bob
    In SQL you can do this with a 'group by' statement, combined with 'minimum'... The exact syntax escapes me, but you can find it on the web..

    Another less chique solution could be some sort of loop, where you assign the first entry to a variable and checks all other entries with this and replace it... In structure smt like:

    x = first date where resource = "laptop2"
    ref = x
    move x to next entry where resource = "laptop2"
    if x > ref then ref=x

    Again, I don't know syntaxis yet but I know the above is possible in Access/VBA... Hope this helps

Posting Permissions

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