Creating tables to store info on visits to clients at different dates and times

[bkaren1203]

I'm not sure if I'm in the right place to get the answer that I need. I'm trying to figure out what is the best way to keep track of employee work dates and times. I haven't set up any tables yet for this because I just don't know where to start. I'm sure that I'm over thinking this. Here's the information that I need to keep track of.



We have client sites that our employees visit during the week at certain times of day based on the client request. If only one employee is assigned to one client on one day at specific times, that would be easy. However, the client visits sometimes get split between multiple reps on different days and at different times. Here is an example

Scenario 1: Client X is requesting that we send 2 employees on Monday, Wednesday and Friday from 3-5pm.

Scenario 2: Client Y is requesting that we send 2 employess on Monday from 9a-1pm, Wednesdays and Fridays from 10am-5pm.

Secnario 3: Client Z is requesting that we send 2 employees on Monday, Wednesay and Friday from 3-5pm, but one of our employees works Monday, a second rep works Wednesday and a third works Friday.

Secnario 4: Combination of Scenario 2 & 3. Client A is requesting that we send 2 employees on Monday, Wednesday and Friday from 3-5pm. On Monday, one employee is scheduled from 3-4pm and a second from 4-5pm. A third employee is working Wednesday and Friday from 3-5pm.

I just can't figure out what tables need to be set up to store all of this information. The more I think about it, the more I confuse myself.

Thanks in advance for your help.

[ItsMe]

It is important to get the tables correct before building hundreds of forms and queries. You are probably not overthinking it. The problem is, relations are not easy. So, to help you keep track of things, you need to get something down on paper.

You do not have to literally write on paper, your ideas. Sometimes I will start directly with table design in Access. Perhaps you can start with a couple or few tables. Do not include too many fields. Get something in the DB to help you think through the process.

Consider the more simple problem of tracking the relationship between clients and employees. The events table would be the appointments. Store activities like appointments in an events table. So, three tables; tblEmployees, tblClients, tblAppt. Just put the minimum number of fields in each table, like a PK and FirstName field. In the events table you will need some Foreign Key (FK) fields too.

With that, add a couple of records to tblEmployees, tblClients. Then create a query or two to see how you can start adding records to your events table. Don't worry about the multiple appointments in one day for now. Just worry about creating a record in the events table that stores Key values from tblEmployees and tblClients.

After doing that you may have a better picture of how you will keep track of dates and times. It may become apparent that storing a date and time in the events table will help to distinguish the records in the events table.