Results 1 to 4 of 4
  1. #1
    Villa123 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2016
    Posts
    9

    Trying to select the latest date by month

    I have 2 tables: in Table 1 are a list of projects and data by month surrounding those projects. In Table 2 it contains ratings of the projects reference in Table 1, but it is on a weekly basis.



    I am attempting to combine the data from both tables, but I only want to include information from Table 2 for the latest date range/month.

    Example:

    Table 1
    Project 1 June Data
    Project 1 July Data
    Project 2 June Data
    Project 2 July Data

    Table 2
    Project 1 6/13/16
    Project 1 6/20/16
    Project 1 7/4/16
    Project 1 7/11/16
    Project 2 6/20/16
    Project 2 6/27/16
    Project 2 7/11/16
    Project 2 7/18/16


    From the example above, I would want to combine the data from Table 2 to Table 1, but for Project 1 I need 6/20/16 and 7/11/16 data only. And for Project 2 I need 6/27/16 and 7/18/16 data only.


    Projects can end in the middle of a month, so the projects rating may not always be the last week of the month.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Get the MAX

    select project,Max(projDate) from table2

  3. #3
    Villa123 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2016
    Posts
    9
    within the query i set MAX to the date in table 2, and if i add only the project id... the query runs as it should and pulls the max date from each month per project id. However, when I try to add additional project details it duplicates records for the project. So for Project 1, it will have 4 records (1 for each week of the month), yet the "date" field reflects the "max" date.

    Any advice?

  4. #4
    Villa123 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2016
    Posts
    9
    Ranman256, any help to my latest post?

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

Similar Threads

  1. Replies: 1
    Last Post: 09-06-2014, 01:08 PM
  2. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  3. Query: select latest date within given category
    By martinhough in forum Queries
    Replies: 4
    Last Post: 10-22-2013, 12:34 PM
  4. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  5. Value of latest date
    By v!ctor in forum Queries
    Replies: 3
    Last Post: 02-27-2013, 03:48 PM

Tags for this Thread

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