Forms and subform in Access 2014

Folks,
I am using Access 2013 and have run into a problem in regards to forms / sub forms and how they work. Although I have read and looked at various tutorials for some reason I am unable to connect the dots. Please understand, I am not asking anyone to do this. Instead I am looking for me to complete this step-by-step. I am willing to break this into as many questions as needed until done.
First of all, I need to explain my objective. I am wanting to use Access to track my daily protein, carbs and fats based upon a recipe I select. My tables look like this:

tblDietRecord
This table is used to track, by date my beginning and ending weight.
DailyRecordID is an auto number field
DailyDate is a short date field
BeginningWeight is a double number
EndingWeight is a double number

tblMealtime
This table breaks down the mealtimes available, i.e. Breakfast, Morning snack, Lunch, Afternoon snack, Dinner.
MealtimeID is an auto number field
MealTime is a text field that has a record for each of the above mealtimes.

tblPCF
This table contains the number of proteins, carbs, and fats that I'll associate with a recipe.
PCFID is an auto number field
Protein is a number field
Carbs is a number field
Fat is a number field

tblRecipe
This table contains the Recipe and the protein, carbs, and fats associated with the receipt
RecipeID is an auto number
Recipe is a text field for the name of the recipe
PCFID is associated with the tblPCF

Every day I'll track the meals I consumed weighing myself before and at the end of a day. Therefore, a day should show 5 mealtimes.

I believe that I'll need another table (?) - step 1 might be where I need to start.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Good start, but task yourself the question "What did I have for a meal?"

You need to know with a meal what Recipe you had.  That means you want a copy of tblRecipe's primary key in the meal table (this is called a foreign key).

So now you'd have this:

tblMealtime
This table breaks down the mealtimes available, i.e. Breakfast, Morning snack, Lunch, Afternoon snack, Dinner.
MealtimeID is an auto number field
MealTime is a text field that has a record for each of the above mealtimes.
RecipeID - FK field to tblRecipe

 Also tblPCF is not needed.  You can simply put the Protein, Carb, and Fat fields in tblRecipe.

 I would also suggest that for tblMeanTime, you don't have specific records, but rather "I had a meal" and it was a meal type of  "afternoon snack", because what if you have a week moment and have two afternoon snacks?

 What that means though is you'd need tblMealTypes; what do you think the setup will look like (thinking about Recipe's and Meals)?

Jim.
0
Frank FreeseAuthor Commented:
Thanks Jim - I have all the recipes already and each recipe gives me the protein, carbs, and fats. So all I need to do is put in the recipe's name and assign it the protein, carbs, and fats (that the reason for the tblPCF - they hold the various combinations w/o me having to type them in). Let me draft out on paper your suggestions and get back tomorrow. Appreciate your input!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<they hold the various combinations w/o me having to type them in). >>

  But here's the thing; for a given recipe, is there more than one set of protiens, carbs, and fats?  And what do those represent?

  Keep in mind that a table represents some "thing", like a recipe or recipe type.     So are the proteins, carbs, and fats something we know about a recipe , or do they represent something a recipe can be associated with, like ingredients?

Overall, the way you approach a database design is:

1. You first determine what you'd like to get out in terms of information (reports and inquiries)

2. What data will you need to gather to get #1.

3. Start normalization by:

a. Grouping data together that are facts (or attributes) about one "thing"

b. Form a relation (a table) for each of those groups. Ensure that you have enough facts about each relation so that you can identify each uniquely.   In the case of recipes, "receipt name" might be sufficient.    What your doing here is choosing the natural primary key.

c. establish the relationships between each of the things.  For example, recipes can have one or more ingredients, so that will mean another table to represent that relationship.

 I'm sure you've read about the first three normal forms.  The above is following those.  

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hnasrCommented:
" I am not asking anyone to do this. Instead I am looking for me to complete this step-by-step. I am willing to break this into as many questions as needed until done."

That is a good idea.

I can see Jim is going with you in the right direction.
I'll monitor the question, and pop in when I see an author comment.
0
Frank FreeseAuthor Commented:
Jim,
With the additional information you have provided I need to do some re-thinking. A recipe can have multiple serving sizes and each serving size has a fixed number of proteins, carbs and fats. My allowable meal intake of protein, carb and fat may require more than one recipe. I need to make allowances for more than one recipe (serving size) for each meal to get the correct total of PCF. I hope this is clear.
Regarding the tblPCF, I can see your logic there. I will drop that table and when I create a recipe I can manually input the protein, carb and fat for a seving size.
Allow me to now change my tables and establish relationships. I know that my next question will be the form / sub form process.
BTW, I'm not uncomfortable with VBA.
Thanks - - -  let me close this part, re-think and redesign then post the next question.
0
Frank FreeseAuthor Commented:
thanking you kindly
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I hope this is clear.>>

 Yup!

 What I would do is have the protien, carb, and fat per oz/gram for a given recipe.  Then as part of a meal, the recipe used and how much in oz/grams

Jim.
0
Frank FreeseAuthor Commented:
thanks Jim
0
Frank FreeseAuthor Commented:
Jim,
I'm posting another question - this one is my relationship map. Can you look at that for me please? Points to be awarded
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.