I am having a hard time trying to create tables for a database I am trying to create. I am starting in Access but eventually it will be ported to SQL, but the forms will be made in Access. I work for a health care organization and I have been given the task to create a database for the Central Kitchen. They prepare food for other institutions. The only information the other institutions give them is the amount requested from the menu and the Central kitchen prepares the food according to the amount they request and then each institution is in charge of preparing the trays.
Sounds simple enough. Problem is they have a 40 year old system that basically everyday they just cut from a form and post the amounts required.
The menu changes everyday according to a weekly cycle. Each year they create 3 cycles wich last a week (From Wednesday to Tuesday). In each cycle there is a different menu for each day of the week.
They have 11 different forms each one is for modified diets (breakfast, lunch, dinner, and cold food which include juices and deserts) with many specifications and one regular diet.
The food requisition form are dived in the following forms:
They need the total amounts for each item from each Hospital (Institution) they serve.
I created a table titled Food and there I include all the different types of foods with a field titled TypeOfFood and ModifiedDietPreparationMethod for the modified diets.
Food Table
FoodID
TypeOfFood
MD (Boolean; if it is a modified diet or not)
Rate
Weight (weight of each ration)
I am having a hard time creating the order request form, so far I have the following:
OrderRequestHeader
OrderRequestHeaderID
OrderRequestDetailID (Foreign key)
Date
Cycle
Institution
TypeOfDiet (From one of the 11 forms listed earlier)
Diet (Wheter breakfast, lunch or dinner)
OrderRequestDetail
OrderRequestDetailID
OrderRequestHeaderID (Foreign key)
Food
Quantity
MD (Boolean; if it is a modified diet or not)
ModifiedDietPreperationMethod
PreperationMethod (Mostly for the preparation method of meats and farinaceous)
TypeOfFood
Commentary (For any changes)
Menu Table
MenuID
Cycle
Day
Date
Food
Preperation
Diet (Wheter breakfast, lunch or dinner)
MD (Boolean; if it is a modified diet or not)
ModifiedDietPreperationMethod
TypeOfDiet (From one of the 11 forms listed earlier)
I am having a lot of trouble trying to create the menu table. It is just so the Central Kitchen can enter the daily menu for each cycle and each institution can view the menu and select the quantity, hence why I want to transfer the database to SQL, but leave Access for the forms and reports.
Any help anybody can offer will be greatly appreciated.
Sounds simple enough. Problem is they have a 40 year old system that basically everyday they just cut from a form and post the amounts required.
The menu changes everyday according to a weekly cycle. Each year they create 3 cycles wich last a week (From Wednesday to Tuesday). In each cycle there is a different menu for each day of the week.
They have 11 different forms each one is for modified diets (breakfast, lunch, dinner, and cold food which include juices and deserts) with many specifications and one regular diet.
The food requisition form are dived in the following forms:
- Regular diet which includes menu selection for breakfast, lunch and dinner.
- Modified diet: Breakfast (Modifications include nonfat milk with salt, non fat milk, without salt, with water, no salt, etc…)
- Cold diet: Breakfast (Includes: dairy products, juices, and fruits)
- Modified diet: Lunch Farinaceous (containing flour; starchy)
- Modified diet: Lunch Vegetables
- Modified diet: Lunch Meats
- Cold diet: Lunch (Includes: dairy products, juices, and fruits)
- Modified diet: Dinner Farinaceous (containing flour; starchy)
- Modified diet: Dinner Vegetables
- Modified diet: Dinner Meats
- Cold diet: Dinner (Includes: dairy products, juices, and fruits)
They need the total amounts for each item from each Hospital (Institution) they serve.
I created a table titled Food and there I include all the different types of foods with a field titled TypeOfFood and ModifiedDietPreparationMethod for the modified diets.
Food Table
FoodID
TypeOfFood
MD (Boolean; if it is a modified diet or not)
Rate
Weight (weight of each ration)
I am having a hard time creating the order request form, so far I have the following:
OrderRequestHeader
OrderRequestHeaderID
OrderRequestDetailID (Foreign key)
Date
Cycle
Institution
TypeOfDiet (From one of the 11 forms listed earlier)
Diet (Wheter breakfast, lunch or dinner)
OrderRequestDetail
OrderRequestDetailID
OrderRequestHeaderID (Foreign key)
Food
Quantity
MD (Boolean; if it is a modified diet or not)
ModifiedDietPreperationMethod
PreperationMethod (Mostly for the preparation method of meats and farinaceous)
TypeOfFood
Commentary (For any changes)
Menu Table
MenuID
Cycle
Day
Date
Food
Preperation
Diet (Wheter breakfast, lunch or dinner)
MD (Boolean; if it is a modified diet or not)
ModifiedDietPreperationMethod
TypeOfDiet (From one of the 11 forms listed earlier)
I am having a lot of trouble trying to create the menu table. It is just so the Central Kitchen can enter the daily menu for each cycle and each institution can view the menu and select the quantity, hence why I want to transfer the database to SQL, but leave Access for the forms and reports.
Any help anybody can offer will be greatly appreciated.