I debate how to best describe this to you and still be brief. This is data
collected in three hospitals from patients concerning their comments and
complaints. It is primarily collected from surveys returned to the hospital
that identify in which department the customer experienced their services.
Hence, the data is first attributed to a particular hospital and a particular
department. When the data is input to the system, the clerk decides, by the
nature of the comment/complaint, whether it is of concern to other
departments as well, and if so, adds those departments as referrals. (For
example, a complaint may come from a patient on a nursing floor but pertain
to housekeeping as well.) The departments are identified by an ID number
named LocID (location ID). Because each instrument may contain more than one
comment, it is necessary to log patient/survey identifying information in a
separate table from the specific comments. While each instrument is primarily
identified with one department, each comment may have several referrals to
other departments. Additionally, it is possible for a department to have no
attributed primary comments (for example mechanical systems doesn’t seek
patient comments) yet have multiple comments referred from other departments.
The following are the two queries that feed the report:
qryRptDept_L (main report)
SELECT [qryMain-per-ext_L].PatID, [qryMain-per-ext_L].EntDate,
[qryMain-per-ext_L].PatLNm, [qryMain-per-ext_L].PatFN,
[qryMain-per-ext_L].PatMI, [qryMain-per-ext_L].LocID,
[qryMain-per-ext_L].FrmType, [qryMain-per-ext_L].strLocation,
[qryCom-ext_L].ComVerbID, [qryCom-ext_L].ComSec, [qryCom-ext_L].ComVal,
[qryCom-ext_L].qryEmpName_L.Expr1, [qryCom-ext_L].qryEmpName_L_1.Expr1,
[qryCom-ext_L].qryEmpName_L_2.Expr1, [qryCom-ext_L].Comment
FROM [qryMain-per-ext_L] LEFT JOIN [qryCom-ext_L] ON
[qryMain-per-ext_L].PatID = [qryCom-ext_L].PatID
WHERE ((([qryMain-per-ext_L].LocID)=[Dept ID]));
qryRptRefDept_L (sub report)
SELECT qryRefDept_L.LocID, qryRefDept_L.ComVerbID, qryRptAllDept_L.PatID,
qryRptAllDept_L.FrmType, qryRptAllDept_L.strLocation, qryRptAllDept_L.ComSec,
qryRptAllDept_L.ComVal, qryRptAllDept_L.qryEmpName_L.Expr1,
qryRptAllDept_L.qryEmpName_L_1.Expr1, qryRptAllDept_L.qryEmpName_L_2.Expr1,
qryRptAllDept_L.Comment
FROM qryRefDept_L INNER JOIN qryRptAllDept_L ON qryRefDept_L.ComVerbID =
qryRptAllDept_L.ComVerbID
ORDER BY qryRefDept_L.LocID, qryRefDept_L.ComVerbID;
The report keys on the LocID from the first query and works well. The sub
report is linked to the main report by LocID and either prints the data
correctly when data exists in the sub report or returns the sub report blank
if no data exists.
I can sort the data and prepare a second report specifically for cases where
the first query is null and the second is populated, but I am trying to avoid
doing that. Additionally, I am attempting to automate this whole reporting
process, so the fewer steps the better.
The following is an example of one of the reports filled correctly:
Patient Comment Report
Report Period: 8/1/2005 Through 8/31/2005 Emergency Dept. Registration
ID Value / Section Input Form Type / Comment Mentioned Employees Also
Referred To:
4139 negative Emergency Department
A We were not informed of anything unless we
asked.
negative Emergency Department Radiology Department
B My mother was taken for x-rays. After an
extremely long time, I asked where she was.
They said she was going to get CT scan. Again,
I waited for an extremely long time. When my
mother returned to the room, she was
disoriented and upset. Apparently she was left in
the hallway while an emergency pt was being
taken care of (this is understanding, but neither
she nor I were informed of the delay).
negative Emergency Department Medical Staff Office
C The doctor had a cold and she only washed her
hands once when being in the room with us.
negative Emergency Department
E A young child was brought into the room where I
was waiting. TV was put on too loudly and child
fussed constantly.
neutral Emergency Department
Other Yes, keep us both informed.
The following comments have been referred from surveys received in other
departments:
ID Value / Section Input Form Type / Comment Mentioned Employees Referred
From:
3914 neutral Emergency Department Emergency Department
Other If pt is nearby and asks where to go, take
them by arm. Don't give directions.
Courteous at Admission's Emergency Room.
3954 positive Emergency Department Emergency Department
F Registered immediately - no delays.
The sub report begins with “The following comments………..†Sorry the
formatting didn't translate but you can at least see the data.
Thanks again.
