understanding Relations between tables, See this Please

[shadowmaster]

Good day all. am a beginner in access and here is what am facing.
in the pic attached is 6 tables u will figure it when u see it. the 3 tables in the bottom had data that will be repeated. patient for example can have too many visit and so on. the Issue is what is the right relation between those tables in order to read in save data in main form with out duplications.
also attached sample from the work.
help appreciated

 

[PNGBill]

Didn't download and view you files but the relationship of tables is quite basic.

Some tables hold critical data like TblPatient (patient table), and TblConsult (records of any consultation or activity with a patient), TblScript (Prescription table) TblAppt (Appontment table) TblTeam (All staff)

TblPatient and TblConsult would have a one to many relationship ie one patient would have many consults.
TblTeam and TblConsult would have a one to many - one team record would have many consult records.

When a doctor sees a patient a form would show with the patient's data from TblPatient and a subform with the consults for this patient list by descending order (last visit at the top of the list) and beside this subform another subform of the prescriptions also listed by descending order.

TblPatient and TblConsult would be joined on PatientID.
TblPatient and TblScript also joined on PatientID.
TblConsult and TblScript are joined on PatientID - one to many as one consult could have many scripts. Could be joined ConsultID - but then why would you raise a scritp with no patient? so back to patientID.

The subfoms I mention above would get there data from queries that collected script and consult data as the doctor would want to view it when reviewing a patient.

Trust the above assists

 

[boblarson]

Didn't download and view you files but the relationship of tables is quite basic.

Actually that is not true. It is very NON-NORMALIZED and needs a lot of work.

 

[shadowmaster]

it really shows that i need help so any Ideas?

 

[PNGBill]

I did open the relationship jpg and see what Bob was talking about.

Move your tables around so you get a good look at them and use the whole screen.

Assume PatienInfo is a main database, like a "Customer" table to a sales database or "Inventory" table to a Inventory control database.
Each one of the other tables should have only one line to PatientInfo and possibly one line to another table.

Try and clean this up and consider that any information you require will not come from the tables but from a query that will search the tables.
Suppose you want to know what symptoms and drugs were prescribed to Patient 12678 on 31/12/2009.

A query will search the records of PatientInfo and get whatever data you requested (fields you added to your query) and then search Examination table for the data there (A line joins PatientNo) and also search table SHDSymptoms and so on because all tables are linked by one line only between two tables. One table can be linked to many others but any two only once to each other.

Another fatal problem with your tables is PatientInfo uses PatientNo as it's Primary Key - Good - But... Examination table and all the others must not use this field as there primary key.

Think about the Examination table... This is a record of Examinations and should have a Primary Key called ExaminationID or similar, and the 2nd field could be PatientNo or even the third field as sometimes you may want ExamDate to be 2nd field - not important where PatientNo is but it A can not be Primary key and B must be listed somewhere on Examination table.

Your table SHDSymptoms could be linked to Examination table and not even hold a PatientNo as your query will seach for the patient's data in Examination and this in turn will link to SHDSymptom.
In this example, SHDSymptom will have Primary Key SymptomID and one of it's fields will include either ExaminationID or PatientNo depending on how you join you tables but it won't need both.

Sort this out and then we go to the next steps.

 

[boblarson]

A simplified table structure (not meant to be the end, but only an example) would be:

Patients (table)
PatientID - Autonumber (PK)
PatientHN - Text (for storing the hospital/medical facility's patient ID)
FirstName - Text
LastName - Text
DOB - Date
Address - Text
Address2 - Text
City - Text
State - Text
PostCode - Text


Providers (Table)
ProviderID - Autonumber (PK)
ProviderHN - Text (hospital identifier of the provider)
ProvFName - Text
ProvLName - Text
...etc.

Conditions (Table)
ConditionID - Autonumber (PK)
ConditionDesc - Text

PatientConditions (Table - Junction for many-to-many)
PatientConditionID - Autonumber(PK)
PatientID - Long Integer (FK)
ConditionID - Long Integer (FK)
DateDiagnosed - Date
DiagnosedBy - Long Integer (FK - ProviderID from providers table)

Attibutes (Table - for possible attributes like smoking, Gave up smoking, Mild, Moderate, Severe, etc.
AttributeID - Autonumber(PK)
AttributeDesc - Text

PatientConditionsAttributes (Junction table for storing attributes for that patient's condition)
PatientConditionAttributeID - Autonumber (PK)
PatientConditionID - Long Integer (FK)
AttributeID - Long Integer (FK)


And there can be more - (it is not an easy thing to set up the data structure properly for a medical type of situation, but it is important to do so).

 

[shadowmaster]

thanks bob

thats logic
but the reason why i did the tables like this is the old data. there was an old access application with around 19000 records for a GP private clinic. and the old data ooh (dont let me start). so i cannot make an auto number fields cause we need the old data to have the same numbers.
now if u took a look at the relation photo, for the 3 tables that not having a primary key. what is the right way to do relation with patient info table to get the data right?
it really shows that u r excellent in access cause u exactly know what u r talking about. so thanks for taking time and if u can answer me man it would be awesome.

 

[jdraw]

Here's a data model of a Doctor's Practice than may shed some light on what you are trying to do.

http://www.databaseanswers.org/data_models/doctors_practice/index.htm