Error due to duplication of data

[Nona]

Could someone please tell me what I am doing wrong? I am trying to create a
database to track medical appointments: Three tables: Patients (primary key
is Unique ID), Health Care Professionals (primary key is HCPID), and Apts
(primary key is autonumber). The only fields that are indexed are the primary
keys in each of the tables. The relationships are one patient to many apts.
(linked by the patient UniqueID) and one health care professional to many
apts. (linked by the HCPID.)

One query links the patient with the apt date and time. A second query links
the health care professional with the apt date and time. When I try to enter
new data, I get an erro saying it would create duplicates.

Here are the SQLs:

SELECT QryPatients.UniqueID, QryPatients.Name, QryApts.AptDate,
QryApts.AptTime, QryApts.AutoNumber, QryApts.HCPID
FROM QryPatients LEFT JOIN QryApts ON QryPatients.UniqueID = QryApts.UniqueID;

SELECT QrySchedule1.UniqueID, QrySchedule1.Name, QrySchedule1.AptDate,
QrySchedule1.AptTime, QrySchedule1.HCPID, QryHCP.HCPID, QryHCP.HCPName,
QryHCP.HCPAddress
FROM QryHCP LEFT JOIN QrySchedule1 ON QryHCP.HCPID = QrySchedule1.HCPID;

I will appreciate your help!

 

[tina]

your tables/relationships structure looks okay to me, as far as it goes.
exactly how you would set up data entry forms would depend on the work flow
of the task. you might have a main form bound to the patients table, where
you find existing patient records, or add new ones; add subform to that
mainform, bound to the appointments table, with a combobox control in the
subform whose RowSource is set to the health care professionals table. with
the described setup, the work flow would be to open the mainform and find an
existing patient record, or add a new patient record, then move to the
subform to record an appointment and assign a health care professional to
that appointment record.

nowhere in the above setup is a multi-table query used. and if your work
flow is different, requiring a different form(s) setup, you probably still
would not need multi-table queries. so my question is, where are you using
the multi-table queries that you posted?

hth

 

[Nona]

I'd like to create a data entry form for the apts with a list box for the
patients and another list box for the health care professionals and with the
fields to enter the date and time of the apt.

The reason for the multi table query is that Access told me it couldn't
create the relationships between the three tables in one query - that I
should create the relationship in one query, then use that query to create
the second relationship.

I don't have a lot of experience of skill in establishing relationships and
thinking through the consquences of certain steps.

I appreciate your help!

 

[tina]

okay, so your work flow is directed to appointments primarily, assigning a
patient and health care professional to each appointment record you enter.
no problem. create a form for the appointment data entry, and use the
appointments table as the form's RecordSource. do NOT use a multi-table
query, you don't need it. in the form's Design view, add a combobox control,
and set its' ControlSource to the UniqueID field in the appointments table
(that's the foreign key field that links the appointment record back to the
patients table). set the combobox control's RowSource to the patients table.
read up on combobox controls in Access Help, so you'll understand how they
work; come back to the thread with any specific questions. next, add another
combobox control to the form, set its' ControlSource to the HCPID field in
the appointments table (that's the foreign key field that links the
appointment record back to the health care professionals table), and set the
combobox control's RowSource to the health care professionals table.

viola. now you can enter an appointment record, and select the patient and
the health care professional in the appointment record - and you don't need
any multi-table queries at all.

hth

 

[Nona]

You are an angel. Your instructions work perfectly! And much easier than
what I was trying to do. Thank you Very, Very much!

 

[tina]

you're very welcome. i can't accept the wings, though, thanks anyway -
they'd clash with my pitchfork and pointy tail! <g>

 

[kin sabine]

you're very welcome. i can't accept the wings, though, thanks anyway -
they'd clash with my pitchfork and pointy tail! <g>