Ambiguous outer join vs. multiple duplicative records

[Guest]

I am designing a database to follow patient records in a medical office. The
records are strictly medical data and not to do with billing. I am trying to
create a report and form that will list each encounter of the patient,
incorporating their medication lists. This means that there are both multiple
encounters for each patient and generally multiple medications for each
patient. I don't want the medication list to be one per page, either (i.e.
the whole list on the front page of each encounter report for each patient).

My problem is this, the query will either give me an error message
"ambiguous outer joins" and does not perform the query, or, if I change the
join type, I get multiple duplicates for each record (?Cartesian product).
The code for each example is below. Please help!

AMBIGUOUS JOIN:

SELECT DISTINCT Demographics.FileNumber, "Rec No. " & [Record Number] AS
RecNumber, Demographics.LastName & ", " & Demographics.FirstName AS [Patient
Name], Demographics.DOB,
DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
AS Age, [Vital Statistics].[Record Date], Demographics.[African American],
Demographics.Practitioner, [Vital Statistics].[Weight in lbs], [Vital
Statistics].[Height in inches], [Expr1]/[Expr3] AS BMI, [Vital
Statistics].BUN, [Vital Statistics].Creatinine, Demographics.Gender,
Demographics.[African American] AS Expr7, [Vital Statistics].GFR, [Vital
Statistics].[Systolic BP 1], [Vital Statistics].[Diastolic BP 1], [Vital
Statistics].Pulse1, [Vital Statistics].Respirations, [Vital
Statistics].Temperature, [Weight in lbs]*0.4536 AS Expr1, [Height in
inches]*0.0254 AS Expr2, [Expr2]*[Expr2] AS Expr3, 140-[Age] AS Expr4,
[Expr4]*[Expr1] AS Expr5, 72*[Creatinine] AS Expr6, [Expr5]/[Expr6] AS GFRm,
[GFRm]*0.85 AS GFRf, IIf([Gender]="Male",[GFRm],[GFRf]) AS GFR,
IIf([GFR]>100,"","CKD") AS CKD, IIf([GFR] Between 89 And 99,"Stage 1","") AS
[Stage 1], IIf([GFR] Between 60 And 89,"Stage 2","") AS [Stage 2], IIf([GFR]
Between 30 And 59,"Stage 3","") AS [Stage 3], IIf([GFR] Between 15 And
29,"Stage 4","") AS [Stage 4], IIf([GFR]<15,"Stage 5","") AS [Stage 5],
[Vital Statistics].[Sys BP 2], [Vital Statistics].[Dia BP 2], [Vital
Statistics].Pulse2, [Vital Statistics].[Sys BP 3], [Vital Statistics].[Dia BP
3], [Vital Statistics].Pulse3, [Vital Statistics].[Sys BP 4], [Vital
Statistics].[Dia BP 4], [Vital Statistics].Pulse4, [Med Lists].Medication
FROM (Demographics INNER JOIN [Vital Statistics] ON Demographics.FileNumber
= [Vital Statistics].FileNumber) RIGHT JOIN [Med Lists] ON
Demographics.FileNumber = [Med Lists].FileNumber
ORDER BY Demographics.LastName & ", " & Demographics.FirstName;


MULTIPLE DUPLICATES:

SELECT DISTINCT Demographics.FileNumber, "Rec No. " & [Record Number] AS
RecNumber, Demographics.LastName & ", " & Demographics.FirstName AS [Patient
Name], Demographics.DOB,
DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
AS Age, [Vital Statistics].[Record Date], Demographics.[African American],
Demographics.Practitioner, [Vital Statistics].[Weight in lbs], [Vital
Statistics].[Height in inches], [Expr1]/[Expr3] AS BMI, [Vital
Statistics].BUN, [Vital Statistics].Creatinine, Demographics.Gender,
Demographics.[African American] AS Expr7, [Vital Statistics].GFR, [Vital
Statistics].[Systolic BP 1], [Vital Statistics].[Diastolic BP 1], [Vital
Statistics].Pulse1, [Vital Statistics].Respirations, [Vital
Statistics].Temperature, [Weight in lbs]*0.4536 AS Expr1, [Height in
inches]*0.0254 AS Expr2, [Expr2]*[Expr2] AS Expr3, 140-[Age] AS Expr4,
[Expr4]*[Expr1] AS Expr5, 72*[Creatinine] AS Expr6, [Expr5]/[Expr6] AS GFRm,
[GFRm]*0.85 AS GFRf, IIf([Gender]="Male",[GFRm],[GFRf]) AS GFR,
IIf([GFR]>100,"","CKD") AS CKD, IIf([GFR] Between 89 And 99,"Stage 1","") AS
[Stage 1], IIf([GFR] Between 60 And 89,"Stage 2","") AS [Stage 2], IIf([GFR]
Between 30 And 59,"Stage 3","") AS [Stage 3], IIf([GFR] Between 15 And
29,"Stage 4","") AS [Stage 4], IIf([GFR]<15,"Stage 5","") AS [Stage 5],
[Vital Statistics].[Sys BP 2], [Vital Statistics].[Dia BP 2], [Vital
Statistics].Pulse2, [Vital Statistics].[Sys BP 3], [Vital Statistics].[Dia BP
3], [Vital Statistics].Pulse3, [Vital Statistics].[Sys BP 4], [Vital
Statistics].[Dia BP 4], [Vital Statistics].Pulse4, [Med Lists].Medication
FROM (Demographics INNER JOIN [Vital Statistics] ON Demographics.FileNumber
= [Vital Statistics].FileNumber) INNER JOIN [Med Lists] ON
Demographics.FileNumber = [Med Lists].FileNumber
ORDER BY Demographics.LastName & ", " & Demographics.FirstName;

 

[John Vinson]

I am designing a database to follow patient records in a medical office. The
records are strictly medical data and not to do with billing. I am trying to
create a report and form that will list each encounter of the patient,
incorporating their medication lists. This means that there are both multiple
encounters for each patient and generally multiple medications for each
patient. I don't want the medication list to be one per page, either (i.e.
the whole list on the front page of each encounter report for each patient).

Since you have two "branching" one to many relationships, you must
abandon the idea of "one great master query that does everything".
Instead, use the other tools that Access provides. You should't be
looking at query datasheets for data display in any case (if you are).

Consider instead a Form based on Patients, with two Subforms, one
based on Visits and the other on Medications. If you're printing, use
a Report with two Subreports.

I'm not sure what your reference to "pages" might be; are these pages
on screen, paper pages, or what? There's nothing inherent in Access to
force one record per (screen or paper) page; you can certainly create
Forms and Reports which do so, but you can also create continuous
Forms which display multiple records onscreen.

John W. Vinson[MVP]