MS Access Table

Occasional Visitor

I am several different types of herbs. I would like to create a database that lists the herb name and the various symptoms that the herb addresses.  I have been away from MS Access so long that I've forgotten how to even google what I want to do. Can anyone help me, please? Thanks, in advance.

1 Reply

@Clayblay I'm assuming you are not several herbs, but would like to save them to a database :)
You are describing a classic one-to-many relation: each herb addresses (ahem) many symptoms.
That would mean:
tblHerbs
HerbID, autonumber, PK
HerbName text(50) required uniqueindex

tblHerbSymptomsAddressed
HerbID long int required FK
Symptom text(255) required

 

However, let's think about it some more. Would you really want to type in the symptom of "menstrual cramps" every time? Without misspellings? Probably not. This leads to a table of symptoms:
tblSymptoms
SymptomID autonumber PK
Symptom text(255) required uniqueindex

The relation between Herb and Symptom is many-to-many (written as M:M): each herb addresses (0 to) many symptoms, and each symptom is addressed by (0 to) many herbs.
To implement the relation, we need a third table:
tblHerbsForSymptoms
HerbID long int PK
SymptomID long int PK
(note the PK is over the combination of both fields)