0

I currently have a table called ClinicT that contains: ClinicID (primary key), ClinicSite, ClinicDiscipline and ClinicArea. I have other tables in my database that have the ClinicSite, ClinicDiscipline and ClinicArea as well. However, I would like to replace those three fields for a field called ClinicID that would capture all those information. I have tried two methods:

  1. Lookup Wizard: it does show the corresponding Clinic ID with its discipline, site and area but I would need to manually enter the Clinic ID for each record and I have over 100,000 records.
  2. Use a join and union: although the Clinic ID appears when only site and discipline are included in the query, it shows up blank when I introduce clinic area in the query.
SELECT
ClinicT.ClinicID, DNAT.UR, DNAT.ApptDate, DNAT.DNA, DNAT.ApptDuration, DNAT.ClinicSite, DNAT.ClinicDiscipline
FROM DNAT
LEFT JOIN ClinicT
ON (ClinicT.ClinicDiscipline = DNAT.ClinicDiscipline) AND (ClinicT.ClinicSite = DNAT.ClinicSite) AND (ClinicT.ClinicArea = DNAT.ClinicArea);

UNION

SELECT
ClinicT.ClinicID, DNAT.UR, DNAT.ApptDate, DNAT.DNA, DNAT.ApptDuration, DNAT.ClinicSite, DNAT.ClinicDiscipline
FROM ClinicT
RIGHT JOIN DNAT
ON (ClinicT.ClinicDiscipline = DNAT.ClinicDiscipline) AND (ClinicT.ClinicSite = DNAT.ClinicSite) AND (ClinicT.ClinicArea = DNAT.ClinicArea);

For the ClinicArea field, some records are empty (no data entered) because they don't have a specific area they work in (e.g. cardiac rehabilitation).

Thanks,

QuestionsEverywhere

QuestionsEverywhere...
  • 1
  • 1
  • yes you can, with an update sequence, but only if each combination possible of ClinicSite, ClinicDiscipline and Clinic Area is unique, and related to a single ID. If two different ID can have the same clinic, discipline and area, you can't – Foxfire And Burns And Burns Oct 12 '20 at 18:25
  • @Foxfire And Burns And Burns Thank you very much, different IDs can have same clinic, discipline and area so it is unfortunate it cannot be done. Thank you very much for your reply :) – QuestionsEverywhere... Oct 14 '20 at 06:45
  • That's a problem, because I guess each ID is for an unique Clinic. So if two differentes ID can have the same site, Discipline and Area, how do you difference each one of them? IS there any other field that makes the record unique? – Foxfire And Burns And Burns Oct 14 '20 at 07:02
  • @FoxfireAndBurnsAndBurns Yes, so I have other tables: ApptT (contains patient medical record number, date of referral, date of discharge, appointment duration) and WaitT (contains patient medical record number, date of being placed on waitlist, date of being placed off waitlist) in addition to ClinicSite, ClinicDiscipline and ClinicArea. – QuestionsEverywhere... Oct 16 '20 at 01:29

0 Answers0