Unanswered: Scheduling Database for Home Health Care
This is a database model/design question. I am a novice when it comes to database design, so bare with me. I have done a lot of research, and plenty of experimenting on my own, but sadly, I have come to the realization that I cannot figure this out on my own.
I am trying to create a database for my home health care company. I schedule employees with clients based on the hours the clients are allowed daily. However, every client does not receive services everyday. A client can have several times throughout a day that an employee needs to be scheduled with them. Clients can be scheduled with many different employees throughout a day. Schedules begin on Sunday at 12am, and end on Saturday at 12am. Schedules are archived by the date of the last day of the schedule being archived. It is important to schedule employees that have been trained with specific clients, but employees that have not been trained with a specific client are always an option. It is also important to know the employees that are prohibited from working with specific clients due to various reasons. Knowing which clients still have hours that need to be scheduled is a must. Finally, there must be a way to record when an employee is sick, or a client must reschedule, for payroll purposes.
Ok so that is the database design part of it, but I figured I should explain how I want to accomplish actually scheduling employees with clients via queries, forms, etc.
*I plan to have a "home" form for navigation to my forms*
Step 1: Add client details (first name, last name, phone#, address, etc.) via a client details form
Step 2: Click next on the client details form, then enter the clients hours that need to be scheduled Sun.-Sat. via the client hours form. Click finish.
Step 3: Add all of my employees details via the employee details form. Click finish.
Step 4: Click begin scheduling
Step 5: Select a week ending date from a combobox or listbox to either resume a schedule or edit an existing schedule, or enter a new week ending to begin scheduling for a new week.
*Here is a Windows form I created as a visual aid to help you understand the scheduling form*
I realize I am asking for a lot of a help. I spent pretty much my entire weekend researching and trying to figure this out. Everything I know about databases/Access is self taught, and I am sure none of you would be impressed :P Which is why I need your help! I'm not planning on making a career out of databases, but this is something I actually really need for my business! I am a do-it-yourself kind of guy (in this case with your help) and I love to learn new things If you need any additional information, please ask. I would be genuinely grateful and in debt for any guidance/help with designing this database!!!