I need help creating a list of items with a corresponding count.

I have 3 tables:

event
staff
status

I need to list the statuses and count the number of events for each based on
a userid's existence in either "event" or "staff".

For example, suppose the structure and data for each table looks like the
following:

table: event
eventid userid statusid

1 1 2

table: staff
eventid userid roleid

1 2 1
1 2 2

table: status
statusid statusname

1 unscheduled
2 scheduled
3 rescheduled
4 cancelled

For userid 1 (existing in table "event") the output would look like this:

status event count

unscheduled 0
scheduled 1
rescheduled 0
cancelled 0

For userid 2 (existing in table "staff") the output would be the same
because eventid 1's statusid is 2 ("scheduled") even though userid 2 appears
in the staff table twice.

The following query will generate a list of statues and counts for ALL
users:

SELECT s.statusname, Count(e.statusid) AS statuscount
FROM status AS s
LEFT JOIN event e ON e.statusid = s.statusid
GROUP BY s.statusid
ORDER BY s.statusid

Bottom line, I need a list of statuses with a corresponding count of events
per status driven by a userid, whether the userid exists in table "event" or
"staff". The same userid will never appear in both tables. The userid's in
"event" are managers and the userid's in "staff" are worker bees.

Help is greatly appreciated. Thanks!

"Bosconian" <bosconian@planetx.com> wrote in message
news:a92dnRRdqaXQeTfeRVn-qA@comcast.com...
> Bottom line, I need a list of statuses with a corresponding count of
> events
> per status driven by a userid, whether the userid exists in table "event"
> or
> "staff". The same userid will never appear in both tables. The userid's in
> "event" are managers and the userid's in "staff" are worker bees.

It's not clear what you're tracking here. I think you need to redesign the
schema to account for the one-to-many relationships. For example, the Staff
table should have another table e.g. Staff_Role to record the multiple roles
each staff person may have. Then the Staff table would have only one row
per staff person.

Also, I'm not sure about the Event table. I suspect you need to split this
table into multiple tables too. Is it keyed by eventid or userid? Do the
statuses apply to the event (and are therefore redundant on multiple rows)
or to the user? In other words, what is it that's being scheduled?

Finally, I wouldn't recommend designing a schema that put the people in
different tables. That's a sure sign that a restructuring is called for.
I'd do something like this:

- Table Event records the event itself.
- Table Person records all people, event attendees and staff.
- Table Status just holds the four possible statuses for reference.
- Table Attend records which people attend which events, and whether they
attend as a user or as staff.
- The Role table lists possible roles.
- The Staff_Role table references both Role and Person; you may have
multiple roles per staff member, so put multiple rows in the Staff_Role
table.

Then if the status applies to the whole event, you put a reference field in
the Event table to the Status table. If the status applies to each
individual attendee, you put the reference field in the Attend table.
Either way, it becomes much easier to count the number of people per event
and per status, by joining the Event and Attend tables.

Once you have the correct schema to model the relationships, the query to
generate a report is usually much easier to see.

Regards,
Bill K.

"Bill Karwin" <bill@karwin.com> wrote in message
news:dof484012g4@enews4.newsguy.com...
> "Bosconian" <bosconian@planetx.com> wrote in message
> news:a92dnRRdqaXQeTfeRVn-qA@comcast.com...
> > Bottom line, I need a list of statuses with a corresponding count of
> > events
> > per status driven by a userid, whether the userid exists in table
"event"
> > or
> > "staff". The same userid will never appear in both tables. The userid's
in
> > "event" are managers and the userid's in "staff" are worker bees.
>
> It's not clear what you're tracking here. I think you need to redesign
the
> schema to account for the one-to-many relationships. For example, the
Staff
> table should have another table e.g. Staff_Role to record the multiple
roles
> each staff person may have. Then the Staff table would have only one row
> per staff person.
>
> Also, I'm not sure about the Event table. I suspect you need to split
this
> table into multiple tables too. Is it keyed by eventid or userid? Do the
> statuses apply to the event (and are therefore redundant on multiple rows)
> or to the user? In other words, what is it that's being scheduled?
>
> Finally, I wouldn't recommend designing a schema that put the people in
> different tables. That's a sure sign that a restructuring is called for.
> I'd do something like this:
>
> - Table Event records the event itself.
> - Table Person records all people, event attendees and staff.
> - Table Status just holds the four possible statuses for reference.
> - Table Attend records which people attend which events, and whether they
> attend as a user or as staff.
> - The Role table lists possible roles.
> - The Staff_Role table references both Role and Person; you may have
> multiple roles per staff member, so put multiple rows in the Staff_Role
> table.
>
> Then if the status applies to the whole event, you put a reference field
in
> the Event table to the Status table. If the status applies to each
> individual attendee, you put the reference field in the Attend table.
> Either way, it becomes much easier to count the number of people per event
> and per status, by joining the Event and Attend tables.
>
> Once you have the correct schema to model the relationships, the query to
> generate a report is usually much easier to see.
>
> Regards,
> Bill K.
>
>

Hi Bill,

Thanks (again) for your reply.

Please allow me to clarify. The schema in my original post included
references to foreign keys in unspecified tables and was for example only.
The actual project schema closely matches the structure you outlined. I was
attempting (perhaps unsuccessfully) to illustrate my specific query question
without muddling things. I apologize if this was not clear or obvious.

I essentially have a table of events, each with an owner. Each event can
have multiple participants. I simply need to generate a list of statuses and
event counts based on a specific user--whether they be owners or
participants. In my example the userid foreign keys in tables "event" and
"staff" both point to the same table "user".

"Bill Karwin" <bill@karwin.com> wrote in message
news:dof484012g4@enews4.newsguy.com...
> "Bosconian" <bosconian@planetx.com> wrote in message
> news:a92dnRRdqaXQeTfeRVn-qA@comcast.com...
> > Bottom line, I need a list of statuses with a corresponding count of
> > events
> > per status driven by a userid, whether the userid exists in table
"event"
> > or
> > "staff". The same userid will never appear in both tables. The userid's
in
> > "event" are managers and the userid's in "staff" are worker bees.
>
> It's not clear what you're tracking here. I think you need to redesign
the
> schema to account for the one-to-many relationships. For example, the
Staff
> table should have another table e.g. Staff_Role to record the multiple
roles
> each staff person may have. Then the Staff table would have only one row
> per staff person.
>
> Also, I'm not sure about the Event table. I suspect you need to split
this
> table into multiple tables too. Is it keyed by eventid or userid? Do the
> statuses apply to the event (and are therefore redundant on multiple rows)
> or to the user? In other words, what is it that's being scheduled?
>
> Finally, I wouldn't recommend designing a schema that put the people in
> different tables. That's a sure sign that a restructuring is called for.
> I'd do something like this:
>
> - Table Event records the event itself.
> - Table Person records all people, event attendees and staff.
> - Table Status just holds the four possible statuses for reference.
> - Table Attend records which people attend which events, and whether they
> attend as a user or as staff.
> - The Role table lists possible roles.
> - The Staff_Role table references both Role and Person; you may have
> multiple roles per staff member, so put multiple rows in the Staff_Role
> table.
>
> Then if the status applies to the whole event, you put a reference field
in
> the Event table to the Status table. If the status applies to each
> individual attendee, you put the reference field in the Attend table.
> Either way, it becomes much easier to count the number of people per event
> and per status, by joining the Event and Attend tables.
>
> Once you have the correct schema to model the relationships, the query to
> generate a report is usually much easier to see.
>
> Regards,
> Bill K.
>
>

I should add that the event table holds lots of additional information about
an event besides the owner (user) ID. The event_staff table is suppose to
serve as a bridge table between the event and user tables.

"Bill Karwin" <bill@karwin.com> wrote in message
news:dof484012g4@enews4.newsguy.com...
> "Bosconian" <bosconian@planetx.com> wrote in message
> news:a92dnRRdqaXQeTfeRVn-qA@comcast.com...
> > Bottom line, I need a list of statuses with a corresponding count of
> > events
> > per status driven by a userid, whether the userid exists in table
"event"
> > or
> > "staff". The same userid will never appear in both tables. The userid's
in
> > "event" are managers and the userid's in "staff" are worker bees.
>
> It's not clear what you're tracking here. I think you need to redesign
the
> schema to account for the one-to-many relationships. For example, the
Staff
> table should have another table e.g. Staff_Role to record the multiple
roles
> each staff person may have. Then the Staff table would have only one row
> per staff person.
>
> Also, I'm not sure about the Event table. I suspect you need to split
this
> table into multiple tables too. Is it keyed by eventid or userid? Do the
> statuses apply to the event (and are therefore redundant on multiple rows)
> or to the user? In other words, what is it that's being scheduled?
>
> Finally, I wouldn't recommend designing a schema that put the people in
> different tables. That's a sure sign that a restructuring is called for.
> I'd do something like this:
>
> - Table Event records the event itself.
> - Table Person records all people, event attendees and staff.
> - Table Status just holds the four possible statuses for reference.
> - Table Attend records which people attend which events, and whether they
> attend as a user or as staff.
> - The Role table lists possible roles.
> - The Staff_Role table references both Role and Person; you may have
> multiple roles per staff member, so put multiple rows in the Staff_Role
> table.
>
> Then if the status applies to the whole event, you put a reference field
in
> the Event table to the Status table. If the status applies to each
> individual attendee, you put the reference field in the Attend table.
> Either way, it becomes much easier to count the number of people per event
> and per status, by joining the Event and Attend tables.
>
> Once you have the correct schema to model the relationships, the query to
> generate a report is usually much easier to see.
>
> Regards,
> Bill K.
>
>

Bill,

I've been thinking about your suggestions and agree the schema could use
some work. My dilemma is that much of the current schema was predefined and
any changes cannot break this existing structure.

The aforementioned "staff" or "event_staff" table was my attempt to add
support for additional participants associated with an event. The event
table userid (owner) is part of the pre-existing structure.

I'll mull it over and any suggestions in the meantime would be gladly
accepted.

Thanks!

"Bill Karwin" <bill@karwin.com> wrote in message
news:dof484012g4@enews4.newsguy.com...
> "Bosconian" <bosconian@planetx.com> wrote in message
> news:a92dnRRdqaXQeTfeRVn-qA@comcast.com...
> > Bottom line, I need a list of statuses with a corresponding count of
> > events
> > per status driven by a userid, whether the userid exists in table
"event"
> > or
> > "staff". The same userid will never appear in both tables. The userid's
in
> > "event" are managers and the userid's in "staff" are worker bees.
>
> It's not clear what you're tracking here. I think you need to redesign
the
> schema to account for the one-to-many relationships. For example, the
Staff
> table should have another table e.g. Staff_Role to record the multiple
roles
> each staff person may have. Then the Staff table would have only one row
> per staff person.
>
> Also, I'm not sure about the Event table. I suspect you need to split
this
> table into multiple tables too. Is it keyed by eventid or userid? Do the
> statuses apply to the event (and are therefore redundant on multiple rows)
> or to the user? In other words, what is it that's being scheduled?
>
> Finally, I wouldn't recommend designing a schema that put the people in
> different tables. That's a sure sign that a restructuring is called for.
> I'd do something like this:
>
> - Table Event records the event itself.
> - Table Person records all people, event attendees and staff.
> - Table Status just holds the four possible statuses for reference.
> - Table Attend records which people attend which events, and whether they
> attend as a user or as staff.
> - The Role table lists possible roles.
> - The Staff_Role table references both Role and Person; you may have
> multiple roles per staff member, so put multiple rows in the Staff_Role
> table.
>
> Then if the status applies to the whole event, you put a reference field
in
> the Event table to the Status table. If the status applies to each
> individual attendee, you put the reference field in the Attend table.
> Either way, it becomes much easier to count the number of people per event
> and per status, by joining the Event and Attend tables.
>
> Once you have the correct schema to model the relationships, the query to
> generate a report is usually much easier to see.
>
> Regards,
> Bill K.
>
>

I have decided to simply use 2 queries. The userid's in event and
event_staff will never be the same so combining the results in 1 recordset
is unnecessary and makes my life MUCH simplier. My program logic knows
whether the user is a "manager" or "staff" so adding a condition is a
breeze.

Ok, I feel better now. :-)