Results 1 to 11 of 11
  1. #1
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26

    Microsoft Access SQL Return Max ID for 3 Key grouping

    I am attempting to use Microsoft Access SQL to return a unique set of data represented in the attached image. The table name can just be [Table1] and all the column headings below represent the columns in the table within my access db. I am using 3 keys (State_Numeric, County_Numeric, and Feature_Name). As you can see there is duplicate data up to the point where the lat/long geospatial data is different. Is there a way to select all columns, and get the max Feature_ID for data that is duplicated?


    Example as below.
    The goal would be to query the data and return Max Feature_ID for the key-grouping. For the example, the row where the Feature_ID is 132818 for the Bethel School (historical) rows of data in Blue.
    I would do this portion of the work in Excel but the file size is around 45 meg and wish to learn how to perform the action in Access. Any help is appreciated.

    Within Access, my SQL code (Which gets distinct Rows) is

    Code:
    SELECT DISTINCT [USGS NationalFile_20210101].STATE_NUMERIC, [USGS  NationalFile_20210101].COUNTY_NUMERIC, [USGS  NationalFile_20210101].FEATURE_NAME FROM [USGS NationalFile_20210101] WHERE ((([USGS NationalFile_20210101].FEATURE_CLASS)="Populated Place"  Or ([USGS NationalFile_20210101].FEATURE_CLASS)="Military" Or ([USGS  NationalFile_20210101].FEATURE_CLASS)="School")) ORDER BY [USGS NationalFile_20210101].STATE_NUMERIC, [USGS  NationalFile_20210101].FEATURE_NAME;

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It sounds like this could be adapted:

    http://www.baldyweb.com/LastValue.htm

    The first query would return your max ID for the 3 field group.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    Thanks. Will try it tomorrow morning. I appreciate your response!
    Wayne

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not sure I understand the requirements, but I typed in these rows
    Click image for larger version. 

Name:	table.png 
Views:	26 
Size:	63.1 KB 
ID:	44396

    modified your query (I aliased your table name )
    Code:
    SELECT DISTINCT Max(USCGNF.FEATURE_ID) AS MaxOfFEATURE_ID, USCGNF.STATE_NUMERIC, USCGNF.COUNTY_NUMERIC, USCGNF.FEATURE_NAME
    FROM [USGS NationalFile_20210101] AS USCGNF
    GROUP BY USCGNF.STATE_NUMERIC, USCGNF.COUNTY_NUMERIC, USCGNF.FEATURE_NAME, USCGNF.FEATURE_CLASS
    HAVING (((USCGNF.FEATURE_CLASS)="Populated Place" Or (USCGNF.FEATURE_CLASS)="Military" Or (USCGNF.FEATURE_CLASS)="School"))
    ORDER BY USCGNF.STATE_NUMERIC, USCGNF.FEATURE_NAME;
    and got these results:
    Click image for larger version. 

Name:	query1.png 
Views:	26 
Size:	30.5 KB 
ID:	44397

    Is this close?

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just a comment for consideration:

    When posting a request for assistance involving data and/or SQL manipulation of data, you will get more focused responses if readers/responders can interact with the data. So, instead of a picture/graphic of the data, please post a file or database with some sample data.
    Steve (ssanfu) typed in some data, but many will ignore the post.

    Also, from Steve's post

    Not sure I understand the requirements
    Agree.

    @Whilburn,
    Did you try PBaldy's suggestion? Result?
    Last edited by orange; 02-25-2021 at 07:12 AM. Reason: spelling

  6. #6
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    Hi Paul, ssanfu and orange,
    Thanks so much.
    To make it simple, I concatenated all the Keys that would be grouped and added the column to my data table. then I took Paul's example and made the following.

    Code:
    SELECT  Max([USGS US States And Cities Final].[FEATURE_ID]) AS  MaxOfFEATURE_ID, [USGS US States And Cities Final].Key_FN_FC_SN_CN,  [USGS US States And Cities Final].Key_FN_SN_CN, [USGS US States And  Cities Final].STATE_NUMERIC, [USGS US States And Cities  Final].COUNTY_NUMERIC, [USGS US States And Cities Final].FEATURE_NAME,  [USGS US States And Cities Final].FEATURE_CLASS, [USGS US States And  Cities Final].STATE_ALPHA, [USGS US States And Cities Final].COUNTY_NAME
    FROM [USGS US States And Cities Final]
    GROUP  BY [USGS US States And Cities Final].Key_FN_FC_SN_CN, [USGS US States  And Cities Final].Key_FN_SN_CN, [USGS US States And Cities  Final].STATE_NUMERIC, [USGS US States And Cities Final].COUNTY_NUMERIC,  [USGS US States And Cities Final].FEATURE_NAME, [USGS US States And  Cities Final].FEATURE_CLASS, [USGS US States And Cities  Final].STATE_ALPHA, [USGS US States And Cities Final].COUNTY_NAME
    ORDER  BY [USGS US States And Cities Final].STATE_NUMERIC, [USGS US States And  Cities Final].COUNTY_NUMERIC, [USGS US States And Cities  Final].FEATURE_NAME;
    It worked great. I am not deleting any rows, and I created a unique set of rows.
    Now I have to crack the nut for using a case select statement to priortize the feature class by (Military, Populated Place, Island, etc)..It looks like ssanfu put the "having" statement may be in the direction. Will try that on my own before asking for help.

    Thanks again for all of your help and certainly ssanfu for the effort put forth (I really like how you put the having statement in there),
    Wayne

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by whilburn View Post
    (I really like how you put the having statement in there)
    Uhhhh, well, I cheated.
    I pasted in your SQL, then changed it to a totals query and the Where clause was automatically converted to "Having" clause.



    Good luck with your project...

  8. #8
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    LoL! Query builder does some great things
    Thanks!
    Wayne

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    For info, WHERE and HAVING both filter the data according to the criteria used.
    However, they work in different ways in an AGGREGATE (totals) query.
    Using WHERE, filtering is done first on the complete record set then it is grouped.
    Using HAVING, grouping is done first and then the grouped query is filtered.
    You can see the difference in the order of the clauses when viewing the query SQL.
    This means for a very large recordset where indexing isn't being applied, HAVING will often be much slower than WHERE.

    Also, if you use the query designer and do not tick the Show checkbox on the filtered field(s), Access will use WHERE.
    If you tick Show, the query uses HAVING.
    This is done automatically as part of the query execution plan.
    Last edited by isladogs; 02-26-2021 at 03:55 PM. Reason: Corrected errors. Thanks Micron for the prompt.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Using HAVING, filtering is done first on the complete record set then it is grouped.
    Using WHERE, grouping is done first and then the grouped query is filtered.
    I think you said that backwards?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oops.
    Thanks for the correction.
    You're right. I did say that backwards and indeed the code order is the opposite of what I said. Bit of a brain fade it seems.
    The reality is still that WHERE is faster than HAVING ... bot only if the filtered fields are using indexing correctly.

    I've now corrected the error in my earlier post.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 4
    Last Post: 02-25-2020, 02:52 AM
  2. Replies: 2
    Last Post: 01-15-2018, 07:46 PM
  3. Importing Information From Microsoft Word to Microsoft Access
    By Runlynch in forum Import/Export Data
    Replies: 9
    Last Post: 01-17-2016, 06:21 PM
  4. Replies: 13
    Last Post: 10-06-2015, 09:12 AM
  5. Replies: 1
    Last Post: 09-13-2011, 01:52 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