How do I build an Access database with medical diseases, symptoms, and lab tests?

[nitinrao]

[FONT=&quot]I'm a medical student, and trying to organize diseases, lab tests, physical exam findings, and symptoms into a database.The disease will be related to 2+ "symptoms", 2+ physical exam findings, and 2+ lab tests to diagnose. So when I pull up a disease in the query, I want to see all of the symptoms it relates to, its lab tests, and physical exam findings. And if I pull up a symptom, I could see all the possible diseases it belongs to. If I pull up a lab test, I want to see all the possible diseases it belongs to.
[/FONT]
[FONT=&quot]Finally, I want to do a query for a group of symptoms, and I want to see what diseases matches the query for those set of symptoms.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot] Anyone know how I could do all this in Microsoft Access?[/FONT]

 

[Lightwave]

Yes

First off you create the tables then you design the forms. From your description I believe your table structure should be as follows.

Create 7 tables a rough guess would be as follows (you may wish to add / alter the additional fields in my guess framework)

Tables as follows with possible fields (You will need the PKID fields)
T001Diseases
PKIDDisease
Diseasename
DiseaseSynopsis

T002Symptoms
PKIDSymptom
Symptomname
SymptomSynopsis

T003PhysicalExams
PKIDPhysicalExams
PhysicalExamName
PESynopsis

T004LabTest
PKIDLabTest
LabTestName

Now you will need 3 Junction tables to link everything together because one symptom can relate to many different diseases and one disease can also have many different symptoms. This is called a many to many relationship. The same relationship exists between physical exams and diseases and lab tests and diseases. (I think)

T005JunctionSymptom
PKJSymptom
FKIDDisease
FKIDSymptom

T006JunctionPhysExam
PKIDJPE
FKIDDisease
FKIDPhysExam

T007JunctionLabTest
PKIDJLabTest
FKIDDisease
FKIDLabTest

Now this would be the framework structure if there are many to many relationships between your four things (with everything being related to diseases)

So the top four tables hold unique records on the distinct diseases, lab tests, exams and symptoms while the bottom three tables hold the information on how they relate to each other.

In this way to find out what symptoms a disease is you would go to the Junction Symptom table and pull out all the records which have a disease ID of the disease you are interested in. You will be able to get a list of symptoms for that disease.

On the other hand if you want to know what diseases have a symptom id you do something similar you go to the Junction Symptom table again but this time pull out all the records which have a symptom ID of the symptom you are interested in and you will get a list of diseases.

Hey presto the many to many relationship is created.

Read up on how to make one to many relationships and how implementing two such relationships with a junction table between creates the many to many relationship.