Results 1 to 7 of 7
  1. #1
    PHILnTEX is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    6

    Query to show only records base on most recent date change

    Hey all,
    Please help! I’m new to Access and Sql. I have a table that looks something like the one below. I want the resulting query to only show the records with the most recent date for each item. If I simply use max() on the ModifiedDate field it returns all records with the most resent date for each item, but doesn’t remove the other records and i still end up with 9 records.


    UniqueID ITEM PRICE ModifiedDate
    232323 item-1 $1.23 1/1/2017
    111344 item-1 $3.12 12/11/2016
    11111567 item-1 $2.17 12/13/2016
    232300 item-2 $7.23 1/12/2017
    119989 item-2 $8.43 11/1/2016
    12202211 item-2 $6.23 12/13/2016
    232212 item-3 $14.22 2/13/2017
    122002 item-3 $13.14 10/11/2016


    11887556 item-3 $12.12 12/23/2016


    Looking for the result to be this (just 3 records):
    UniqueID ITEM PRICE ModifiedDate
    232323 item-1 $1.23 1/1/2017
    232300 item-2 $7.23 1/12/2017
    232212 item-3 $14.22 2/13/2017


    Code:
    
    
    Code:
    SELECT TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate
    FROM TempTable
    GROUP BY TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE;



    Ideas?
    Thanks in advance

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Group on items

    And use max for the date field?


    Sent from my iPhone using Tapatalk

  3. #3
    PHILnTEX is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    6
    Hi andy49,
    Hmmm, isn't that what i'm dong?

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Did you create this in query design view?


    Sent from my iPhone using Tapatalk

  5. #5
    PHILnTEX is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    6
    andy49,
    Yes, querying the TempTable.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Long answer, create a group query on just the two columns item and date (using max). Then inner join results of this query to the other fields you need.

    Someone will do it in 1 sql statement if you can wait


    Sent from my iPhone using Tapatalk

  7. #7
    PHILnTEX is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    6
    andy49,
    Thanks for the suggestion. I actually tried that and it does reduce the records to the 3 the I'm looking for but as soon as I join another query that has the UniqueID it puts all 9 records back in.

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

Similar Threads

  1. return most recent records by date stored as text
    By mindbender in forum Queries
    Replies: 3
    Last Post: 04-27-2016, 07:00 AM
  2. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  3. Replies: 5
    Last Post: 02-27-2014, 08:25 PM
  4. Most recent Date query
    By SmartestIdiot in forum Queries
    Replies: 1
    Last Post: 01-11-2014, 07:56 AM
  5. Replies: 17
    Last Post: 07-31-2013, 11:35 AM

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