Newbie: multiple values in single field?

Hi group,

In the database I am developing I have one table listing diseases and
another table listing symptoms. One field in the diseases table links
to the symptoms table. My problem is that a disease may be associated
with more than one of the symptoms listed in the symptoms table. I
cannot figure out how to design the database to make this possible.
But then, I'm a database newbie ...

Can anyone point me in the right direction?
Thanks!
Jan Roelof

.... and a Symptom can be associated with more than one Disease???

You probably need the 2 Tables related by Many-to-Many relationship.

Check Access books (or Relational Database Design books) / Access Help on
the Many-to-Many relationship.

--
HTH
Van T. Dinh
MVP (Access)




wrote in message
...
Hi group,

In the database I am developing I have one table listing diseases and
another table listing symptoms. One field in the diseases table links
to the symptoms table. My problem is that a disease may be associated
with more than one of the symptoms listed in the symptoms table. I
cannot figure out how to design the database to make this possible.
But then, I'm a database newbie ...

Can anyone point me in the right direction?
Thanks!
Jan Roelof

On Wed, 16 Jun 2004 16:15:43 +0200, wrote:

Hi group,

In the database I am developing I have one table listing diseases and
another table listing symptoms. One field in the diseases table links
to the symptoms table. My problem is that a disease may be associated
with more than one of the symptoms listed in the symptoms table. I
cannot figure out how to design the database to make this possible.
But then, I'm a database newbie ...

Can anyone point me in the right direction?
Thanks!
Jan Roelof

Welcome to the world of relational design... g

You will need three tables, not two. Since you have a "many to many"
link between Diseases and Symptoms, you can create a third table
wherein each record has a link to a specific disease and a specific
symptom. Normally this link would be to the Primary Key of the
respective tables. Just for example, this table might contain records
like

Malaria Fever
Diptheria Fever
Malaria Chills
Rhinovirus Chills


with as many records for each disease as it has symptoms, and vice
versa.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thanks Van and John!

I get the idea and can now get on.

Jan Roelof


On Wed, 16 Jun 2004 16:15:43 +0200, wrote:

Hi group,

In the database I am developing I have one table listing diseases and
another table listing symptoms. One field in the diseases table links
to the symptoms table. My problem is that a disease may be associated
with more than one of the symptoms listed in the symptoms table. I
cannot figure out how to design the database to make this possible.
But then, I'm a database newbie ...

Can anyone point me in the right direction?
Thanks!
Jan Roelof