Frugalmeister
New member
- Local time
- Today, 08:18
- Joined
- Oct 17, 2011
- Messages
- 1
Hi everyone,
I'm a newcomer to Access and have been browsing these forums for a while. I've been hesitant to sign up till now but a problem's been plaguing me for the past two months and I could really use some friendly advice.
I want to create a form to make weekly schedules (Mon - Sun). This schedule is for a medical team visiting patients in their homes. On the form, I want all 7 days to be displayed left to right, along with 10 boxes (or slots) under each day, one for each patient. This means that 10 visits can be made per day, 7 days a week, totalling 70 visits a week. To do this I've created 3 tables: tblSchedule (PK: scheduleID), tblPatient (PK: patientID) and tblEmployee (PK: employeeID).
I've tried a few different structures for tblSchedule and this is the one I had settled on, although I feel strongly that this is a very poor way of doing it: each record in tblSchedule corresponds to one weekly schedule. It has the following fields: scheduleID (PK), employeeID (linked to employeeID in tblEmployee), d1Date (date of day 1, Monday), d1V1 (patientID, not linked to tblPatient), d1V2, d1V3, ..., d1V10, d2Date, d2V1, d2V2, d2V3, ..., d7V10. This means that I have 79 fields per record. Due to my limited knowledge, this structure seemed like the best way to go as it was relatively simple to work with and allowed me to display an entire weekly schedule on one form.
My medical team has been using the database to make schedules for a while now and it's working perfectly. The structure is wrong though-I'm sure of it-and it's really been bothering me. The d1V1, d1V2, d1V3, etc fields, which store a patientID (same patientID found in tblPatient) are not linked in any way to tblPatient, which can't be right. I'm also running into a problem now where I want to create a query to display all schedules including a certain patientID in one of the 70 d1V1, d1V2, d1v3, etc fields. While the well-known addage, "If it ain't broke, don't fix it" comes to mind, I strongly feel that I have to do something about this soon.
Could someone please help me redesign this table properly? I really don't know where to start. Please bear in mind that my knowledge of Access is very limited.
Thank you!
Regards,
Frugalmeister
I'm a newcomer to Access and have been browsing these forums for a while. I've been hesitant to sign up till now but a problem's been plaguing me for the past two months and I could really use some friendly advice.
I want to create a form to make weekly schedules (Mon - Sun). This schedule is for a medical team visiting patients in their homes. On the form, I want all 7 days to be displayed left to right, along with 10 boxes (or slots) under each day, one for each patient. This means that 10 visits can be made per day, 7 days a week, totalling 70 visits a week. To do this I've created 3 tables: tblSchedule (PK: scheduleID), tblPatient (PK: patientID) and tblEmployee (PK: employeeID).
I've tried a few different structures for tblSchedule and this is the one I had settled on, although I feel strongly that this is a very poor way of doing it: each record in tblSchedule corresponds to one weekly schedule. It has the following fields: scheduleID (PK), employeeID (linked to employeeID in tblEmployee), d1Date (date of day 1, Monday), d1V1 (patientID, not linked to tblPatient), d1V2, d1V3, ..., d1V10, d2Date, d2V1, d2V2, d2V3, ..., d7V10. This means that I have 79 fields per record. Due to my limited knowledge, this structure seemed like the best way to go as it was relatively simple to work with and allowed me to display an entire weekly schedule on one form.
My medical team has been using the database to make schedules for a while now and it's working perfectly. The structure is wrong though-I'm sure of it-and it's really been bothering me. The d1V1, d1V2, d1V3, etc fields, which store a patientID (same patientID found in tblPatient) are not linked in any way to tblPatient, which can't be right. I'm also running into a problem now where I want to create a query to display all schedules including a certain patientID in one of the 70 d1V1, d1V2, d1v3, etc fields. While the well-known addage, "If it ain't broke, don't fix it" comes to mind, I strongly feel that I have to do something about this soon.
Could someone please help me redesign this table properly? I really don't know where to start. Please bear in mind that my knowledge of Access is very limited.
Thank you!
Regards,
Frugalmeister
