Prablem in designing relationship

I have made two tables for my database. One is Patient demographic data which
has fields like
#Patient ID which is an autonumber and the primary key
#Name
#Address
#Home Phone
#OfficePhone
etc. ( please note I am not using the # sign inside the table. This is only
for your reference)

The second table is "Admission Table" which has the following fields
#Admission ID which again is the primary key and an autonumber
#DateOfAdmission
#DateOfDischarge
#Diagnosis1
#Diagnosis2
#Diagnosis3

etc.
Now one patient can have only one record in the demographic table as one
record will be unique per patient but he can get admitted many times. So he
can have multiple records in the admission table. So to link the two tables I
have added
#Admission ID in the demographic table as a number field and not a primary
field.
Now when I join these two fields between these two tables i.e.,
#Admission ID I get a one to many relationship by access which is
One --- "Admission Table" and Many ---- "Demographic table"
I want it the other way round.
Where am i going wrong?

Also should I enforce referential integrity or not ?
Thanks for your time !

Dr Alok Modi MD

You have the right idea, you just went the wrong way. You want to create a
PatientID field within the Admission Table.

I would say that you do want to enforce referential integrity. This will
save you from accidentally failing to enforce it somewhere else.

HTH

Sharkbyte



"Dr Alok Modi MD" wrote:

I have made two tables for my database. One is Patient demographic data which
has fields like
#Patient ID which is an autonumber and the primary key
#Name
#Address
#Home Phone
#OfficePhone
etc. ( please note I am not using the # sign inside the table. This is only
for your reference)

The second table is "Admission Table" which has the following fields
#Admission ID which again is the primary key and an autonumber
#DateOfAdmission
#DateOfDischarge
#Diagnosis1
#Diagnosis2
#Diagnosis3

etc.
Now one patient can have only one record in the demographic table as one
record will be unique per patient but he can get admitted many times. So he
can have multiple records in the admission table. So to link the two tables I
have added
#Admission ID in the demographic table as a number field and not a primary
field.
Now when I join these two fields between these two tables i.e.,
#Admission ID I get a one to many relationship by access which is
One --- "Admission Table" and Many ---- "Demographic table"
I want it the other way round.
Where am i going wrong?

Also should I enforce referential integrity or not ?
Thanks for your time !

Dr Alok Modi MD

I think Sharkbyte let you off a little easy. I'm fairly sure he/she noticed
but didn't say anything about repeating Diagnosis columns. A more normalized
application would have a table:
tblAdmitDiagnosis
===================
AdmitDiagID autnonumber primary key
AdmissionID
Diagnosis

If an admission had 2 diagnosi then this would create 2 records in this
related table.

I have worked with many "canned" medical databases and most of them aren't
too well normalized. I suppose there may be a reason for this.

--
Duane Hookom
MS Access MVP


"Sharkbyte" - wrote in message
...
You have the right idea, you just went the wrong way. You want to create
a
PatientID field within the Admission Table.

I would say that you do want to enforce referential integrity. This will
save you from accidentally failing to enforce it somewhere else.

HTH

Sharkbyte



"Dr Alok Modi MD" wrote:

I have made two tables for my database. One is Patient demographic data
which
has fields like
#Patient ID which is an autonumber and the primary key
#Name
#Address
#Home Phone
#OfficePhone
etc. ( please note I am not using the # sign inside the table. This is
only
for your reference)

The second table is "Admission Table" which has the following fields
#Admission ID which again is the primary key and an autonumber
#DateOfAdmission
#DateOfDischarge
#Diagnosis1
#Diagnosis2
#Diagnosis3

etc.
Now one patient can have only one record in the demographic table as one
record will be unique per patient but he can get admitted many times. So
he
can have multiple records in the admission table. So to link the two
tables I
have added
#Admission ID in the demographic table as a number field and not a
primary
field.
Now when I join these two fields between these two tables i.e.,
#Admission ID I get a one to many relationship by access which is
One --- "Admission Table" and Many ---- "Demographic table"
I want it the other way round.
Where am i going wrong?

Also should I enforce referential integrity or not ?
Thanks for your time !

Dr Alok Modi MD

I have done that , but the problem persists. And my tables are normalised.
The reason there are going to be 5 fields for diagnosis is because a patient
can have 5 diagnoses and I need separate fields to normalise it and to search
when required.
Something is going wrong in my logic here. Can you help me please ?

On Sat, 11 Jun 2005 20:48:01 -0700, Dr Alok Modi MD
wrote:

I have done that , but the problem persists. And my tables are normalised.
The reason there are going to be 5 fields for diagnosis is because a patient
can have 5 diagnoses and I need separate fields to normalise it and to search
when required.
Something is going wrong in my logic here. Can you help me please ?



If a patient can have five diagnoses, they might have six... or seven.

One field per diagnosis is INCORRECT normalization. It violates first
normal form!

You have a perfectly typical Many to Many relationship: each patient
can have zero, one, or more diagnoses; each diagnosis may apply to
zero, one, or more patients.

This structure requires *three* tables (not one!) to correctly model
the relationship:

Patients
PatientID
LastName
FirstName
other biographical data

Diagnoses
DiagCode ' there are standard insurance codes, I'd use them
' or if you're interested in only a few, you could
' use an Autonumber diagnosisID
Diagnosis

PatientDiagnosis
PatientID ' link to Patients
DiagCode ' one of the diseases this patient suffers
any info about this condition in this patient, e.g. a comment
field, severity, date diagnosed, etc.

If a patient has five conditions, there would be five records in
PatientDiagnosis for that patient's PatientID.

A Form (based on Patients) with a Subform (based on PatientDiagnosis,
with a combo box to select the diagnosis) would be convenient for
entering data into this structure.

John W. Vinson[MVP]

I have already done that John. There are in fact not 3 but 7 tables in my
database container. One for "diagnoses", one for " Referring doctors names",
one for " treating Consultant" ,"Patient demographic Tabl;e " which stores
all the vital geographical data for the patient like
FirstName
LastName
Age
Sex which is a Yes/No data type
AddressLine1
AddressLine2
AddressLine3
Addresscity
AddressState
AddressPinCode etc.
Since each patient can have multiple admissions, and the "demographic Table"
records for each patient will be unique for the lifetime per record per
patient, this is linked via a one --- many relationship to "Admission table"
which has
DOA
TOA
DOD
TOD
etc
DOD is date of discharge, TOD is time of discharge etc.
There is a separate table for diagnoses , but 5 fields for
diagnosis1
diagnosis2
diagnosis3
diagnosis4
diagnosis5
each is a combo box bound control type the control source table being this
diagnosis field.

Now I have a new and a different problem which I am going to post in the
appropriate heading viz forms , since it is concerning active X calendar
control.
Thanks for your time,
Dr Alok Modi MD

You might want to read up on normalization. There are links to a number of
good articles from
http://www.ltcomputerdesigns.com/JCR...abaseDesign101.

Having multiple diagnosis fields in a single table is not normalized.

--
Duane Hookom
MS Access MVP


"Dr Alok Modi MD" wrote in message
...
I have already done that John. There are in fact not 3 but 7 tables in my
database container. One for "diagnoses", one for " Referring doctors
names",
one for " treating Consultant" ,"Patient demographic Tabl;e " which stores
all the vital geographical data for the patient like
FirstName
LastName
Age
Sex which is a Yes/No data type
AddressLine1
AddressLine2
AddressLine3
Addresscity
AddressState
AddressPinCode etc.
Since each patient can have multiple admissions, and the "demographic
Table"
records for each patient will be unique for the lifetime per record per
patient, this is linked via a one --- many relationship to "Admission
table"
which has
DOA
TOA
DOD
TOD
etc
DOD is date of discharge, TOD is time of discharge etc.
There is a separate table for diagnoses , but 5 fields for
diagnosis1
diagnosis2
diagnosis3
diagnosis4
diagnosis5
each is a combo box bound control type the control source table being this
diagnosis field.

Now I have a new and a different problem which I am going to post in the
appropriate heading viz forms , since it is concerning active X calendar
control.
Thanks for your time,
Dr Alok Modi MD