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

    Use 4 column grouping to prioritze row selected

    I have attached a Db that has been severely cut back. The data file is around a gig of data and it contains Geo spatial data for the U.S. States and outlying islands.


    The table has columns that I use for a level of grouping (State Numeric, Country Numeric, Feature Name). At this level of grouping, I have duplicate data across rows.
    By adding Feature Class to the grouping, I can prioritize by the type of class, which row I want to select.

    There are two queries that have specific examples where I need to choose between two rows of data.

    Although there are 64 potential Feature Classes, I have reduced them to a prioritized list (Military, Populated Place, Civil, Island, and Census) in that order.
    For Example 1 Query, I need to make sure that the Selected Row is for the Populated Place instead of Island.
    For Example 2 query, I need to make sure that the selected row is for the Military instead of Populated Place.


    If I was using VBA, I could use a Select Statement and just set each Case to the priority and then move to the next.
    I am not sure how to do it in SQL. I do know that I will need to allow prioritization to at least 6 prioritization of Feature Classes.

    I realize this is confusing and I hope the database with the written information makes sense.

    Thanks,
    Wayne
    Attached Files Attached Files

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If I understand you correctly, just add an additional filter to each query
    Example1: Feature Class = "populated place". This gives 1 record
    Example2: Feature Class ="Military". There are no records

    EDIT: I hadn't looked at your other parallel thread when I answered so may have missed something of relevance.
    Last edited by isladogs; 02-26-2021 at 04:41 AM.
    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

  3. #3
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    Hi Isladogs,

    I have had two dilemmas with the data. The key I am using is inclusive of 4 columns (State Numeric, County Numeric, Feature Name, and Feature Class) The only difference in the rows were some minor Lat/long variations that made the whole row unique. I resolved that issue in the last thread.
    This thread is about taking (State Numeric, County Numeric, and Feature Name) and then using Feature Class to select which row out of the grouping. If you look at the two Example queries, you can see where I need the row with Military over the Populated Place Feature Class.
    If I was using a Select Case I could just check for Military, then Populated Place, and so on.
    thanks
    Wayne

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Wayne,
    Please have a look at the attached file. It could be done a bit more streamlined using subqueries but I wanted you to see my approach easier.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    whilburn is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    26
    Hi Vlad,
    That is pretty clever. I think I see where you are going. Convert the Feature Class to a discrete value, then do like I did on my first issue... for a particular grouping, get the min priority value.
    Will post back whether or not I am able to move forward with it.
    Thanks.
    Wayne

    Edited---------------
    I selected the max feature ID and min Feature Class (lowest number was highest priority selection) and it worked
    Thanks again Vlad!
    Last edited by whilburn; 02-26-2021 at 02:10 PM. Reason: Edited after testing implemented idea.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome Wayne!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 8
    Last Post: 09-21-2017, 12:53 PM
  2. concatenate text column by grouping?
    By aero in forum Queries
    Replies: 2
    Last Post: 07-27-2016, 03:11 PM
  3. Replies: 5
    Last Post: 04-07-2015, 02:20 PM
  4. Replies: 11
    Last Post: 12-09-2013, 06:33 PM
  5. Replies: 9
    Last Post: 01-28-2011, 06:05 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