Forum Discussion

Clayblay's avatar
Clayblay
Copper Contributor
Oct 29, 2022

MS Access Table

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 ...
access
Tom_van_Stiphout's avatar
Tom_van_Stiphout
Steel Contributor
Oct 29, 2022

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)