Hi,
I am trying to develop personnel-scheduling database for my Anesthesia practice. This can be looked at as a baseball team, where each player is assigned to a different position each day. There are more players on the team and positions, so some players will either be off or on vacation each day. I'd like to be able to go back and tabulate how many times in the year each player has played each position, as well as how many times each player has pitched on the weekend. I'm having trouble conceptualizing the setup and relations for this. I have a table for Players and a table for Positions. Could somebody please give me some guidance on how to proceed from here? Thanks!

Ron

Table: Player_Position

Player_ID (FK)
Postion_ID (FK)
Play_Date Date
Weekend_YN char (1) Y or N


To get "how many times in the year each player has played each position":

select count(*), player_id, position_id
from player_position
where play_date between '1-Jan-2004' and '31-Dec-2004'
group by player_id, position_id

To get "how many times each player has pitched on the weekend":

select count(*), player_id
from player_position
where weekend_yn = 'Y'
and position_id = 'Pitching'
group by player_id