Results 1 to 7 of 7
  1. #1
    Penni is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    3

    Want to return min/max dates for data that references duplicate values in another column

    It's hard to describe - but I have three fields. 1) Site 2)Date 3) Area. For each site there are multiple dates and areas. What I need to do is find the oldest and most recent date for each site and return a table that shows that plus the data in the Area field.



    E.g. resulting data
    Site Date Area
    18279 5/30/02 0.0013
    18279 5/25/16 0.03
    18387 6/03/02 0.0013
    18387 5/26/17 0.00








    See attached sample data file.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Q1, get the Max & min for the Site (turn on summation)
    select SITE, Max(Date) from table
    Q2
    select SITE, min(Date) from table

    Q3: use Q1, Q2, and the table, joined on SITE to get all your info

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try this:
    Code:
    SELECT SurveyData.SiteID, Min(SurveyData.SurveyDate) AS SurveyDate, First(SurveyData.EstimatedArea) AS Area
    FROM SurveyData
    WHERE (((SurveyData.SiteID) In
      (SELECT TOP 1 SiteID                            
       FROM SurveyData AS S                              
       WHERE S.SiteID = SurveyData.SiteID)))
    GROUP BY SurveyData.SiteID
    
    UNION 
    
    SELECT SurveyData.SiteID, Max(SurveyData.SurveyDate) AS SurveyDate, Last(SurveyData.EstimatedArea) AS Area
    FROM SurveyData
    WHERE (((SurveyData.SiteID) In 
       (SELECT TOP 1 SiteID                            
       FROM SurveyData AS S                              
       WHERE S.SiteID = SurveyData.SiteID)))
    GROUP BY SurveyData.SiteID
    Order By SiteID, SurveyDate;;
    NOTE: I named the table "SurveyData" and I removed spaces in the field names.
    Change the table name to your table names and the field names to your field names (no spaces ).



    Based on the data in the Excel file you posted, these are the results:
    SiteID SurveyDate Area
    18279 5/30/2002 0.0013
    18279 5/28/2016 0.03
    18387 6/3/2002 0.0013
    18387 5/26/2017 0
    18395 6/6/2002 1
    18395 7/29/2017 0.01
    18398 6/7/2002 0.001
    18398 7/6/2018 0
    18404 6/18/2002 0.0013
    18404 5/31/2017 0
    18543 6/5/2002 0.0013
    18543 5/27/2017 0

    Close????

  4. #4
    Penni is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    3

    Results are correct but where do I put all those lines of code?

    The results are correct. But, as I've only ever done simple queries, where do I put those lines of code? And then how do I "run" to get the resulting data table?

    Quote Originally Posted by ssanfu View Post
    Try this:
    Code:
    SELECT SurveyData.SiteID, Min(SurveyData.SurveyDate) AS SurveyDate, First(SurveyData.EstimatedArea) AS Area
    FROM SurveyData
    WHERE (((SurveyData.SiteID) In
      (SELECT TOP 1 SiteID                            
       FROM SurveyData AS S                              
       WHERE S.SiteID = SurveyData.SiteID)))
    GROUP BY SurveyData.SiteID
    
    UNION 
    
    SELECT SurveyData.SiteID, Max(SurveyData.SurveyDate) AS SurveyDate, Last(SurveyData.EstimatedArea) AS Area
    FROM SurveyData
    WHERE (((SurveyData.SiteID) In 
       (SELECT TOP 1 SiteID                            
       FROM SurveyData AS S                              
       WHERE S.SiteID = SurveyData.SiteID)))
    GROUP BY SurveyData.SiteID
    Order By SiteID, SurveyDate;;
    NOTE: I named the table "SurveyData" and I removed spaces in the field names.
    Change the table name to your table names and the field names to your field names (no spaces ).



    Based on the data in the Excel file you posted, these are the results:
    SiteID SurveyDate Area
    18279 5/30/2002 0.0013
    18279 5/28/2016 0.03
    18387 6/3/2002 0.0013
    18387 5/26/2017 0
    18395 6/6/2002 1
    18395 7/29/2017 0.01
    18398 6/7/2002 0.001
    18398 7/6/2018 0
    18404 6/18/2002 0.0013
    18404 5/31/2017 0
    18543 6/5/2002 0.0013
    18543 5/27/2017 0

    Close????

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A UNION query can not be created in the design grid, it MUST be typed/pasted in.

    Create a new query, switch to SQL view and paste in the SQL.
    Save the query. Use the query as a form or report record source. Or open the query.

  6. #6
    Penni is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    3

    Results are correct but where do I put all those lines of code? I've only ever done simple queries.

    Thank you! That worked but I had to remove the last semi colon in the last line.

    Quote Originally Posted by ssanfu View Post
    A UNION query can not be created in the design grid, it MUST be typed/pasted in.

    Create a new query, switch to SQL view and paste in the SQL.
    Save the query. Use the query as a form or report record source. Or open the query.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are correct. There should have been only 1 semi colon at the end...... ( too many copy and pastes)


    Glad you got it to work......

    Good luck with your project.

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

Similar Threads

  1. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  2. Replies: 1
    Last Post: 11-03-2014, 12:31 PM
  3. Replies: 15
    Last Post: 11-08-2012, 10:09 AM
  4. find out values between two dates column
    By learning_graccess in forum Queries
    Replies: 3
    Last Post: 04-20-2012, 04:17 AM
  5. Replies: 5
    Last Post: 02-09-2011, 11:22 AM

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