Where to start - Totaling data..

D

denton

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?
 
J

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?
Click to expand...

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]
 
Top