Results 1 to 5 of 5
  1. #1
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68

    Question How to display names one time with the number of times it is listed in the database?

    I have the below excel spreadsheet, looks something like this

    ID LAST_NAME FIRST_NAME
    1 Smith James


    2 Smith James
    3 Jackson Jack
    4 Jackson Todd
    5 Jackson Jack
    6 Jackson Jack
    7 Rover Robert

    I made a vba form where I want to display something like this, each name once with the number of times it is in the table

    Smith James 2
    Jackson Jack 3
    Jackson Todd 1
    Rover Robert 1

    So far below is the code I have, but it only displays one field and I don’t know how to display the number of times in the data without display the names multiple times. Does anyone know?

    Code:
        
        Dim rst As dao.Recordset
        Dim sSQL As String
        
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Reminder")
        While Not rst.EOF
           
            Me.txtLastName = rst![LAST_NAME]
            Me.txtFirstNAme = rst![FIRST_NAME]
            Me.txtNumberP = ""
              
            rst.MoveNext
        Wend
     
        rst.Close
        Set rst = Nothing

  2. #2
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68
    I changed the default view of the form to Continuous Forms and now all the values appear. But how do I make sure that duplicate names do not appear and how do I display the number of duplicates?

  3. #3
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68
    can this be done through vba or is it the best to create a duplicate query through the query wizard under the create tab?

    For now I'm just going to use the query wizard

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    This is easily done with a totals query.

    In the query design view, add the last name and first name fields. Change it to a Totals query, then add the ID field (any field will do, actually), and change "Group By' to "Count" in the row labeled "total".

    When you run the query, you will see each distinct combination of Last Name and First Name, along with the number of times each combination occurs.

  5. #5
    AAAndy is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    68
    That works much better, thanks!

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

Similar Threads

  1. Replies: 1
    Last Post: 06-09-2015, 07:45 AM
  2. Counting the number of times a record appears
    By liamfrancis2013 in forum Access
    Replies: 1
    Last Post: 06-08-2015, 07:04 AM
  3. Replies: 7
    Last Post: 12-12-2014, 11:58 AM
  4. Replies: 5
    Last Post: 05-14-2014, 03:19 PM
  5. Replies: 1
    Last Post: 04-21-2014, 08:00 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