Counting Data from Multiple Records

[rab99]

Colleagues,

I am tasked to generate an Access 2007 report providing the number of
times a person has participated in each of several positions at our
events.

The master table (tAllRecs) contains everyone's names and member numbers
(key), among other things that don't matter for this report (addresses,
phone numbers, etc.).

The event table (tEvent) contains the Event Number (key) and a field for
each of the 40 positions we need to fill at each event (fPos01, fPos02,
fPos03,…).

The report needs to list the number of times each member has served in
each position.


Member fPos01 fPos02 fPos03 …

fLast, fFirst (mbr. No) x x x
Doe, Jane (2341) 5 3 10
Smith, John (346) 12 0 5

Any hints or outright solutions? (Yes, I know the report will be rather
wide.)


Rob B.

 

[Allen Browne]

The problem lies with the structure of tEvent. What you have is effectively
a spreadsheet. It is not a relational data structure. Consequently, it's not
easy to query.

Instead of many fields in tEvent, you need a related table with many records
for the people who served in the different positions in each event. The new
table (with a name like tEventPos) would have fields like this:
EventPosID AutoNumber (primary key)
EventID relates to an event in tEvent
MemberNo relates to a person in tAllRecs
PosNum the position number

Sample data:
EventPosID EventID MemberNo PosNum
======== ===== ======== ======
1 1 2341 5
2 1 2341 2
3 1 346 3
4 1 346 4
and so on. So if there were 40 positions filled for event number 1, you
would have 40 *records* in this table. You now delete the fields fPos01,
fPos02, ... fPos40 from tEvent.

You probably want to create a table of positions as well, so no one can
enter a position number that does not exits. this tPosition table might have
fields like this:
PosNum Number a unique number for this postion. primary
key.
PosName Text description of what this is (e.g. marketer
or janitor.)

(I'm not sure what the "5 3 10" represents in your existing table:
perhaps hours? scores? Whatever, you may need an extra field to store that
number as well.)

You can now use a crosstab query to generate the report you wanted:
- MemberNo as Row Heading (group by);
- PosNum as Column Heading (group by);
- EventPosID as Value (count).

More info about building a relational structure:
http://allenbrowne.com/casu-06.html

More info about crosstab queries:
http://allenbrowne.com/ser-67.html

 

[Rob B.]

A tad more of a challenge than I anticipated (at least for me), but will
give it whirl. Thank you.