how to build a normalized table with the following details in it?

I am building a database of food nutrients and their characteristics (roles,
food sources, benefits, deficiency symptoms, combination with other nutrients
etc.) . Since each food nutrient has more than one roll, one source etc, i
currently have a huge table with the food nutrients as column titles (vitamin
A, B, C etc) and in column A i named lines with characteristics : role 1 ,
role 2, role 3..., food source 1, food source 2, food source 3...etc.
Reading the notes exchange on the group i realize i have not set up the
table correctly for Access since i can't search for data in this table but i
can't think of the correct way of doing it.
Eventually by goal is to be able to run a query for a word (for example
"liver") in the whole database, and to receive everything that has to do with
it (for example- liver is a food source for certain nutrients, but also liver
can be damaged by shortage of a food nutrient etc.
Can you please let me know what is the best way to store my database? and
how to run such queries once the database is set up correctly?
I have been trying to solve this for a very long time without success now,
so i would really appreciate your help. Thanks!

These are what are called Many-to-Many relationships. What is required is a
separate table for roles, food sources, vitamins, deficiencies.

Then you need a single table with a record for each unique combination. The
individual tables will contain an ID and a value like:

tblVitamins
VitaminID - AutoNumber
Vitamin - Text

The junction table will contain the ID values from each related table:

RoleID
VitaminID
FoodSourceID
etc.

You can get a good idea about how to build your tables from Crystal Long's
tutorials:

http://www.accessmvp.com/Strive4Peace/Index.htm

http://www.accessmvp.com/Strive4Peace/Videos/Index.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"??? ???? ???" wrote in message
...
I am building a database of food nutrients and their characteristics
(roles,
food sources, benefits, deficiency symptoms, combination with other
nutrients
etc.) . Since each food nutrient has more than one roll, one source etc, i
currently have a huge table with the food nutrients as column titles
(vitamin
A, B, C etc) and in column A i named lines with characteristics : role 1 ,
role 2, role 3..., food source 1, food source 2, food source 3...etc.
Reading the notes exchange on the group i realize i have not set up the
table correctly for Access since i can't search for data in this table but
i
can't think of the correct way of doing it.
Eventually by goal is to be able to run a query for a word (for example
"liver") in the whole database, and to receive everything that has to do
with
it (for example- liver is a food source for certain nutrients, but also
liver
can be damaged by shortage of a food nutrient etc.
Can you please let me know what is the best way to store my database? and
how to run such queries once the database is set up correctly?
I have been trying to solve this for a very long time without success now,
so i would really appreciate your help. Thanks!

You need to read up on database theory especially the rules of normalization.
There may be something about this in the Access help system.
You definitely dont want repeating items (columns) in a single table, these
need to be separate rows in a separate table.
To provide your final query results, you may have to run multiple queries,
not just one.
As for running the queries, you will need a form to accept parameters for
the queries and a command button to kick off the query process. But first you
need to get your database designed correctly.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"×’×*ן גידל דגן" wrote:

I am building a database of food nutrients and their characteristics (roles,
food sources, benefits, deficiency symptoms, combination with other nutrients
etc.) . Since each food nutrient has more than one roll, one source etc, i
currently have a huge table with the food nutrients as column titles (vitamin
A, B, C etc) and in column A i named lines with characteristics : role 1 ,
role 2, role 3..., food source 1, food source 2, food source 3...etc.
Reading the notes exchange on the group i realize i have not set up the
table correctly for Access since i can't search for data in this table but i
can't think of the correct way of doing it.
Eventually by goal is to be able to run a query for a word (for example
"liver") in the whole database, and to receive everything that has to do with
it (for example- liver is a food source for certain nutrients, but also liver
can be damaged by shortage of a food nutrient etc.
Can you please let me know what is the best way to store my database? and
how to run such queries once the database is set up correctly?
I have been trying to solve this for a very long time without success now,
so i would really appreciate your help. Thanks!

On Tue, 5 Jan 2010 03:15:01 -0800, ??? ???? ???
wrote:

I am building a database of food nutrients and their characteristics (roles,
food sources, benefits, deficiency symptoms, combination with other nutrients
etc.) . Since each food nutrient has more than one roll, one source etc, i
currently have a huge table with the food nutrients as column titles (vitamin
A, B, C etc) and in column A i named lines with characteristics : role 1 ,
role 2, role 3..., food source 1, food source 2, food source 3...etc.
Reading the notes exchange on the group i realize i have not set up the
table correctly for Access since i can't search for data in this table but i
can't think of the correct way of doing it.
Eventually by goal is to be able to run a query for a word (for example
"liver") in the whole database, and to receive everything that has to do with
it (for example- liver is a food source for certain nutrients, but also liver
can be damaged by shortage of a food nutrient etc.
Can you please let me know what is the best way to store my database? and
how to run such queries once the database is set up correctly?
I have been trying to solve this for a very long time without success now,
so i would really appreciate your help. Thanks!

Take a look at some of the resources here; Crystal's tutorial contains a
"Normalization 101" section which would be particularly relevant here.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]

I currently have a huge table with the food nutrients as column titles
(vitamin A, B, C etc)
That is a spreadsheet!
You need a relational set of tables.
Nutrients --
NutrID - Autonumber - primary key
Title - Text
Measure - Text - mg, gram, etc.

NutrientSource --
NutrSourceID - Autonumber - primary key
NutrID - Number - Long Integer - foreign key
Measure - Text
Preparation - Text
QTY - Number -

HealthNeed --
HealthNeedID - Autonumber - primary key
Title - Text
NutrID - Number - Long Integer - foreign key
Measure - Text
QTY - Number -
Ailament -

Create one-to-many relationship for Nutrient to Source and Nutrient to
HealthNeed. Use form/subform with Master/Child links set on primary/foreign
key fields.

--
Build a little, test a little.


"×’×*ן גידל דגן" wrote:

I am building a database of food nutrients and their characteristics (roles,
food sources, benefits, deficiency symptoms, combination with other nutrients
etc.) . Since each food nutrient has more than one roll, one source etc, i
currently have a huge table with the food nutrients as column titles (vitamin
A, B, C etc) and in column A i named lines with characteristics : role 1 ,
role 2, role 3..., food source 1, food source 2, food source 3...etc.
Reading the notes exchange on the group i realize i have not set up the
table correctly for Access since i can't search for data in this table but i
can't think of the correct way of doing it.
Eventually by goal is to be able to run a query for a word (for example
"liver") in the whole database, and to receive everything that has to do with
it (for example- liver is a food source for certain nutrients, but also liver
can be damaged by shortage of a food nutrient etc.
Can you please let me know what is the best way to store my database? and
how to run such queries once the database is set up correctly?
I have been trying to solve this for a very long time without success now,
so i would really appreciate your help. Thanks!