How to setup this concept in tables

[accessmatt]

Hello,



I'm a brand new Access user and I have a project that I'd like to try in Access. Basically I'm trying to create a database where I can report across these fields. I'll be entering produdct details in for each:

1. Name of food
a. Vitamin components
b. recommended dossage
c. Heals what ailments

What's the best way to organize this information in an Access db that will allow me to create output based on whatever input I choose; for instance foods with X vitamin components and their dossage. Or query for ailments, etc

[rpeare]

tblFoods
FoodID FoodName ----> other food fields

FoodID would be an autonumber

tblVitamins
VitID VitName ----> other vitamin fields

VitID would be an autonumber
VitName would be the name of the vitamin

tblAilments
AilID AilName ---> other ailment fields

AilID would be an autonumber
AilName would be the name of the ailment

tblFoodVitamins
FVID FoodID VitID Dosage

FVID would be an autonumber
FoodID would be the foreign key from tblFood
VitID would be the vitamin found in that food (many vitamins per food)
Dosage would be the amount of that vitamin found in the food.

if the food is supposed to heal an ailment you would have

tblFoodAil
FAID FoodID AilID

FAID would be an autonumber
FoodID would be the foreign key from tblFood
AilID would be the foreign key from the ailments table

If the vitiamin is supposed to heal an ailment you'd have

tblVitAil
VAID FoodID VitID

the same sort of set up.

[accessmatt]

rpeare: Thank you. I very much appreciate your reply.

I'm trying with the build-out that you suggested.

[accessmatt]

rpeare a follow up question:

How should I handle the ingredients field? In fact foods have multiple ingredients? Do I make an Igredient 1, Ingredient 2, etc?

[rpeare]

Yes then you'd have an ingredient table which would be related to the FOOD table through the FOODID though... it leads me to wonder if you're going to have foods that are also ingredients (eggs for instance) you will likely want foods and ingredients on the same table with a simple yes/no checkbox to indicate ingredient vs. final food then you could keep them all on the FOOD table

[accessmatt]

Thanks, good points. I'm interested in looking at the nutritional properties of each substance more than the final product. Ingredient is really the wrong word - probably vitamin or nutrient is better. So In that case I think that I need to have 2 tables as you suggested.