UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Structure, Access 2010    
 
   
jr1956
post Nov 21 2017, 06:07 PM
Post#1



Posts: 27
Joined: 23-April 05



I am trying to work on a table structure to be able to use a form to enter a date, in the future, on a table called tblMenu then in a series of subforms 1)Breakfast, 2) Lunch, and 3) Dinner, enter a list of items served for each meal example Milk, hamburger, hamburger bun, pk catsup, ect. for each meal.

In my example each subform line would have a drop down combo list of items that belong to the MealTime for fast entry uniformity and capture for each date, records of all the items being served for Breakfast, Lunch and Dinner. I currently have a table called tblRecipes that has fields named RecipesID (Autonumber), Description (Text), Quantity (Numeric), and MealTimes (Numeric). I use this table to list all of the meals in each subform using a queries that filter each query/subform by MealTimes both limiting the number of items on the dropdown and to enter the mealtime and item served on the tblMenu table. The purpose of tblMenu is to capture MealDate (Date), MealTime (Numeric), RecipeID (Numeric) for all three meals for each date.

Using three subForms to enter all this data on tblMenu seems like it might work but I am not sure exactly how to do this. Any help is appreciated.
Attached File(s)
Attached File  tblMenu.gif ( 120.82K )Number of downloads: 19
Attached File  tblRecipes.jpg ( 399.66K )Number of downloads: 21
 
Go to the top of the page
 
GroverParkGeorge
post Nov 22 2017, 07:54 AM
Post#2


UA Admin
Posts: 31,216
Joined: 20-June 02
From: Newcastle, WA


It's generally easier to understand the problems by looking at the actual accdb than screenshots of isolated elements. If it's possible to share it, using just enough sample data to see how it works, please do so.

Compact & Repair the accdb, then compress it into a ZIP file. Upload the ZIP file here.

Thank you.

--------------------
Go to the top of the page
 
doctor9
post Nov 22 2017, 10:31 AM
Post#3


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


JR,

> enter a date, in the future, on a table called tblMenu then in a series of subforms 1)Breakfast, 2) Lunch, and 3) Dinner,
> enter a list of items served for each meal example Milk, hamburger, hamburger bun, pk catsup, ect. for each meal.

Okay, so the first thing to do is to break down your data into logical groups. In this description, I'm seeing these groups:

1. Meal types (Breakfast, Lunch, Dinner)
2. Items that CAN be served at a meal (Milk, hamburger, etc.)
3. Meals (a specific date and meal type)
4. Items that were served at a SPECIFIC meal

Here's a basic table structure to get you started:

tblMealTypes
MealTypeID [Autonumber, Primary Key]
strMealTypeName ("Breakfast", "Lunch", "Dinner", "Midnight snack", etc.)

tblItems
ItemID [Autonumber, Primary Key]
strItemName ("Milk", "Hamburger", etc.)

tblMeals
MealID [Autonumber, Primary Key]
dteMealDate
lngMealType [Foreign Key to tblMealTypes.MealTypeID]

tblMealItems
MealItemID [Autonumber, Primary Key]
lngMealID [Foreign Key to tblMeals.MealID]
lngItemID [Foreign Key to tblItems.ItemID]

This last table is a Junction Table that creates the many-to-many relationship between items and meals (one item can be eaten at many meals, and one meal can be made up of many items).

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
jr1956
post Nov 28 2017, 10:54 AM
Post#4



Posts: 27
Joined: 23-April 05



Thank you for this help. I had a similar structure and had to make some changes. I really like how you name field names to indicate what kind of data type the field contains that is really helpful. I am more confident to know I am on the right track. Since I am having another problem with a complex form and multiple subforms I am going to post that question in the forms section of this site. Thanks again.

JR
Go to the top of the page
 
jr1956
post Nov 28 2017, 02:01 PM
Post#5



Posts: 27
Joined: 23-April 05



I tried to edit this message but was unable. Here is what I wanted to post:

Thank you for this help. I had a similar structure but had to make some changes. I really like how you name field names to indicate what kind of data type the field contains that is really helpful. I am more confident to know I am on the right track. I have another question concerning a form with three subforms. In this form I want to record the daily menu for all three meals. My idea is to have a form with three smaller subforms. The main form is based on a table called tblMenu with the following fields: MenuID (primary autonumber), dteMealDate, LngMealTypesID, lngMealItemsID. The three subforms will be a selection of Meal items. 1) Breakfast: this has a query in the combo box from tblItems that is filtered to only include breakfast meal items to select from. It is formatted as a datasheet view to add multiple breakfast items to that days breakfast menu. This data is to be added to the tblMenu. Subform 2) Lunch is structured like Breakfast and Subform 3) Dinner is structured like the other two. One of my problems is how do I structure three subforms to all add menu items for the same date for breakfast, lunch and dinner using three different subforms to populate tblMenu?
Go to the top of the page
 
doctor9
post Nov 28 2017, 03:28 PM
Post#6


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


jr1956,

> The main form is based on a table called tblMenu with the following fields: MenuID (primary autonumber), dteMealDate, LngMealTypesID, lngMealItemsID.
> In this form I want to record the daily menu for all three meals.

What logical group does tblMenu represent? It seems like it is replacing tblMeals from my suggested structure. This table would not have a meal items ID field. Meal items are in a separate group/table from meals themselves.

Don't let the idea of a form with three similar subforms dictate how you think about your table structure. Instead, think of how the data itself is related. The table structure will dictate how your forms are designed, not the other way around. Keep in mind that a list of breakfast items, a list of lunch items and a list of supper items are not grouped by a meal or menu. They are three menus grouped by a single date. At least, as far as I can surmise.

Ask yourself how important it is to see the Breakfast menu while working on the Supper menu. If it's not really important, then you're just making your menu item list 1/3 as large as it could be for no good reason.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
GroverParkGeorge
post Nov 28 2017, 05:23 PM
Post#7


UA Admin
Posts: 31,216
Joined: 20-June 02
From: Newcastle, WA


Here's how I have my meal database set up.

It's going to look somewhat different because the tables are in SQL Azure, but the basics are the way it works best.

Also, I have labelled the TimeofDay table a little differently because it serves other purposes besides meal times.

Attached File  Menu.png ( 23.13K )Number of downloads: 9

--------------------
Go to the top of the page
 
jr1956dwb
post Dec 2 2017, 10:19 AM
Post#8



Posts: 11
Joined: 16-July 13



Firstly I would like to thank everyone who has spent their time to help me. Thank you!

I have attached my database as it may be easier to look to see what I am doing wrong. The database should perform the following functions once completed.


1) Once all of the meal selections for each day are entered for the quarter using the frmMenu, the database should be able to calculate each of the same ingredients used in all meal items for the quarter and produce a report listing a total of the ingredient used for all meals prepared and served in that projected quarter. An example of this would be several dinner meals may use ground beef in differing amounts. Each meal item could be served from 3 to 18 times in a quarter. My plan is to be able to calculate all enter meals for the quarter, then group and total ingredients using a query to report ingredient totals for ordering product (not there yet, need to make my menu part work first)

2) Create a published weekly menu based on a quarter of a year meal plan. I should able to switch out meal items as some items may not be available at the time of meal prep.

Where I am stuck

3) In order to achieve the two above goals it seems like I must make this form work. I have been working for about a week to try to get my Menu form to fill in all of the fields needed to capture the data to develop my menus. I am missing something and I am having trouble understanding how to fill in a particular field:

Specifically tblMeals.lntMealTypes.

I am using three subforms in this form to capture all three daily meals for each date. There has been questions if I really need this form structure. The driving reason is my users are not computer people they are cooks. In order to have them relate and better except this new method of calculating food items for planning menus and ordering the ingredients I need to present it in a fashion they are used to seeing and typing/entering it on the current spreadsheet method. There appears to be plenty of room on the form frmMenu to accommodate the three subforms and keeps it less confusing in their minds to do this entry the way they have always viewed it.

For my frmMenu form I am using the table tblMeals as the table behind the main form but I am only capturing the date not the lngMealType.

I am capturing data entered in all three subforms on tblMealItems. All needed data entered on the subforms seems to work and enters the data in the tblMealItems table.

There may be many more problems with my database that I am not aware of but, the current problem is I cannot figure out how I can enter lngMealType. Like the date of the meal, this field is located on the table behind the main form. I am guessing that the data could be captured using the data in the sub forms combo boxes. Would it be proper to use some kind of code as an after update of the combo boxes to enter the mealtype data into the table behind the main form?
Attached File(s)
Attached File  FoodProcessor.zip ( 460.57K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Dec 2 2017, 11:22 AM
Post#9


UA Admin
Posts: 31,216
Joined: 20-June 02
From: Newcastle, WA


Thanks for sharing the progress on your database.

First thing I note (and I am sure there will be additional comments) is that you are trying to identify "Items" as being for one of four different meal types. This is unnecessary, as your fourth type illustrates clearly: BreakfastOrLunchOrDinner, i.e. "any meal". That fourth category is unnecessary in fact.

Yes, the truth is that ANY food item can--eventually will be--served for any meal. That's true for things like eggs, ketchup, even popcorn and waffles. You need meal type to identify only each MEAL, not items in it. If the meal is "breakfast", by definition every item served at that meal is automatically a "breakfast item".

Now, that does leave you the problem of offering your users an unfiltered list of items, any of which could potentially be included in any given meal. If that is a problem from the business side (i.e. if you have a rule that says "I never want anyone to include Breakfast Pockets in a lunch menu no matter how much they would like to do that for a surprise meal"), then you can retain this as a "preferred meal type", I suppose, but don't rely on it for anything else. And, above all, don't use it to prevent your cooks from using eggs in a dinner omelet, if that suits their fancy.

You have correctly included MealType as a field in tblMeals, but it needs to be related back to tblMealTypes properly. Also, I would rename it, for the sake of consistency:

Second thing observe is that you have gotten around to identifying relationships between tables yet. This is an important step. I refer you back to the Newcomers' Reading List for more on this point about defining relationships.

Third thing. You have put UnitOfMeasurementID in two tables, Items and Item-Ingredients. Why? Will the unit of measure for "Bay leaves" be tablespoons in some places but ounces elsewhere? Or, will you keep one standard UOM for each item and adjust the QUANTITY used in different places to reflect those different recipes. Note, of course, that understanding how relational tables work helps explain why you don't need to redundantly store UOM twice. If you have UOMin tblItems, any time you use that item, it will have that same UOM and you can do math on it reliably because you know it didn't accidentally get changed when added to the item-ingredient table.

More to come, I'm sure.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 2 2017, 11:38 AM
Post#10


UA Admin
Posts: 31,216
Joined: 20-June 02
From: Newcastle, WA


Menus in tblMenu is also a bit overdone, IMO.

A Daily Menu consists of three meals: Breakfast, Lunch and Dinner. Period.

Breakfast is a meal; items served in THAT meal are identified sufficiently in the meal-item table for the meal you include in that day's menu. And any given MealID can be reused over and over in other breakfast menus. If you vary the items, that's a new meal with its own new MealID.

Lunch is also a meal; items served in THAT meal are identified sufficiently in the meal-item table for the meal you include in that day's menu. And any given MealID can be reused over and over in other breakfast menus. If you vary the items, that's a new meal with its own new MealID.

And the same is true for Dinner.


Also, I note here that I am interested in tracking meals after the fact. I.e. I only record each meal as I prepare and eat it (or go to a restaurant), but I am not disciplined enough to plan meals weeks, or even days, in advance. However, if I were to do that, I would follow the same strategy I'm suggesting here. Create meals, with a standard set of items. Then assign those meals to one of five meal times (I include "snacks" and "treats" along with the three standard meals). But that would get boring I'm afraid. "If it's Tuesday, lunch will be carrots and fish sticks... again."

--------------------
Go to the top of the page
 
jr1956dwb
post Dec 3 2017, 05:24 PM
Post#11



Posts: 11
Joined: 16-July 13



GroverParkGeorge thank you for responding;

"First thing I note (and I am sure there will be additional comments) is that you are trying to identify "Items" as being for one of four different meal types. This is unnecessary, as your fourth type illustrates clearly: BreakfastOrLunchOrDinner, i.e. "any meal". That fourth category is unnecessary in fact." Yes that is correct as I created this table solely to limit the meal items in the breakfast, lunch and dinner combo boxes on the frmMenu subforms. In this business model (I have been reading your Data Modeling 101 chapter) we do not have many items that change from dinner, lunch or breakfast (yes we have a very boring menu). There is only a limited amount of items such as milk, bread and fruit ect. that apply to multiple mealtypes so all of those items would be on all of the combo boxes while the other items would remain true to the breakfast, lunch or dinner menus


"Yes, the truth is that ANY food item can--eventually will be--served for any meal. That's true for things like eggs, ketchup, even popcorn and waffles. You need meal type to identify only each MEAL, not items in it. If the meal is "breakfast", by definition every item served at that meal is automatically a "breakfast item". As mentioned I need to limit the combo boxes on the frmMenu subforms. and I am not sure I need the mealtype recorded on any other table as was suggested by Doctor9. I included it because I believe he knows better than I and there must be something (maybe a whole bunch of something) that I am not considering. In addition, the goal of this database is to calculate and convert all of the individual ingredients for a projected 90 days of meals, and multiply that sum by a variable amount of servings to total the ingredient such as 1,000 lbs of carrots or 5 gallons of cider. This produces the detail needed to enter the items in a purchase order. The calculation of all of the individual ingredients for all the meals takes at least a week or more to manually calculate using spreadsheets and other tools (calculator). The hope is, after all the meals are entered, the needed ingredient data will calculate and print to be entered as a purchase of all the ingredients needed to feed a variable sized group of people for the next 90 days. Similar to a school cafeteria.

Also it was mentioned that I have two attributes in different tables using unit of measure (UOM) and while I use only one table to define UOM, there are two distinct measurements used in this database. The first is in the table tblItems-Ingredients the lngUnitOfMeasure represents the amount of ingredients to make say baked beans. In our baked beans recipe, one of the ingredients we use to make baked beans is .0005 pints of cider per serving. In the table tblItems when we serve the cooked item, we serve 4 ounces per serving. This measurement is used for the servers to know what size serving each person should receive for that meal while the other is to make the item. All recipes seem to work whether you serve 100 servings or 5,000 servings.


"Now, that does leave you the problem of offering your users an unfiltered list of items, any of which could potentially be included in any given meal. If that is a problem from the business side (i.e. if you have a rule that says "I never want anyone to include Breakfast Pockets in a lunch menu no matter how much they would like to do that for a surprise meal"), then you can retain this as a "preferred meal type", I suppose, but don't rely on it for anything else. And, above all, don't use it to prevent your cooks from using eggs in a dinner omelet, if that suits their fancy." Our Business Rules, do not permit serving impromptu items or being creative on the menu. Like a school cafeteria, Tuesday is carrots and fish sticks... again. When we do have a new item sent down from the nutritionist, we would enter it via the frmItems. Then select the Item in the frmEnterIngredients form and add the needed ingredient to serve one serving and the menu item will be available forever more. We can then select the future meal item on the projected menu date on the frmMenu form.


"I'm suggesting here. Create meals, with a standard set of items. Then assign those meals to one of five meal times (I include "snacks" and "treats" along with the three standard meals). But that would get boring I'm afraid. "If it's Tuesday, lunch will be carrots and fish sticks... again." Yes this is precisely how it works and a standard set of items is a great idea. You suggest that instead of re-entering the same standardized breakfast meal with the same items, I am already creating a menu set via the MealID. This sounds much more efficient.

Since my users see the menu as a concrete list of breakfast items and are not productive in the abstract, How could I represent the standard say breakfast meal (Fruit, Cracked Wheat Cereal, Scrambled Eggs, Potato Triangle, Nonfat Milk, coffee ect. as a concrete list of items opposed to a concrete list with the customized breakfast meal in a combo box where the user could know that the potato triangle would be replaced with Diced Potatoes? (this only happens if the shipment of potato triangle is late and we have to move already ordered and received menu items (Diced Potatoes) forward or backwards on the meal to manage vendors or other shipping/receiving errors


Thank you again for your time.
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2017, 10:11 AM
Post#12


UA Admin
Posts: 31,216
Joined: 20-June 02
From: Newcastle, WA


Thanks for the detailed explanation.

I think your goal in including MealTypeID in the Food Items is probably an appropriate use of that field, with one variation. Since some items can be used in one or more meals, I'd probably change that from a field in the Food Items table to a junction table in which each food item is listed with one or more appropriate meals. That way you have only the valid meals in the meal table, not that odd "BreakfastLunchDinner" field. The only use of that junction table would be as a way to filter food items for selection in a meal, which makes sense there.

The second paragraph all seems to conform to what I'd expect to see in a properly normalized structure. The calculations you describe should, I think, be possible without additional modifications.

With regard to UOM. I would definitely make a compromise here.

In place of two tables, I would use one. This table would have fields for the UOM to use, i.e. "cup", and the type of application for it: right now I see two, "Recipe" and "Serving". This strategy allows you to add a third type of application, if necessary down the road, i.e. "Order", where you'd enter UOMs like "pallet" or "six-pack" etc. In other words, if you can avoid adding additional tables and/or fields in tables to handle changes in business, you should do that.

I like that last idea as well.

Good luck with the project.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 11:32 PM