How to count the 1st instance of X in a recordset

[Alexb128]

Hi all,

I'm fairly new to Access, so bear with me.

I'm trying to create a query that will total the number of trainees (each identified by a unique StaffID #) and list each trainer (each identified by a unique ProviderID #) associated with training classes held during a range of dates. Each class is associated with a unique Date ID# and trainers and trainees are linked to a class by this same DateID #. The problem with this query is that because the DateID number is used as the variable which connects everything in this database, when I run a query that shows all of the trainees and trainers within a range of dates, I get the following result when there are multiple trainers associated with the same class:



Date ID# Class Staff ID# Provider ID#
1 A 1 1
1 A 2 1
1 A 3 1
1 A 1 2
1 A 2 2
1 A 3 2

Total Attendees: 6 (instead of 3)

What I want Access to do is select all of the classes that fall within a particular date range and for each Date ID#, count the first Staff ID# and first Trainer ID#. However, I have no idea how to write this query, though I suspect it involves querying the query that generates the results shown above.

Thanks so much for any help you can offer!
-Alex

[June7]

The sample data show the same staff (1, 2, 3) attending the same class (A) on the same date (1) with two different trainers. This class had two trainers partnered?

So what is the attendance count you want returned - 3? Don't include the trainers in the query. Do a query that groups by date and class and counts attendees then do another query that joins that aggregate query with trainers.

[Alexb128]

Hi June7,

Thanks for your reply and suggestion. I'll give it a try. Also, yes, I want Access to count 3 attendees, not 6.

One quick follow-up question: Will I get some kind of error if I run both queries when I run the report? I ask b/c I wasn't sure if Access knew the correct order in which to run the queries.

Thanks again!
Alex

The sample data show the same staff (1, 2, 3) attending the same class (A) on the same date (1) with two different trainers. This class had two trainers partnered?

So what is the attendance count you want returned - 3? Don't include the trainers in the query. Do a query that groups by date and class and counts attendees then do another query that joins that aggregate query with trainers.

[June7]

The report will be bound to the last query. Should not error.

[Alexb128]

Hi June7,
I revised the query that connects students to classes, so now the report that shows the number of students in each class for a given range of dates calculates the total number of attendees correctly. Also, I created a separate query that shows which trainers have taught each class. However, because both tables that connect students to classes and trainers to classes are in many-to-many relationships with a common table and use the same DateID # to establish these connections, I run into the same problems that I had originally. That is, can you explain once again how I'm supposed to connect these queries so that 1 can be used as the record source for a report? When I create a query that joins the students-to-classes query and the trainers-to-classes query, Access uses the Date ID# to associate each student with a trainer and because Access counts each Student ID # to determine the number of attendees per class, if there are 10 students in a class that has 2 trainers, Access thinks that there are 20 students in the class instead of 10 students in a class with 2 trainers.
Thanks,
Alex

[June7]

You joined the attendance count (aggregate) query to the trainers/classes query?

Want to provide db for analysis? Follow instructions at bottom of my post.