Count Records problem

[batonman]

I have a simple problem (I'm assuming) that I'm stumped on:

I have two tables:
Table 1 - Contacts
Table 2 - Seasons

Each Contact is placed in a group each season. Each Contact in Table 1
- Contacts is linked to their multiple records of Season/Group data by
field "ContactID" to records in Table 2 - Seasons. Each Contact has
several records correlating to their season/group/section
participation, so a typical look at the Table2-Seasons table will look
like this:


ContactID Season Group Section
Smith 2005 X 1
Smith 2004 X 1
Smith 2003 Y 1
Smith 2002 Z 1
Johnson 2005 X 2
Johnson 2004 Y 2

etc.

How do I count the number of Seasons each Contact participates in group
X, number of seasons for Group Y, etc. in a query? I'd ideally like to
display this information in a PivotTable as such

Group Group etc.

Section Contact - #Seasons Contact - #Seasons etc.

Section Contact - #Seasons Contact - #Seasons etc.



I'd appreciate any help you can offer. I'm sure there's a simple way,
but my research has stumped me.

 

[Guest]

This seems to do what you want. Use a totals query then a crosstab query.

SELECT GroupSection.Group, GroupSection.Section, GroupSection.ContactID,
[ContactID] & " - " & Count([Season]) AS [Contact # Season]
FROM GroupSection
GROUP BY GroupSection.Group, GroupSection.Section, GroupSection.ContactID;

TRANSFORM First(Query49.[Contact # Season]) AS [FirstOfContact # Season]
SELECT Query49.Section
FROM Query49
GROUP BY Query49.Section
PIVOT Query49.Group;

 

[JaxScott]

That worked. Thanks for the education!
SG