A query for Report

[Jeff]

Hi everyone,

I have a table that store the patient's personal data like name, address
etc., a table that store 4 tests result of the patient. These 2 tables are
one to one relation, because each patient has only on record of test results.
The test result columns store 1 or 2 instead of pass or fail. Therefore, I
need another table, tbMark, to store 1, 2 as ID and pass, fail as Text. How
to build a query for the Report, so that pass or fail are printed out in
stead of 1 or 2? My code below doesn't work. Thank you.

SELECT tbResult.RResult1, *
FROM (tbPatient INNER JOIN tbResult ON tbPatient.ID = tbResult.MasterID)
INNER JOIN tbMark ON (tbResult.LResult1 = tbMark.ID) AND (tbResult.RResult1 =
tbMark.ID) AND (tbResult.LResult2 = tbMark.ID) AND (tbResult.RResult2 =
tbMark.ID)
WHERE (((tbPatient.ChartNo)=23622));

 

[bhicks11 via AccessMonster.com]

In the field cell of the Query designer:

PassFail: iif(me.rresult1=1, "pass", "fail")

Bonnie
http://www.dataplus-svc.com/document_scanning/document_scanning.htm

 

[John Spencer MVP]

The problem is that you need to 4 instances of tbMark in your query. You
would join each separate instance to one of the RRresult fields in tbResult.

Assumption: tbResult always as a value of 1 or 2 in the RResult fields
(If not, then you will probably need to change the inner joins to LEFT JOINS
to return a row.)

So your query SQL might look something like the following UNTESTED query.

SELECT tbPatient.*, tbResult.*, M1.PassFail as Pass1
, M2.PassFail as Pass2
, M3.PassFail as Pass3
, M4.PassFail as Pass4
FROM ((((tbPatient INNER JOIN tbResult ON tbPatient.ID = tbResult.MasterID)
INNER JOIN tbMark as M1 ON tbResult.RResult1 = M1.ID )
INNER JOIN tbMark as M2 ON tbResult.RResult2 = M2.ID )
INNER JOIN tbMark as M3 ON tbResult.RResult3 = M3.ID )
INNER JOIN tbMark as M4 ON tbResult.RResult4 = M4.ID
WHERE tbPatient.ChartNo=23622

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff]

Hi Bonnie,
It works with your suggestion if I re-write the code as: PassFail:
IIf(rresult1=1, "pass", "fail"). Now I have another problem, i.e. if I leave
rresult1 blank and it still shows “failâ€. My rresult1 column is integer type.
Any way to fix this problem? Thank you.

Jeff


"bhicks11 via AccessMonster.com" 來函：

 

[Jeff]

Hi John,
Thank you for your reply. There is syntax error with JOIN by using your
code, and I am still unable to fix it.

 

[John Spencer]

No idea why that doesn't work. Try setting the query up for ONE field.

If that works then add the tbMark to the query again. And Join on
RResult2 to tblMark.ID.

If that works, then add another copy of the table...

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jeff]

Thank you very much, John.