G
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;
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;