Counting occurrences

[djchapple]

I have a table which is basically a calendar containing the columns (i) date, (ii) special info relating to that date (iii) morning volunteers and (iv) afternoon volunteers.

In the "Morning Volunteers" there are up to two names with sometimes a first name, sometimes an initial but always a family name.

The same is true for the "Afternoon Volunteers".

What I want to do is, using the Family name, to find out how many mornings/afternoons each of the volunteers has manned the Visitor Centre.

As I am new to Access I have no idea whether this is possible and, if so, how to do it.

I can do it by exporting the table to Excel but this is very clumsy and time consuming

I am using Vista and Access 2003.

 

[ajetrumpet]

I have a table which is basically a calendar containing the columns (i) date, (ii) special info relating to that date (iii) morning volunteers and (iv) afternoon volunteers.

What I want to do is, using the Family name, to find out how many mornings/afternoons each of the volunteers has manned the Visitor Centre.

Try this in your query...to find the number of days volunteered, both morning and afternoon....

SELECT COUNT([table].[morning volunteers]) AS [Number of days volunteered]
FROM [table]
WHERE (([table].[morning volunteers] LIKE "*" & "family name" & "*") 
AND ([table].[afternoon volunteers] LIKE "*" & "family name" & "*"));

Do you like running Vista?? I have about 40 MB of updates waiting for me to download, but I'm hesitant to do so. Vista by itsself is a memory hog!!