Results 1 to 7 of 7
  1. #1
    oliver1535 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    3

    need help w/query selecting only the first row in each grouping, deleting the rest

    Hi All,
    I'm new to Access and will soon start a formal training class but in the meantime I have a problem I need to solve. I have also tried finding solutions through other online resources like forums and youtube, a book on Access 2016, etc. but cannot find a solution. I hope someone can help me. I have an access query sorted just the way I want but now I need to select only the first row in each zip code grouping while deleting the rest of the rows within the grouping. In the example provided, I'd only like to have in the final results the yellow highlighted rows. Note that the highlighted rows are the first that appear for each zip code grouping (column Expr1). The final table should only include the highlighted rows. Thank you.
    Attached Thumbnails Attached Thumbnails Capture 1.PNG  

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Query 1 is a totals query grouped by Expr1 with the min of zone.
    Query 2 links query 1 with original source of data, grouped by expr 1, minofzone, first of the rest of the fields. (Not sure what your sort order is)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    Note that the highlighted rows are the first that appear for each zip code grouping (column Expr1)
    how are you defining that? what is the logic that dictates the first row? It can't be based on just sorting expr1 and minofzone because you have a number of expr1 with the same minofzone. Is it perhaps the size of population? or something else?

    Also you delete data from tables, not queries. so does this

    while deleting the rest of the rows within the grouping
    mean you don't want to see the other rows? or you want to delete the data from the underlying data?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    Here is code I use. You will need a field MARK (string 1) to mark duplicates.
    The code will scan the query get the 1st item, then mark the duplicates.
    Then you can either delete the dupes , or only show the unmarked recs.


    Code:
    Public Sub RemoveDuplicates()
    Dim db As Database
    Dim rst   'As Recordset
    Dim qdf As QueryDef
    Dim vCurrDup, vPrevDup, vKey, vCurrFld, vAddr
    
    DoCmd.SetWarnings False
    
    pvQry = "qsSortedList"
    pvDupeFld = "expr1"
    pvChgFld = "mark"
            
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pvQry)
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    vPrevDup = "*&%"
    
    With rst
        While Not .EOF
            vCurrDup = .Fields(pvDupeFld) & ""
            
            If vCurrDup <> "" Then
                    '-----------------------
                    'MARK THE DUPES...
                    '-----------------------
                  If vPrevDup = vCurrDup Then          'mark it if dupe
                        .Edit
                        .Fields(pvChgFld) = "X"
                        .Update
                  End If
            End If
            vPrevDup = vCurrDup
            
           .MoveNext
        Wend
    End With
    
    
    '===== FINSHED =========
        
        'show non marked records
    DoCmd.OpenQuery "qsShowUnmarked"
     
    'OR
      'delete marked records
    sSql = "Delete * from table where [" & pvChgFld & "]='X'"
    'DoCmd.RunSQL sSql
    
    DoCmd.SetWarnings True
    
    Set qdf = Nothing
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
    ErrRemove:
    MsgBox Err.Description, , "RemoveDuplicates():" & Err
    End Sub

  5. #5
    oliver1535 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    3
    Thanks Ajax.

    Basically, this is a project that determines where to open a new warehouse, in addition to my existing warehouses. To help me make a decision, I want to know from which city can I serve the most of my existing customers with 2 day shipping. So to answer your first question, the logic that determined the first row was 1) from each of the warehouse locations (existing and proposed) in the analysis, select the warehouse (move it to the top row for that zip code) for each zip code that can get a shipment to that zip code in two days using the lowest zone # (least cost) 2) in the case of a zone tie, select the warehouse that already exists 3) if the selection is between proposed warehouses only (i.e. not any of my existing warehouses), then select the warehouse that serves the highest population based on transit day 1 (result table from another analysis).

    Duly noted on you delete data from tables, not queries. Based on that, I'd like to create a new table based on the results of the query thus far and based on it. Then, I'd like to create a query that deletes all rows except the first row for each zip code grouping. It's this last step that I'm having trouble finding an answer to. >Oliver

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    Oliver, I can't see all that from what you have said, would need to see the underlying table and the sql to your query. However I see Ranman has proposed a solution which under the circumstances is probably what you need

  7. #7
    oliver1535 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    3
    Understood Ajax. It's been a struggle getting this far, just one last step is the hang-up. Will try Ranman's suggestion. Thanks again.

    Oliver

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

Similar Threads

  1. REST Endpoints
    By foo in forum Programming
    Replies: 1
    Last Post: 12-04-2016, 07:53 PM
  2. Replies: 4
    Last Post: 06-22-2015, 02:15 PM
  3. REST api POST method
    By irade92 in forum Programming
    Replies: 7
    Last Post: 02-03-2015, 03:21 PM
  4. Help with subforms (and the rest)
    By Franco27 in forum Reports
    Replies: 0
    Last Post: 03-14-2011, 09:43 AM
  5. Replies: 11
    Last Post: 12-14-2010, 01:25 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