Design help for basic DB using Access 2007 1

[centraladminman]

Hey everyone, I am trying to create a DB that can track and log my daily nutrition intake. I would like to be able to able to enter in the foods I eat along with the amount of calories, fat(g), protein(g), etc, and then calculate the totals. I know there are programs out there that do this but I want to learn access 2007 so I thought this would be a good project to do that with.

Essentially I understand the fundamentals of Access 2007 but I am definately a beginner. I am having trouble on how to design this. I would like to be able to open a form, select a food type from a drop down list, then have it auto populate the total number of calories, fat, protein, etc in fields to the right of the drop down box ( I am confused on how to do this). In the rows below I would like to select additional food types. I figured out how to use expressions to total each column so I think I got that down.

EXAMPLE of what I am trying to do:

FOOD TYPE (Drop down box) Calories Fat(g) Protien(g)

Egg 60 1 6

1 cup fat free milk 50 1 8


Total 110 2 14

I can not figure how to populate the columns to the right of the food type. Should I be creating a table for each food type? I know you can use expressions to do things, should I be doing something with that. So far I created a single table called food types, and for each record in the table I entered the food name, calories, fat, etc. I used the autonumber field as the primary key. I figured out how to create the drop down box in the form so you can select the food type, just can't the remaining info into the colums to the right... I'm just looking for someone to point me in the right direction on this. Am I way off???

I have been trying to reverse engineer some of the MS demo databases but none of them seem to do anything like this.

Thanks in advance!! Hope this makes sense

 

[dhookom]

First, it is good you have a table of every unique food with the calories etc. I expect there should be some number that identifies the serving size.

Now you need a table of consumption that allows you to store the date, time, primary key from your food table, number of servings, and maybe comments.

Do yourself a favor and give all your tables and fields names that match some sort of naming convention and don't use reserved words.


Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

Eveni f you want to do the whole thing yourself, you could still download the MS nutrition template to look at the table structure and relationships.

http://office.microsoft.com/en-us/t...33.aspx?CategoryID=CT101428511033&ofcresset=1

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[voipguide]

Yes I would also advise u to have a look at the MS nutrition template and see if u are just re-inventing the wheel.