Where to start - Totaling data..

[Guest]

I have data - foods with many nutrients in horizontal rows. Another Excel
sheet had the days of the week and times of day. I want to enter all foods
for a day and then total all nutrients for the day and week. With Excel this
involved too many formulas to total the data into columns for the day. How
can I use Access to accomplish this and be presentable?

 

[John Vinson]

I have data - foods with many nutrients in horizontal rows. Another Excel
sheet had the days of the week and times of day. I want to enter all foods
for a day and then total all nutrients for the day and week. With Excel this
involved too many formulas to total the data into columns for the day. How
can I use Access to accomplish this and be presentable?

Well... the first thing to do is to normalize your data. A very good
spreadsheet design can be a very bad relational table design and vice
versa!

I'd consider the following tables:

Foods
FoodID
FoodName
<some description of the food>

Nutrients
NutrientID
NutrientName <e.g. "Energy", "Calcium", "Vitamin B-12">

FoodValue
FoodID <link to Foods>
NutrientID <link to Nutrients>
Unit <e.g. Calories, mg, ug>
Qty < how many mg Calcium per 100 g frex>

Meals
MealID Autonumber Primary Key
MealTime Date/Time

Menu
MealID <link to Meals>
FoodID <link to Foods>
Quantity <g consumed>

You'ld enter multiple *RECORDS* rather than multiple fields in the
FoodValue and Menu tables. A Totals or Crosstab query joining Meals,
Menu, FoodValue and Foods will let you calculate the nutrient per
day, per week, or whatever.

John W. Vinson[MVP]

 

[Joseph Meehan]

I have data - foods with many nutrients in horizontal rows. Another
Excel sheet had the days of the week and times of day. I want to
enter all foods for a day and then total all nutrients for the day
and week. With Excel this involved too many formulas to total the
data into columns for the day. How can I use Access to accomplish
this and be presentable?

Learning access is a little difficult at first, but it sounds like
Access can handle what you want.

You need to think of Access as a relational database. It has various
tables related to each other.

In this case you would have a table with the foods listed. Likely you
would have fields for each nutrient in this table as well with the amounts
per unit of food listed for each food. How may different nutrients do you
have?

You would then have a table for meals? That would include maybe the ID
of the person consuming the meal (their personal facts like name phone etc
would be in another table) Here there are other ways depending on a number
of factors, but here is one possibility. The meal - person table might link
to a consumption table listing the number of units of each food consumed on
a specific date.

Then you could use reports, queries or forms to display and compute the
totals but person, date, meal, food nutrition etc.