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 forgotten how to even google what I want to do. Can anyone help me, please? Thanks, in advance.

access

1 Reply

  • 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)