G
Guest
I am trying to build a database which will be used for compliance auditing
purposes. Our hospital is tracking patient transfers. I have 18 questions for
which the answers can only be Yes, No, Not Applicable(NA), or Not
Documented(ND). Questions such as: Was a signature obtained?; Was a checklist
completed?; Was the receiving hospital notified?... and so on. I need a form
which will allow the person entering data to see all of the questions on one
screen with a combo box next to each containing the answers (Yes, No, NA, ND)
as well as a medical record number, the date of the transfer, the
transferring physicians name, attending physicians name, and the nurses name.
Right now I have
A “Physicians†table which will be used for a combo box to lookup their name
on the form.
Fields: physicianID(key), MDLastName, MDFirstName
A “Nurses†table which will be used for a combo box to lookup their name on
the form
Fields: nurseID(key), RNLastName, RNFirstName
A “Medical Record†table which stores only a medical record number and
nothing else, which is necessary because each patient could have several
transfers and I want to create a relationship with the “Transfers†table.
Field: MedRecNo(key)
A “Transfers†table to hold info about each transfer
Fields: transferID(key), Date, physicianID, transferringMDID, nurseID,
MedicalRecordNumber, and a field for each question with a value list combo
box with Yes, No, ND, NA answers.
This setup works fine for data entry; a nice form with all the questions on
one screen/page can be generated. The problem is when I try to run a report
which is based on a month’s worth of transfers and counts all the Yes’s all
the no’s, all the NA’s and all the ND’s and gives a percentage total. Like
this:
February 2005
Yes No NA ND
Signature Obtained 40-90% 3-10% 0 0
Checklist Complete 50- 100% 0 0 0
Receiving Hospital Notified 25-50% 5-3% 0
I can’t figure out how to run a query to get this info or how to set it up
in a report without having to use a Dcount function for every question:
=DCount("[TransferID]","DateQry","[LocalMDNotified]='Yes'") –this counts the
number of transferIDs were the “LocalMDNotified†field has a “Yes†answer in
a query that shows only the transfers for a month’s time. This way for every
question I have to set this up four times: one for Yes, one for No, one for
NA, and one for ND
If I do it this way I can get what I need except I can’t reference a
specific table or query in the report properties data source or the whole
thing will repeat. I’m not sure if this will cause me problems down the road
or not.
I’ve read that it is better to set up a questionnaire with a table that has
a “questionID†field a “Response†field and a “respondentID†field (which for
me would be a the transferID field). I tried to set it up this way and can
easily get the report I want using a crosstab query however I can’t or don’t
know how to design a form with all the questions on one page. This setup has
a Questions table with the questionID field that is used in a combo-box. I
don’t want the person entering responses to have to pick a new question from
the combo box on the form. In other words the form will show a drop down with
a list of questions and a drop down with a list of answers for every transfer
ID the data entry person would have to enter each answer to each question on
a new form screen.
I keep thinking there must be a solution to this problem that is more
straightforward than what I have come up with the dcount functions on a
report. Please let me know if you have found a better solution.
Thank you and I apologize if this is hard to understand. I’m going a little
crazy with this.
purposes. Our hospital is tracking patient transfers. I have 18 questions for
which the answers can only be Yes, No, Not Applicable(NA), or Not
Documented(ND). Questions such as: Was a signature obtained?; Was a checklist
completed?; Was the receiving hospital notified?... and so on. I need a form
which will allow the person entering data to see all of the questions on one
screen with a combo box next to each containing the answers (Yes, No, NA, ND)
as well as a medical record number, the date of the transfer, the
transferring physicians name, attending physicians name, and the nurses name.
Right now I have
A “Physicians†table which will be used for a combo box to lookup their name
on the form.
Fields: physicianID(key), MDLastName, MDFirstName
A “Nurses†table which will be used for a combo box to lookup their name on
the form
Fields: nurseID(key), RNLastName, RNFirstName
A “Medical Record†table which stores only a medical record number and
nothing else, which is necessary because each patient could have several
transfers and I want to create a relationship with the “Transfers†table.
Field: MedRecNo(key)
A “Transfers†table to hold info about each transfer
Fields: transferID(key), Date, physicianID, transferringMDID, nurseID,
MedicalRecordNumber, and a field for each question with a value list combo
box with Yes, No, ND, NA answers.
This setup works fine for data entry; a nice form with all the questions on
one screen/page can be generated. The problem is when I try to run a report
which is based on a month’s worth of transfers and counts all the Yes’s all
the no’s, all the NA’s and all the ND’s and gives a percentage total. Like
this:
February 2005
Yes No NA ND
Signature Obtained 40-90% 3-10% 0 0
Checklist Complete 50- 100% 0 0 0
Receiving Hospital Notified 25-50% 5-3% 0
I can’t figure out how to run a query to get this info or how to set it up
in a report without having to use a Dcount function for every question:
=DCount("[TransferID]","DateQry","[LocalMDNotified]='Yes'") –this counts the
number of transferIDs were the “LocalMDNotified†field has a “Yes†answer in
a query that shows only the transfers for a month’s time. This way for every
question I have to set this up four times: one for Yes, one for No, one for
NA, and one for ND
If I do it this way I can get what I need except I can’t reference a
specific table or query in the report properties data source or the whole
thing will repeat. I’m not sure if this will cause me problems down the road
or not.
I’ve read that it is better to set up a questionnaire with a table that has
a “questionID†field a “Response†field and a “respondentID†field (which for
me would be a the transferID field). I tried to set it up this way and can
easily get the report I want using a crosstab query however I can’t or don’t
know how to design a form with all the questions on one page. This setup has
a Questions table with the questionID field that is used in a combo-box. I
don’t want the person entering responses to have to pick a new question from
the combo box on the form. In other words the form will show a drop down with
a list of questions and a drop down with a list of answers for every transfer
ID the data entry person would have to enter each answer to each question on
a new form screen.
I keep thinking there must be a solution to this problem that is more
straightforward than what I have come up with the dcount functions on a
report. Please let me know if you have found a better solution.
Thank you and I apologize if this is hard to understand. I’m going a little
crazy with this.