B
Basil
Hiya,
I am seriously desperate for help!! Please don't be scared
by how much I have written, I just wanted to explain it
properly.
What I am doing:
Hospital - patients are referred and then given an
outpatient appointment of which they can have multiple
under the same referral.
They can be referred more than once.
Some people get appointments without a referral.
I have 2 seperate tables - 1 with referrals, 1 with
appointments - no direct link.
Unique identifier exists for patient ("patient key" - same
on both tables), for each referral ("referral key" - only
on referral table).
I want to assign the correct rkey for every appointment,
which can be done as so:
SELECT rphop.[Patient Key], rphop.[Clinic RSNO], rphop.
[Appointment Date], rphop.[Appointment Time], Last
(Referrals.[Referral Key]) AS [Referral Key]
FROM rphop LEFT JOIN Referrals ON rphop.[Patient Key] =
Referrals.[Patient Key]
WHERE (((Referrals.[Date Referral Received])<[rphop]!
[Appointment Date]))
GROUP BY rphop.[Patient Key], rphop.[Clinic RSNO], rphop.
[Appointment Date], rphop.[Appointment Time];
Call this "qryrphop1"
This gives me the patient key, clinic, appointment date
and appointment time - together these make a unique entry.
From this however (due to the where statement) it will
only produce those records that have a referral record.
I thought that if I do another query based on the original
outpatient appointment table and this new query
(qryrphop1), I could do a left join to include those
records that aren't included in qryrphop1 (because no
referral record) as well as the referral key for those
records that do have a referral.
Here is the SQL:
SELECT rphop.*, qryrphop1.[Referral Key]
FROM rphop LEFT JOIN qryrphop1 ON (rphop.[Patient Key] =
qryrphop1.[Patient Key]) AND (rphop.[Appointment Time] =
qryrphop1.[Appointment Time]) AND (rphop.[Appointment
Date] = qryrphop1.[Appointment Date]) AND (rphop.[Clinic
RSNO] = qryrphop1.[Clinic RSNO]);
This is where the problem lies - in the second query
above, for patient keys with appointments from more than
one referral (that are correctly identified in the first
one), it will only allow one referral key to be displayed
for each patient key.
Does anyone know why?? How I can get round this? An entire
hospital is reliant on reports based on this methodology
so any advice would really really be appreciated.
Many thanks, and cheers for taking the time to read all
the way down to here!
Basil
I am seriously desperate for help!! Please don't be scared
by how much I have written, I just wanted to explain it
properly.
What I am doing:
Hospital - patients are referred and then given an
outpatient appointment of which they can have multiple
under the same referral.
They can be referred more than once.
Some people get appointments without a referral.
I have 2 seperate tables - 1 with referrals, 1 with
appointments - no direct link.
Unique identifier exists for patient ("patient key" - same
on both tables), for each referral ("referral key" - only
on referral table).
I want to assign the correct rkey for every appointment,
which can be done as so:
SELECT rphop.[Patient Key], rphop.[Clinic RSNO], rphop.
[Appointment Date], rphop.[Appointment Time], Last
(Referrals.[Referral Key]) AS [Referral Key]
FROM rphop LEFT JOIN Referrals ON rphop.[Patient Key] =
Referrals.[Patient Key]
WHERE (((Referrals.[Date Referral Received])<[rphop]!
[Appointment Date]))
GROUP BY rphop.[Patient Key], rphop.[Clinic RSNO], rphop.
[Appointment Date], rphop.[Appointment Time];
Call this "qryrphop1"
This gives me the patient key, clinic, appointment date
and appointment time - together these make a unique entry.
From this however (due to the where statement) it will
only produce those records that have a referral record.
I thought that if I do another query based on the original
outpatient appointment table and this new query
(qryrphop1), I could do a left join to include those
records that aren't included in qryrphop1 (because no
referral record) as well as the referral key for those
records that do have a referral.
Here is the SQL:
SELECT rphop.*, qryrphop1.[Referral Key]
FROM rphop LEFT JOIN qryrphop1 ON (rphop.[Patient Key] =
qryrphop1.[Patient Key]) AND (rphop.[Appointment Time] =
qryrphop1.[Appointment Time]) AND (rphop.[Appointment
Date] = qryrphop1.[Appointment Date]) AND (rphop.[Clinic
RSNO] = qryrphop1.[Clinic RSNO]);
This is where the problem lies - in the second query
above, for patient keys with appointments from more than
one referral (that are correctly identified in the first
one), it will only allow one referral key to be displayed
for each patient key.
Does anyone know why?? How I can get round this? An entire
hospital is reliant on reports based on this methodology
so any advice would really really be appreciated.
Many thanks, and cheers for taking the time to read all
the way down to here!
Basil