Results 1 to 2 of 2
  1. #1
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63

    Deleting rows when number of value instances exceeded

    I didn't know if I should have posted this in Excel or Access since I'm manipulating an Excel workbook from within Access using VBA but here goes.

    In my worksheet I need to filter for a particular value in one column and then delete rows where another value appears more than five times. For example, let's say I'm filtering one column to show all addresses in the United States only, leaving out any other country. I then need to make sure there are no more than five rows showing each state's name in the state column. So I couldn't have more than five rows showing "CA" or "TX" or any other state in the state column. I can only leave a maximum of five rows for each state in the worksheet.



    I'd sure appreciate any ideas along these lines. Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    attach the xl sheet as an external linked table,
    make a form that shows a listbox of the filtered states,

    cycle thru a listbox (lstSTate) of every state,
    run append qry to add ONLY 5 records to the targe 'report' table.


    Code:
    Dim i As Integer
    dim vState
    dim iCt as long
    Code:
    docmd.setwarnings false
    For i = 0 To lstSTate.ListCount - 1
       vState= lstSTate.ItemData(i)  'get next item in list data
       lstSTate = vState     'set listbox to the state
     
          'see if there are any records,
       iCt = dcount("*","tClients","[State]='" & vSTate & "'")
       if iCt > 0 then    docmd.openquery "qaAdd1State2Target"
    Next
    docmd.setwarnings true
    the append query "qaAdd1State2Target", would only add 5 records top to the target table. Later to export.

    INSERT INTO tReports ( rptNum, rptName )
    SELECT TOP 5 tClients.ClientID, tClients.LastN
    FROM tClients where [state]=forms!myForm!lstSTate



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

Similar Threads

  1. Replies: 1
    Last Post: 09-30-2019, 04:31 AM
  2. Replies: 5
    Last Post: 03-11-2019, 02:35 PM
  3. Replies: 2
    Last Post: 10-12-2016, 05:48 PM
  4. Replies: 5
    Last Post: 10-06-2014, 04:50 PM
  5. Number of Fields Exceeded
    By tsn.s in forum Database Design
    Replies: 2
    Last Post: 12-06-2011, 05:35 AM

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