Counting Data from Multiple Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rab99@cox.net - 25 Jan 2008 01:58 GMT

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.

Reply to this Message

Allen Browne - 25 Jan 2008 02:58 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Colleagues,
>
[quoted text clipped - 23 lines]
>
> Rob B.

Reply to this Message

Rob B. - 29 Jan 2008 03:50 GMT

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

On 24-Jan-2008, "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote:

> The problem lies with the structure of tEvent. What you have is
> effectively
[quoted text clipped - 48 lines]
> More info about crosstab queries:
> http://allenbrowne.com/ser-67.html

> <rab99wrote in message news:fnbfou0rn2@news2.newsguy.com...
> > Colleagues,
[quoted text clipped - 28 lines]
> >
> > Rob B.

Reply to this Message