Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Should be simple Query Question

    I have a table that looks like this
    Date, Truck, Miles
    1/1/2006, 700, 30000
    1/15/2006, 700, 30100
    1/7/2006, 700, 30050
    1/1/2006, 701, 5000
    1/17/2006, 701, 5100
    1/20/2006, 701, 5200

    I need to return the truck number and the miles where the date is the max.

    Such as this
    Date, Truck, Miles
    1/15/2006, 700, 30100
    1/20/2006, 701, 5200

    It seems very simple. I thought my statement should return miles and truck, group by truck where date is max of date.

    If I leave miles out it works, if I put miles in it returns every record.



    Here is my statement.
    SELECT Gas_Tickets_Tbl.Truck, Gas_Tickets_Tbl.Miles
    FROM Gas_Tickets_Tbl
    GROUP BY Gas_Tickets_Tbl.Truck, Gas_Tickets_Tbl.Miles;

    Thanks
    Brandon

  2. #2
    Join Date
    Sep 2005
    Posts
    5
    Try something like this:

    SELECT Max(Date) AS MaxforDate, Truck, Last(Miles) AS LastforMiles
    FROM tblTable
    GROUP BY Truck

    Since you use MAX, you want the pointer to get the last post.

    I hope I am right. Anyhow in my little test I got the right answer.

    /Bengt

  3. #3
    Join Date
    Sep 2005
    Posts
    5
    I was wrong! The Select phrase in my former reply DON'T give the right answer. It seemed to be right only because the posts in the table was ordered ascending.

    But I have tried again, and as before, it gives the right answer. More solid this time, I hope.

    So try this (I named my table Trucks):

    SELECT Truck, Miles, Date
    FROM Trucks
    GROUP BY Truck, Miles, Date
    HAVING (Date IN
    (SELECT MAX(Date) AS MaxDate
    FROM Trucks AS Trucks_1
    GROUP BY Truck))

    regards / Bengt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query (Date Question)
    By cillajones in forum Queries
    Replies: 1
    Last Post: 08-09-2008, 12:05 PM
  2. update query question
    By blusk06 in forum Queries
    Replies: 3
    Last Post: 05-30-2008, 05:55 AM
  3. Simple but hard question
    By andymok in forum Reports
    Replies: 1
    Last Post: 09-20-2007, 09:19 AM
  4. Simple record lookup?
    By Transeau in forum Access
    Replies: 0
    Last Post: 01-18-2006, 10:27 PM
  5. Probably a simple report question
    By gmurrie in forum Reports
    Replies: 1
    Last Post: 12-28-2005, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums