Count function in a query. How many times a name appears.

[FlyingMonkey]

I have a table with contact information for several people. They have a column which states whether they are a chairperson or a rep.



I have 2 queries set up. One listing the chairs and another listing the reps.

I also have a table for meetings. In each meeting there is a chair and a rep. These are selected from a drop down in a combo box based on the 2 queries.

What I would like is an extra column in chair/rep queries which will tell me how many meetings they've attended.

I can't seem to get it working with the Totals function. Is there another way?

[ranman256]

In the tMeeting table, add the field [attended] as yes/no.
[meeting date], [person], [attended]

Then count the [attended]'s by meetings/person

But IF the only persons listed attended (you wont need the new field so), then Count([Meeting]) , [person]

[FlyingMonkey]

Thanks for your reply. The way it's set up now is there are 2 fields for people, in the meeting table. One field for [Chair] and one for [Rep]. A Chair and Rep will be in attendance for every meeting so the yes/no would not be needed. There will be different person as Chair or Rep depending on the meeting.

Can I add a field in my query (I have 2 queries, 1 for Chair and 1 for Rep) that will count how many times the Chair/Rep has been at a meeting? If I could just Count the times they appear in the list.

It seems like it should be simple but I can't get it to work.

[ranman256]

You may need a UNION query (Q3) called qnTotal
Q1 counts the reps
Q2 counts the chairs
Q3 produces the total


qnTotal=
select [person] from tbl where [rep]=true
union
select [person] from tbl where [chair]=true