"Bosconian" <bosconianplanetx.com> wrote in message
news:a92dnRRdqaXQeTfeRVn-qAcomcast.com...It's not clear what you're tracking here. I think you need to redesign the> Bottom line, I need a list of statuses with a corresponding count of
> per status driven by a userid, whether the userid exists in table "event"
> "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.
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
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.