Calculation of Totals for multiple fields

[Guest]

I have a table that books events by date for up to 4 people (fields,date, p1,p2,p3,p4). I would like to construct a query that will determine how many times a person (GROUP BY?) has booked an event by totalling their appearances in any one of the p1,p2,p3,p4 fields. Can this be done in a single query? or should I total four separate queries (one for each of p1,p2 ...). Help on either case would be appreciated

Thanks in advance

Bob Mullen

 

[Mark]

Bob,

It sounds like you want to create a simple cross tab
query. In query design, add your table, from the menu
select Query/Crosstab query. Add your date column and
change the crosstab line to Row Heading, add your column
with the person's name, and change the crosstab line to
Column Heading, add the date column again, and change the
Total row to Count, and the crosstab line to Value.

Have a great day.

Mark

-----Original Message-----
I have a table that books events by date for up to 4

people (fields,date, p1,p2,p3,p4). I would like to
construct a query that will determine how many times a
person (GROUP BY?) has booked an event by totalling their
appearances in any one of the p1,p2,p3,p4 fields. Can this
be done in a single query? or should I total four separate
queries (one for each of p1,p2 ...). Help on either case
would be appreciated.

 

[Mark]

Bob,

I apologize, I misread your question. I thought you were
using a table structure like:

EventDate
Event
Person

Which would be more normalized.

Something like this might work:

qry_Sub_01:
SELECT datefield, p1 AS Person FROM tablename WHERE p1 is
not null
union all
SELECT datefield, p2 FROM tablename WHERE p2 is not null
union all
SELECT datefield, p3 FROM tablename WHERE p3 is not null
union all
SELECT datefield, p4 FROM tablename WHERE p4 is not null

qry_Main
SELECT datefield, Person, count(DateField) as Times
FROM qry_Sub_01
GROUP BY datefield, Person

I think that would work, but union queries on big tables
can be slow.

Mark

-----Original Message-----
Mark:

Thanks for your help. What I would like to do is somehow

count the number of times each member appears in any one
of the four fields (p1,p2,p3,p4). The crosstab suggestion
works well but only for one of the p1 ... p4 fields as the
crosstab only seems to allow a single column heading.
Maybe I'm missing something.

 

[Guest]

Worked perfectly Mark! Thanks a million

Could I take it a step further? Now that I have query results showing the date of each booking for members, could I total the number of bookings for each member and produce a result that is simply Person Name, 10 (Total bookings of all dates)

Thanks again for your continued assistance

Bob Mullen