I have a little more than basic knowledge of Access. I'm currently helping a meals on wheels program that maintains an Access database for client demographics, create weekly delivery routes, and populate reports of the number of meals needed for the week. The one thing I'm struggling with is how to input data based on a specific day or week. For example, client A receives 3 meals on Tuesdays and 4 meals on Friday every week. However, for next week ONLY (week of the 28th) the client wants to change his delivery to 3 meals Wednesday and 4 on Friday. Then his delivery schedule will default back to 3 on Tuesday 4 on Friday. Right now I'm literally creating a whole new database for each week (by copying from the previous week). I want to do it in one database only. I have no idea how to structure that relationship.
Tables
ClientContact
ClientMealInfo
ClientDeliveryInfo
ClientStatus
Routes
Reports
There's one report for each route that shows the delivery schedule (ClientDeliveryInfo) for each week for all Active clients (ClientStatus) and it includes: the client's information (ClientContact), and # of meals per day (ClientDeliveryInfo).
Desperately looking for some guidance! Thank you!