Tennis Player Availability 2

[netrusher]

I am a tennis coach at a local college. What I am trying to do using access is:

I want to enter my players names, classes and class time. That way I can find out when a player is available for practice. I do not know how to accomplish this. Classes start at 8am and end at 5pm. They are usually in one hour increments. I would love some help on how to enter the data and then use queries to see who is available on a certain day at a certain time. Thanks for any advice.

 

[dan08]

Create a table with all your players and create a primary key field so they each have a unique number assigned to them. If you don't have a lot of players and are not worried about name repeats you could just use their last names instead of identifying numbers. Then create a table for each day of the week. In these tables have a column for each hour block and a column to hold the players unique numbers (or last name). Then simply enter the name of the students class in the appropriate time block. Though this table design is a bit complex it will make querying the data quite simple.

Example:

SELECT Names FROM Players INNER JOIN Monday ON Players.playerID = Moday.playerID
Where [tenAM] IS NULL

Would give you all kids who are available at 10 am on monday.

 

[lionelhill]

I would do it slightly differently, but this may not be appropriate for how you work.

I'd have a table of players and a table of sessions.
The table of players would contain their name, any other details you need, and an autonumber primary key field PlayerID.
Sessions would contain a PlayerID field linked to the table of players, and would also contain StartAt and FinishAt fields, which are date+time.

To find out all players who are available at any time, you'd just build a query to return player names from the table of players, where there are corresponding entries in the sessions table where (StartAt < search-slot's time) and (EndAt > search-slot's time). This is very easy to set up in the query design thing.

The design means you don't have to worry about the granularity of your sessions (if someone suddenly decides to plan 15min lessons, or start everything 5 minutes later one day, you've got no problem), and it's also easily queried for all sorts of other interesting summaries should you ever wish to do so.