I have 4 tables for surveys and have created individual reports for the surveys. Now I was asked to created one report by pulling several questions from each of the tables. I now need to get the total number of surveys from each table for each month. I have the total number of surveys working correctly on my individual reports but not on this one report with just a few questions. I used the =Count(*) for my individual reports but when I use the Count(*) in my report with just a few questions, it is calcuating how many questions for the month (which is 1) - not giving me the total number of surveys that were actually completed which should be 30. Thanks for any help!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HELP USING "COUNT" TO GET TOTALS
- Thread starter dutches
- Start date
- Thread starter
- #3
No, I am not using subreports. I created a query from the 4 tables pulling the three questions from the tables that I needed into my query. From this I created the one report. One survey I know has 30 surveys for May03 and 141 for August 03. Does this help you any???
Thanks!
Thanks!
- Thread starter
- #5
the common field that links the table would be the date (month/year). in my query I have built an expression to get the averages into my report which is working fine. here is an example of what is in my query -
MYercare: Avg(IIf([eroverallratingofcare]=1,0,IIf([eroverallratingofcare]=2,25,IIf([eroverallratingofcare]=3,50,IIf([eroverallratingofcare]=4,75,1000)))))
MYpscare: Avg(IIf([psinstructionscareforself]=1,0,IIf([psinstructionscareforself]=2,25,IIf([psinstructionscareforself]=3,50,IIf([psinstructionscareforself]=4,75,100)))))
MYsocare: Avg(IIf([sopinstructionscareforself]=1,0,IIf([sopinstructionscareforself]=2,25,IIf([sopinstructionscareforself]=3,50,IIf([sopinstructionscareforself]=4,75,100)))))
this is also done for the other two questions. then i have this expression in my query for all the questions:
erInstructions: Round([MYercare],0)
psInstructions: Round([MYpscare],0)
ine.
they are group by [forms]![er]![er]
thanks, again!
MYercare: Avg(IIf([eroverallratingofcare]=1,0,IIf([eroverallratingofcare]=2,25,IIf([eroverallratingofcare]=3,50,IIf([eroverallratingofcare]=4,75,1000)))))
MYpscare: Avg(IIf([psinstructionscareforself]=1,0,IIf([psinstructionscareforself]=2,25,IIf([psinstructionscareforself]=3,50,IIf([psinstructionscareforself]=4,75,100)))))
MYsocare: Avg(IIf([sopinstructionscareforself]=1,0,IIf([sopinstructionscareforself]=2,25,IIf([sopinstructionscareforself]=3,50,IIf([sopinstructionscareforself]=4,75,100)))))
this is also done for the other two questions. then i have this expression in my query for all the questions:
erInstructions: Round([MYercare],0)
psInstructions: Round([MYpscare],0)
ine.
they are group by [forms]![er]![er]
thanks, again!
dutches
Well, the problem is that the date isn't what you would normally use to link the 4 tables. I was thinking of a RecordID or something similar, something that creates a one-to-many relationship. However, unless I am missing something here, it wouldn't appear that these tables are related.
You indicated that when you do a report on each table individually you get the proper results...but then when you try to put them together, things act up.
If the 4 tables aren't related, you won't be able to get good results by dragging them all into a Select query. Even if you seem to be getting some favourable results now you run the risk of developing a cartesian product with multiples of everything.
However, if the fields in each of the 4 tables are the same, or at least similar, in data type, you could do a Union query to combine the data from the 4 tables into one, and then build a Select query from that Union query to populate your report.
Another option - if the Union query idea doesn't work - would be to build a generic report, and then pull in a subreport from each of the 4 tables.
Tom
Well, the problem is that the date isn't what you would normally use to link the 4 tables. I was thinking of a RecordID or something similar, something that creates a one-to-many relationship. However, unless I am missing something here, it wouldn't appear that these tables are related.
You indicated that when you do a report on each table individually you get the proper results...but then when you try to put them together, things act up.
If the 4 tables aren't related, you won't be able to get good results by dragging them all into a Select query. Even if you seem to be getting some favourable results now you run the risk of developing a cartesian product with multiples of everything.
However, if the fields in each of the 4 tables are the same, or at least similar, in data type, you could do a Union query to combine the data from the 4 tables into one, and then build a Select query from that Union query to populate your report.
Another option - if the Union query idea doesn't work - would be to build a generic report, and then pull in a subreport from each of the 4 tables.
Tom
- Thread starter
- #7
I get so confused with all this! Sorry! The fields in the 4 tables are the same. The problem with the union query is that one table only has two of the questions whereas the other 3 tables have the 3 questions.
All my calculations are working correctly. I am just now stuck on the step of trying to get the number count of how many surveys were entered for each month and I am clueless!
All my calculations are working correctly. I am just now stuck on the step of trying to get the number count of how many surveys were entered for each month and I am clueless!
dutches
Feelin' a little confused myself. Because I can't quite picture in my head how you are getting combined results in a query when there is no established relationship among the 4 tables. Also, I can't quite picture the "questions."
Can you give me the exact field names for each of the 4 tables? And an idea about what the questions are?
Tom
Feelin' a little confused myself. Because I can't quite picture in my head how you are getting combined results in a query when there is no established relationship among the 4 tables. Also, I can't quite picture the "questions."
Can you give me the exact field names for each of the 4 tables? And an idea about what the questions are?
Tom
- Thread starter
- #9
table - emergencyroom
field name - erinstructionscareforself
field name - eroverallratingofcare
field name - erlikelihoodrecomservices
table - inpatientsatisfaction
field name - psinstructionscareforself
field name - psoverallratingofcare
field name - pslikelihoodrecomservices
table - outpatientservices
field name - opoverallratingofcare
field name - oplikelihoodrecomservices
table - surgicaloutpatient
field name - sopinstructionscareforself
field name - sopoverallratingofcare
field name - soplikelihoodrecomservices
the report is pulled by whichever month & year that we need.
for emergency room for may03 there were only 2 surveys
for inpatient satisfaction for may03 only 16 surveys
for outpatient services for may03 only 1 survey
for surgical outpatient for may03 only 12 surveys
this information prints at the top of each individual report for the may03. now i need this total number (31) to be on the one report that i created with the fields that are listed above.
field name - erinstructionscareforself
field name - eroverallratingofcare
field name - erlikelihoodrecomservices
table - inpatientsatisfaction
field name - psinstructionscareforself
field name - psoverallratingofcare
field name - pslikelihoodrecomservices
table - outpatientservices
field name - opoverallratingofcare
field name - oplikelihoodrecomservices
table - surgicaloutpatient
field name - sopinstructionscareforself
field name - sopoverallratingofcare
field name - soplikelihoodrecomservices
the report is pulled by whichever month & year that we need.
for emergency room for may03 there were only 2 surveys
for inpatient satisfaction for may03 only 16 surveys
for outpatient services for may03 only 1 survey
for surgical outpatient for may03 only 12 surveys
this information prints at the top of each individual report for the may03. now i need this total number (31) to be on the one report that i created with the fields that are listed above.
dutches
Just picked this up. I'll have a look at it this evening.
A couple more questions...
1. What is the data type for the fields in the tables? Numeric or Text?
2. Where does the Date (in this case May03) come in? None of these fields appear to be a date.
3. I'm still looking for a field that links the 4 tables. Or are they joined in some other way? I can see how you can get individual reports, but it's not clear to me how you are joining them in your query.
4. What fields do you expect to show up in the report?
Regards.
Tom
Just picked this up. I'll have a look at it this evening.
A couple more questions...
1. What is the data type for the fields in the tables? Numeric or Text?
2. Where does the Date (in this case May03) come in? None of these fields appear to be a date.
3. I'm still looking for a field that links the 4 tables. Or are they joined in some other way? I can see how you can get individual reports, but it's not clear to me how you are joining them in your query.
4. What fields do you expect to show up in the report?
Regards.
Tom
- Thread starter
- #12
SELECT emergencyroom.ermonth, Avg(IIf([eroverallratingofcare]=1,0,IIf([eroverallratingofcare]=2,25,IIf([eroverallratingofcare]=3,50,IIf([eroverallratingofcare]=4,75,1000))))) AS MYercare, Avg(IIf([psinstructionscareforself]=1,0,IIf([psinstructionscareforself]=2,25,IIf([psinstructionscareforself]=3,50,IIf([psinstructionscareforself]=4,75,100))))) AS MYpscare, Avg(IIf([sopinstructionscareforself]=1,0,IIf([sopinstructionscareforself]=2,25,IIf([sopinstructionscareforself]=3,50,IIf([sopinstructionscareforself]=4,75,100))))) AS MYsocare, Avg(IIf([eroverallratingofcare]=1,0,IIf([eroverallratingofcare]=2,25,IIf([eroverallratingofcare]=3,50,IIf([eroverallratingofcare]=4,75,100))))) AS MYerrating, Avg(IIf([psoverallratingofcare]=1,0,IIf([psoverallratingofcare]=2,25,IIf([psoverallratingofcare]=3,50,IIf([psoverallratingofcare]=4,75,100))))) AS MYpsrating, Avg(IIf([sopoverallratingofcare]=1,0,IIf([sopoverallratingofcare]=2,25,IIf([sopoverallratingofcare]=3,50,IIf([sopoverallratingofcare]=4,75,100))))) AS MYsorating, Avg(IIf([opoverallratingofcare]=1,0,IIf([opoverallratingofcare]=2,25,IIf([opoverallratingofcare]=3,50,IIf([opoverallratingofcare]=4,75,100))))) AS MYoprating, Avg(IIf([erlikelihoodrecomservices]=1,0,IIf([erlikelihoodrecomservices]=2,25,IIf([erlikelihoodrecomservices]=3,50,IIf([erlikelihoodrecomservices]=4,75,100))))) AS MYerrecommend, Avg(IIf([pslikelihoodrecomservices]=1,0,IIf([pslikelihoodrecomservices]=2,25,IIf([pslikelihoodrecomservices]=3,50,IIf([pslikelihoodrecomservices]=4,75,100))))) AS MYpsrecommend, Avg(IIf([soplikelihoodrecomservices]=1,0,IIf([soplikelihoodrecomservices]=2,25,IIf([soplikelihoodrecomservices]=3,50,IIf([soplikelihoodrecomservices]=4,75,100))))) AS MYsorecommend, Avg(IIf([oplikelihoodrecomservices]=1,0,IIf([oplikelihoodrecomservices]=2,25,IIf([oplikelihoodrecomservices]=3,50,IIf([oplikelihoodrecomservices]=4,75,100))))) AS MYoprecommend, Round([MYercare],0) AS erInstructions, Round([MYpscare],0) AS psInstructions, Round([MYsocare],0) AS soInstructions, Round([MYerrating],0) AS ercarerating, Round([MYpsrating],0) AS pscarerating, Round([MYsorating],0) AS socarerating, Round([MYoprating],0) AS opcarerating, Round([MYerrecommend],0) AS errecommendservice, Round([MYpsrecommend],0) AS psrecommendservice, Round([MYsorecommend],0) AS sorecommendservice, Round([MYoprecommend],0) AS oprecommendservice
FROM inpatientsatisfaction RIGHT JOIN (outpatientservices RIGHT JOIN (emergencyroom RIGHT JOIN surgicaloutpatient ON emergencyroom.ermonth = surgicaloutpatient.sopmonth) ON outpatientservices.opmonth = surgicaloutpatient.sopmonth) ON inpatientsatisfaction.psmonth = surgicaloutpatient.sopmonth
GROUP BY emergencyroom.ermonth
HAVING (((emergencyroom.ermonth)=[forms]![er]![er]));
FROM inpatientsatisfaction RIGHT JOIN (outpatientservices RIGHT JOIN (emergencyroom RIGHT JOIN surgicaloutpatient ON emergencyroom.ermonth = surgicaloutpatient.sopmonth) ON outpatientservices.opmonth = surgicaloutpatient.sopmonth) ON inpatientsatisfaction.psmonth = surgicaloutpatient.sopmonth
GROUP BY emergencyroom.ermonth
HAVING (((emergencyroom.ermonth)=[forms]![er]![er]));
- Thread starter
- #13
the data type is numeric.
in the query i have for a field - ERMONTH and in the criteria is have [forms]![er]![er]
may03 is a month that i knew did not have many surveys so this has been the one that i have been playing around with
to get this one hospital wide report.
this is what my reports looks like - with my total number of surveys that i am trying to get to pull into this report.
HOSPITAL WIDE SURVEY RESULTS
FOR MAY03
INSTRUCTIONS GIVEN TO PATIENTS BY NURSE ON HOW TO CARE FOR
THEMSELVES AT HOME :
HOSPITAL WIDE SCORE FOR INSTRUCTIONS 79
"Individual survey results" - GIVEN TO PATIENTS BY NURSE ON HOW TO CARE FOR THEMSELVES AT HOME -
EMERENCY ROOM SURVEY - 63
PATIENT SATISFACTION SURVEY - 77
OUTPATIENT SURGERY SURVEY - 98
OVERALL RATING OF THE CARE PATIENTS RECEIVED DURING THEIR VISIT :
HOSPITAL WIDE SCORE FOR THE OVERALL 87
"individual survey results" - RATING OF THE CARE PATIENTS RECEIVED DURING THEIR VISIT -
EMERGENCY ROOM SURVEY - 63
PATIENT SATISFACTION SURVEY - 89
OUTPATIENT SERVICES SURVEY - 100
OUTPATIENT SURGERY SURVEY - 98
LIKELIHOOD OF RECOMMENDING THE HOSPITAL TO OTHERS :
HOSPITAL WIDE SCORE FOR LIKELIHOOD OF 87
"individual survey results" -PATIENTS RECOMMENDING THE HOSPITAL TO OTHERS -
EMERGENCY ROOM SURVEY - 63
PATIENT SATISFACTION SURVEY - 88
OUTPATIENT SERVICES SURVEY - 100
OUTPATIENT SURGERY SURVEY - 100
in the query i have for a field - ERMONTH and in the criteria is have [forms]![er]![er]
may03 is a month that i knew did not have many surveys so this has been the one that i have been playing around with
to get this one hospital wide report.
this is what my reports looks like - with my total number of surveys that i am trying to get to pull into this report.
HOSPITAL WIDE SURVEY RESULTS
FOR MAY03
INSTRUCTIONS GIVEN TO PATIENTS BY NURSE ON HOW TO CARE FOR
THEMSELVES AT HOME :
HOSPITAL WIDE SCORE FOR INSTRUCTIONS 79
"Individual survey results" - GIVEN TO PATIENTS BY NURSE ON HOW TO CARE FOR THEMSELVES AT HOME -
EMERENCY ROOM SURVEY - 63
PATIENT SATISFACTION SURVEY - 77
OUTPATIENT SURGERY SURVEY - 98
OVERALL RATING OF THE CARE PATIENTS RECEIVED DURING THEIR VISIT :
HOSPITAL WIDE SCORE FOR THE OVERALL 87
"individual survey results" - RATING OF THE CARE PATIENTS RECEIVED DURING THEIR VISIT -
EMERGENCY ROOM SURVEY - 63
PATIENT SATISFACTION SURVEY - 89
OUTPATIENT SERVICES SURVEY - 100
OUTPATIENT SURGERY SURVEY - 98
LIKELIHOOD OF RECOMMENDING THE HOSPITAL TO OTHERS :
HOSPITAL WIDE SCORE FOR LIKELIHOOD OF 87
"individual survey results" -PATIENTS RECOMMENDING THE HOSPITAL TO OTHERS -
EMERGENCY ROOM SURVEY - 63
PATIENT SATISFACTION SURVEY - 88
OUTPATIENT SERVICES SURVEY - 100
OUTPATIENT SURGERY SURVEY - 100
- Thread starter
- #15
dutches
Hang on. This is long. But maybe the most important part of this epistle is #3 under “Notes & Recommendations.” Anyway, here's what I did.
tables
I made 4 tables, “emergencyroom”, “inpatientsatisfaction”,”outpatientservices”, “surgicaloutpatient” and used the fields you gave me, plus ermonth, psmonth, opmonth and sopmonth.
In emergencyroom I entered 2 surveys with a date of May 1/03
In inpatientsatisfaction I entered 16 surveys with a date of May 1/03
In outpatientservices I entered 1 survey with a date of May 1/03
In surgicaloutpatient I entered 12 surveys with a date of May 1/03
Queries
Believing that the only way you are going to get an accurate count of the surveys, by the time you get to the report, is by doing the count in the query, I made 9 queries.
The first 4 queries are straightforward Select queries based on the tables, and I did the Rounding here but not the averaging. I will show the SQL
qryEmergency
SELECT emergencyroom.eroverallratingofcare, Round(IIf([eroverallratingofcare]=1,0,IIf([eroverallratingofcare]=2,25,IIf([eroverallratingofcare]=3,50,IIf([eroverallratingofcare]=4,75)))),0) AS MYercare, emergencyroom.ermonth
FROM emergencyroom;
qryInpatient
SELECT inpatientsatisfaction.psinstructionscareforself, Round(IIf([psinstructionscareforself]=1,0,IIf([psinstructionscareforself]=2,25,IIf([psinstructionscareforself]=3,50,IIf([psinstructionscareforself]=4,75)))),0) AS MYpscare, inpatientsatisfaction.psmonth
FROM inpatientsatisfaction;
qryOutpatient
SELECT outpatientservices.opmonth, outpatientservices.opoverallratingofcare, Round(IIf([opoverallratingofcare]=1,0,IIf([opoverallratingofcare]=2,25,IIf([opoverallratingofcare]=3,50,IIf([opoverallratingofcare]=4,75)))),0) AS MYopcare
FROM outpatientservices;
qrySurgical
SELECT surgicaloutpatient.sopmonth, surgicaloutpatient.sopinstructionscareforself, Round(IIf([sopinstructionscareforself]=1,0,IIf([sopinstructionscareforself]=2,25,IIf([sopinstructionscareforself]=3,50,IIf([sopinstructionscareforself]=4,75)))),0) AS MYsopcare
FROM surgicaloutpatient;
Then I built 4 Totals queries based on each of the above. Here, I got Count and Average, and also added the criteria column (Forms!er!er). The logic is Count the dates (this will give you the number of surveys conducted); Average the Scores from the first query; select the Month.
qryEMERG
SELECT Count(qryemergency.ermonth) AS CountOfermonth, Avg(qryemergency.MYercare) AS AvgOfMYercare, qryemergency.ermonth
FROM qryemergency
WHERE (((qryemergency.ermonth)=[Forms]![er]![er]))
GROUP BY qryemergency.ermonth;
qryINPAT
SELECT Count(qryinpatient.psmonth) AS CountOfpsmonth, Avg(qryinpatient.MYpscare) AS AvgOfMYpscare, qryinpatient.psmonth
FROM qryinpatient
WHERE (((qryinpatient.psmonth)=[Forms]![er]![er]))
GROUP BY qryinpatient.psmonth;
qryOUTPAT
SELECT Count(qryoutpatient.opmonth) AS CountOfopmonth, Avg(qryoutpatient.MYopcare) AS AvgOfMYopcare, qryoutpatient.opmonth
FROM qryoutpatient
WHERE (((qryoutpatient.opmonth)=[Forms]![er]![er]))
GROUP BY qryoutpatient.opmonth;
qrySURG
SELECT Count(qrysurgical.sopmonth) AS CountOfsopmonth, Avg(qrysurgical.MYsopcare) AS AvgOfMYsopcare, qrysurgical.sopmonth
FROM qrysurgical
WHERE (((qrysurgical.sopmonth)=[Forms]![er]![er]))
GROUP BY qrysurgical.sopmonth;
Then I built 1 query to combine the data. I just joined the date fields. I notice that you used RIGHT JOIN, but that gave me ambiguous outer joins.
qryCOMBINED
SELECT qryEMERG.CountOfermonth, qryEMERG.AvgOfMYercare, qryINPAT.CountOfpsmonth, qryINPAT.AvgOfMYpscare, qryOUTPAT.CountOfopmonth, qryOUTPAT.AvgOfMYopcare, qrySURG.CountOfsopmonth, qrySURG.AvgOfMYsopcare
FROM qrySURG INNER JOIN (qryOUTPAT INNER JOIN (qryEMERG INNER JOIN qryINPAT ON qryEMERG.ermonth = qryINPAT.psmonth) ON qryOUTPAT.opmonth = qryEMERG.ermonth) ON qrySURG.sopmonth = qryEMERG.ermonth;
REPORT
My report, for May 1, 2003 looks like this…
HEADER DEPARTMENT # SURVEYS AVERAGE SCORE
Emergency 2 50
In Patient 16 37.5
Out Patient 1 50
Surgery 12 37.5
REPORT FOOTER 31
has a text box with the structure =[CountOfermonth]+[CountOfpsmonth]+[CountOfopMonth]+[CountOfsopmonth]
NOTES & RECOMMENDATIONS
Even though I got this to work, I don’t like it. Here’s why.
1. Unless there is something more in your tables that you didn’t give me, your data is not fully “normalized” (don’t you love that word?) so that these four tables will hinge together properly. There is no common link. A common linkage would normally be a primary key in one table and a foreign key (the primary key from the first table) in another. Without this, the data is hard to pull together.
2. Say you wanted to average the averages in the report. How do you do it? Well, you can add a text box with a hidden control source of =1 beside each average control, and then in the report footer put a text box “=(Average1 + Average2 + Average3 + Average4)/(Text 22 + Text 23 + Text 24 + Text 25)” but it’s a cumbersome process. And you can’t sum or count text boxes that don’t match. That’s why you couldn’t get a count before.
3. You have pretty common fields in your survey tables. I’d be inclined to combine them into one table with 5 fields: RecordID (primary key, either autonumber or some other form of unique number) SurveyDate, Department, CareForSelf, OverAllRating, LikelihoodRecom. The input form would select the Department (Emergency, InPatient, OutPatient, Surgical) by a combo box. If you do this, you will have a lot easier job of maintaining your database, building queries and reports. You could still get individual reports by Department, but combined reports would be tons easier.
4. Another way to go (I like it less, but had to use it myself recently) would be to build a generic report with 4 subreports, one for each of the departments.
Good luck!
Tom
Hang on. This is long. But maybe the most important part of this epistle is #3 under “Notes & Recommendations.” Anyway, here's what I did.
tables
I made 4 tables, “emergencyroom”, “inpatientsatisfaction”,”outpatientservices”, “surgicaloutpatient” and used the fields you gave me, plus ermonth, psmonth, opmonth and sopmonth.
In emergencyroom I entered 2 surveys with a date of May 1/03
In inpatientsatisfaction I entered 16 surveys with a date of May 1/03
In outpatientservices I entered 1 survey with a date of May 1/03
In surgicaloutpatient I entered 12 surveys with a date of May 1/03
Queries
Believing that the only way you are going to get an accurate count of the surveys, by the time you get to the report, is by doing the count in the query, I made 9 queries.
The first 4 queries are straightforward Select queries based on the tables, and I did the Rounding here but not the averaging. I will show the SQL
qryEmergency
SELECT emergencyroom.eroverallratingofcare, Round(IIf([eroverallratingofcare]=1,0,IIf([eroverallratingofcare]=2,25,IIf([eroverallratingofcare]=3,50,IIf([eroverallratingofcare]=4,75)))),0) AS MYercare, emergencyroom.ermonth
FROM emergencyroom;
qryInpatient
SELECT inpatientsatisfaction.psinstructionscareforself, Round(IIf([psinstructionscareforself]=1,0,IIf([psinstructionscareforself]=2,25,IIf([psinstructionscareforself]=3,50,IIf([psinstructionscareforself]=4,75)))),0) AS MYpscare, inpatientsatisfaction.psmonth
FROM inpatientsatisfaction;
qryOutpatient
SELECT outpatientservices.opmonth, outpatientservices.opoverallratingofcare, Round(IIf([opoverallratingofcare]=1,0,IIf([opoverallratingofcare]=2,25,IIf([opoverallratingofcare]=3,50,IIf([opoverallratingofcare]=4,75)))),0) AS MYopcare
FROM outpatientservices;
qrySurgical
SELECT surgicaloutpatient.sopmonth, surgicaloutpatient.sopinstructionscareforself, Round(IIf([sopinstructionscareforself]=1,0,IIf([sopinstructionscareforself]=2,25,IIf([sopinstructionscareforself]=3,50,IIf([sopinstructionscareforself]=4,75)))),0) AS MYsopcare
FROM surgicaloutpatient;
Then I built 4 Totals queries based on each of the above. Here, I got Count and Average, and also added the criteria column (Forms!er!er). The logic is Count the dates (this will give you the number of surveys conducted); Average the Scores from the first query; select the Month.
qryEMERG
SELECT Count(qryemergency.ermonth) AS CountOfermonth, Avg(qryemergency.MYercare) AS AvgOfMYercare, qryemergency.ermonth
FROM qryemergency
WHERE (((qryemergency.ermonth)=[Forms]![er]![er]))
GROUP BY qryemergency.ermonth;
qryINPAT
SELECT Count(qryinpatient.psmonth) AS CountOfpsmonth, Avg(qryinpatient.MYpscare) AS AvgOfMYpscare, qryinpatient.psmonth
FROM qryinpatient
WHERE (((qryinpatient.psmonth)=[Forms]![er]![er]))
GROUP BY qryinpatient.psmonth;
qryOUTPAT
SELECT Count(qryoutpatient.opmonth) AS CountOfopmonth, Avg(qryoutpatient.MYopcare) AS AvgOfMYopcare, qryoutpatient.opmonth
FROM qryoutpatient
WHERE (((qryoutpatient.opmonth)=[Forms]![er]![er]))
GROUP BY qryoutpatient.opmonth;
qrySURG
SELECT Count(qrysurgical.sopmonth) AS CountOfsopmonth, Avg(qrysurgical.MYsopcare) AS AvgOfMYsopcare, qrysurgical.sopmonth
FROM qrysurgical
WHERE (((qrysurgical.sopmonth)=[Forms]![er]![er]))
GROUP BY qrysurgical.sopmonth;
Then I built 1 query to combine the data. I just joined the date fields. I notice that you used RIGHT JOIN, but that gave me ambiguous outer joins.
qryCOMBINED
SELECT qryEMERG.CountOfermonth, qryEMERG.AvgOfMYercare, qryINPAT.CountOfpsmonth, qryINPAT.AvgOfMYpscare, qryOUTPAT.CountOfopmonth, qryOUTPAT.AvgOfMYopcare, qrySURG.CountOfsopmonth, qrySURG.AvgOfMYsopcare
FROM qrySURG INNER JOIN (qryOUTPAT INNER JOIN (qryEMERG INNER JOIN qryINPAT ON qryEMERG.ermonth = qryINPAT.psmonth) ON qryOUTPAT.opmonth = qryEMERG.ermonth) ON qrySURG.sopmonth = qryEMERG.ermonth;
REPORT
My report, for May 1, 2003 looks like this…
HEADER DEPARTMENT # SURVEYS AVERAGE SCORE
Emergency 2 50
In Patient 16 37.5
Out Patient 1 50
Surgery 12 37.5
REPORT FOOTER 31
has a text box with the structure =[CountOfermonth]+[CountOfpsmonth]+[CountOfopMonth]+[CountOfsopmonth]
NOTES & RECOMMENDATIONS
Even though I got this to work, I don’t like it. Here’s why.
1. Unless there is something more in your tables that you didn’t give me, your data is not fully “normalized” (don’t you love that word?) so that these four tables will hinge together properly. There is no common link. A common linkage would normally be a primary key in one table and a foreign key (the primary key from the first table) in another. Without this, the data is hard to pull together.
2. Say you wanted to average the averages in the report. How do you do it? Well, you can add a text box with a hidden control source of =1 beside each average control, and then in the report footer put a text box “=(Average1 + Average2 + Average3 + Average4)/(Text 22 + Text 23 + Text 24 + Text 25)” but it’s a cumbersome process. And you can’t sum or count text boxes that don’t match. That’s why you couldn’t get a count before.
3. You have pretty common fields in your survey tables. I’d be inclined to combine them into one table with 5 fields: RecordID (primary key, either autonumber or some other form of unique number) SurveyDate, Department, CareForSelf, OverAllRating, LikelihoodRecom. The input form would select the Department (Emergency, InPatient, OutPatient, Surgical) by a combo box. If you do this, you will have a lot easier job of maintaining your database, building queries and reports. You could still get individual reports by Department, but combined reports would be tons easier.
4. Another way to go (I like it less, but had to use it myself recently) would be to build a generic report with 4 subreports, one for each of the departments.
Good luck!
Tom
dutches
You might be interested in taking a look at Duane Hookom's survey database. Here's the link
Tom
You might be interested in taking a look at Duane Hookom's survey database. Here's the link
Tom
- Thread starter
- #18
oh, my gosh, tom, how did you do all that so fast!!!!! i printed what you sent so tomorrow i can sit down and really look this over. i am totally blown away! i just tried my database using the date aug03 and something isn't working. my calculations are right except for the first one! this is a total nightmare! thanks so much for your help. i will stay in touch!
i went to the above website but didn't get anywhere. i will try again.
wish me luck tomorrow!!
i went to the above website but didn't get anywhere. i will try again.
wish me luck tomorrow!!
dutches
The reason I send the link to Duane Hookom's survey form approach was that Duane is certainly one of the top experts in this forum, and he has done some really good stuff. I wasn't thinking that you need to totally revamp your survey approach to his, but thought it might be a helpful example to look at...just to see how somebody else did things.
You say you got nowhere at the site. Maybe it's the link. Just go to and look for "At Your Survey" by Duane Hookom.
I made note of your e-mail address, but don't be surprised if it isn't in the post by the morning. There's some kind of sweep thing that goes through and removes e-mail addresses, so that they don't become subject to unwanted stuff.
Good luck tomorrow!
Tom
The reason I send the link to Duane Hookom's survey form approach was that Duane is certainly one of the top experts in this forum, and he has done some really good stuff. I wasn't thinking that you need to totally revamp your survey approach to his, but thought it might be a helpful example to look at...just to see how somebody else did things.
You say you got nowhere at the site. Maybe it's the link. Just go to and look for "At Your Survey" by Duane Hookom.
I made note of your e-mail address, but don't be surprised if it isn't in the post by the morning. There's some kind of sweep thing that goes through and removes e-mail addresses, so that they don't become subject to unwanted stuff.
Good luck tomorrow!
Tom
- Status
Similar threads
- Locked
- Question
