Results 1 to 3 of 3
  1. #1
    ThomasLonsdale is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2015
    Posts
    3

    Removing rows from a query based on date

    Hey,

    I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:



    DATE ID
    01/01/2015 1
    02/01/2015 1

    What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.

    I'm unsure what the easiest way to do this would be (or how to do it at all).

    I am eagerly awaiting your input .

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    859
    Hi,

    you can create 2 queries:
    - one groupsquery: select [ID], max([DATE] from Mytable group by [ID].
    - one query where you link the table data to the results of query 1, keeping only the rows of the most recent dates

  3. #3
    ThomasLonsdale is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2015
    Posts
    3
    Thanks! I left out the detail that there are also two other columns (Company sent to and Comments sections) so I added these to the group by part and I've managed to get something that works (using the max function) for me and produces the results I need to show for my colleague.

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

Similar Threads

  1. Replies: 4
    Last Post: 04-22-2014, 08:23 AM
  2. Filtering query based on multiple rows values
    By GeorgeB in forum Queries
    Replies: 6
    Last Post: 12-05-2013, 06:17 PM
  3. Help : Query based on data from multiple rows
    By GeorgeB in forum Queries
    Replies: 7
    Last Post: 03-28-2012, 11:27 PM
  4. Query to find and COMBINE rows based on 2 fields
    By admessing in forum Queries
    Replies: 2
    Last Post: 12-13-2011, 12:59 PM
  5. Replies: 2
    Last Post: 08-28-2011, 06:06 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