Results 1 to 12 of 12
  1. #1
    aussiegrl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    12

    Counting Duplicate records only once

    I am struggling with a way to create a query that will only count duplicate names once.



    Example:

    I am creating a database to count class registrations. In some instances I have one person taking two or three different classes, but I only want to count them once for reporting purposes.

    Does anyone have a direction to steer me in on how to get this to work?

    Thanks!!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Count students, not classes.
    Tell us about your tables.

    You might want to research Normalization.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    well assuming you have a query that is resulting in the same name listing multiple times; then you want change it to a Distinct Query: to do that, in query design view, with your cursor in the upper half - open the query properties and one row is 'Unique Values' ....change that to Yes.

    every field must be identical to qualify... so you may need to delete fields that differ i.e. class name....

  4. #4
    aussiegrl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    12
    The table I am using with this query is my Registrations Table the fields are: RegistrationID (Auto-#), Activity ID (which has a 1-Many relationship with my Activities Table), FName, LName, Phone, etc....

    I am trying to take a monthly count of how many clients we serve, so if Fred takes Math101 and Math 102 I only want to count him once not twice. (He is listed on my Registration Table every time he registers for a class)

    So he would have two record:

    RegID:1 Activity ID: MATH101 FName: Fred LName: Smith , etc.
    RegID: 2 Activity ID: MATH102 FName: Fred LName: Smith, etc.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think this is an error
    Code:
    (He is listed on my Registration Table every time he registers for a class)
    
    
    Consider 3 tables:
    
    Student ---->RegisteredForClass<------Class
    
    1 John Doe -->       1--78 <---------78  Organic Chemistry 655
                          1--2   <---------2    French Comp
                          1--312<--------312 Advanced Psychology

    1 Student registered for 3 Classes

  6. #6
    aussiegrl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    12
    Referencing back to your normalization link, I think I need to create just a Client Information Table and Activity Listing Table. Then I need to create from these two tables a Registration form that will Auto-Fill my Client/Student Information onto the form based on a Client ID. Then have another bound combo box to be able to select the Activity ID for what they are signing up for. Ahh, back to the drawing board.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In overview (as I see it), you have Many Students and Many Classes.
    1 Student can Register for 1 or Many Classes.
    1 Class can be taken by 1 or Many Students.

  8. #8
    aussiegrl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    12
    Yes that is correct. I am hoping by creating a student table, maybe I can then take your suggestion of counting students and not classes.

  9. #9
    aussiegrl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    12
    Okay re-vamped my tables and still having issues with it counting clients each time they participate in an activity. I only want to track how many clients took classes that month and not have them counted for each activity they took.

    Here are my tables that I am trying to pull data from:

    tblClientInfo
    ClientID
    LastName
    FirstName

    tblRegistrations (I am only including fields I am trying to pull data from as I have many fields in this table)
    RegID (this is just an auto-number field, since I couldn't think what a good Primary Key would be)
    ActivityID (this is the primary key on my tblActivities)
    ClientID (this is the primary key on my tblClientInfo)
    FirstName
    LastName
    Attended (this is a Yes/No field)

    tblActivities (again this table has many fields and I am only listing some of them)
    ActivityID
    StartDate
    Center

    I am pretty sure my problem is that I am using the Count on the Attended field and since that gets checked every time a client shows up for an activity it is counting them. I am not sure if I need to add another field on the ClientInfo table for Active/Inactive though this would cause issues if a client is marked active and then doesn't participate in an activity in let's say March. I am trying to make this user friendly for the co-workers that will be entering clients and getting them registered. The tblClientInfo's purpose was to enter client information and pull from that to use on other forms. I really didn't want them to have to go back in there and try to figure out what clients were active and inactive. I hope I am making sense here. Any help would be greatly appreciated.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your tables should bout a single subject/idea.

    tblClientInfo 'Is just about Clients
    ClientID
    LastName
    FirstName


    tblActivities (again this table has many fields and I am only listing some of them)
    ActivityID
    StartDate
    Center
    'you probably have an ActivityName in here as well

    So far so good..

    Now, tblRegistrations needs some work

    tblRegistrations (I am only including fields I am trying to pull data from as I have many fields in this table)
    RegID (this is just an auto-number field, since I couldn't think what a good Primary Key would be)
    ActivityID (this is the primary key on my tblActivities)
    ClientID (this is the primary key on my tblClientInfo)
    FirstName
    LastName
    Attended (this is a Yes/No field)
    This is what we call a junction table
    As in my code example in post 5. The junction table sits between the 2 parent/related tables and there are two 1 to Many relationships between the parents and the junction table.
    As for a descriptive name you could use ClientIsRegisteredForActivity.

    Because you can join all of your tables to create a query, you do not need to repeat fields in the junction table.

    tblRegistrations
    RegID (this is just an auto-number field, since I couldn't think what a good Primary Key would be)
    ActivityID (this is the primary key on my tblActivities)
    ClientID (this is the primary key on my tblClientInfo)
    FirstName
    LastName

    Attended (this is a Yes/No field)


    You do not need to store FirstName LastName in this table.
    You should create a composite unique index of ClientID and ActivityID to prevent duplicates.

    Good luck.

  11. #11
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    first there must be a query that is Distinct so that it displays only 1 record per name (see my original post)

    then you can make an Aggregate Query or otherwise do a count by name

  12. #12
    aussiegrl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2016
    Posts
    12
    Thank you!! I think I finally got the "Unique Value" query to work. Also Orange I re-did my Registration table not to include the duplicate data. Thanks again for pointing me in the right direction.

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

Similar Threads

  1. Counting records, that have child records?
    By lithium in forum Access
    Replies: 4
    Last Post: 10-28-2015, 09:07 AM
  2. Replies: 11
    Last Post: 03-17-2015, 05:10 PM
  3. Exclude duplicate records when counting in a report
    By Fuzz_cozens in forum Reports
    Replies: 8
    Last Post: 02-14-2014, 02:16 AM
  4. Counting Total Instances of Duplicate Fields
    By sguckemus in forum Queries
    Replies: 1
    Last Post: 04-04-2013, 10:43 PM
  5. Counting records
    By mjsabin in forum Access
    Replies: 1
    Last Post: 03-06-2012, 11:11 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