duplicate entries

[jerrykeel]

I have designed an ms access databse that is infact a registry for patients.

there are 2 tables
demographics and disease.

the first table has the name, surname, age etc. fields.
the second table contains diagnostics for each patients.
the tables are linked (they both contain an autoID number)


I have designed a form which contains the demographics and in it a subform
with tabs for the diagnostics (a tab for each disease the person presents).

What I need to do is:

when someone enters a name and surname that both (not just the first name)
match an existing patient record (table demographics) i want the rest of the
data on the form(table1 demographics) and subform (table2 disease) to be
autofilled so that the user can check the previous data and decide if this is
indeed a new fact or an old one.

any help will be appreciated.

 

[bhicks11 via AccessMonster.com]

Sounds like a job for DLOOKUP()

Bonnie
http://www.dataplus-svc.com

 

[BruceM]

The design may need closer examination. There should be a Patient table
(your demographics table, I think), a Disease table, and a Patient_Disease
table. The Disease table contains a listing of diseases, as the name
suggests. Since one patient can have several diseases, and one disease may
be contracted by several patients, the Patient_Disease table is needed to
resolve the relationship.

tblPatient
PatientID (primary key, or PK)
FirstName
LastName
DOB
etc.

tblDisease
DiseaseID (PK)
DiseaseName
other fields specific to the disease

tblPatient_Disease
PD_ID (PK)
PatientID
DiseaseID
DiagnosisDate
etc.

Create a one-to-many relationship between the PatientID fields in tblPatient
and tblPatient_Disease. Do the same for DiseaseID.

Note that tblPatient contains DOB (date of birth) rather than age. Age will
change; DOB will not. You can calculate age as needed.

In terms of the interface, a main form based on tblPatient and a subform
based on tblPatient_Disease. The subform includes a combo box that gets its
Row Source from tblDisease. The combo box is bound to DiseaseID in
tblPatient_Disease.

Ordinarily you would use a search combo box to find a patient's record
rather than entering the information over and over. When you select a
patient, the patient's record is brought up. You can edit the record, or
add or edit a related disease record.

I'm not sure how you are going about creating a subform with tabs for each
disease.

I have made a very quick sketch here. This tutorial will help you gain a
better understanding of how Access works:
http://allenbrowne.com/casu-22.html

The tutorial contains, in addition to its clear and thorough descriptions,
links to additional resources.

 

[jerrykeel via AccessMonster.com]

thanx for the reply.
that's what I did after all.
I set the datasource for the subform as tbl patient-disease which contains
all the info.

then while creating the subform I used the access wizard function to show
data from a table. (form wizard).

now if someone fills the 10 fields of the main form and the record already
exists (exact match on all 10 fields) the main first tab of the subform
(disease 1) will fill up so he will know to enter the new disease diagnosis
in the second tab (disease 2) (disease 3) and so on....

I am not sure if this is the best way to go about it but it works fine.

thanx for the input and the resources, it was really helpful!

 

[BruceM]

Filling in ten fields to see if the information is already in the database
is redundant at best. Why not just select the name from a combo box?

 

[jerrykeel via AccessMonster.com]

just the name is not enough.
I need at least 4 fields.

families very commonly suffer from the same disease so the name alone is not
enough.
I used 3 after all. (name, surname, DOB).

 

[BruceM]

You can still select the information from a combo box that shows all of the
necessary information in the drop-down list. If you are going to rely on
manually entering data in several fields you are pretty much guaranteeing
that data entry errors will result in unintended new records for existing
patients.