Calculating From 4 Tables

[dutches]

Can anyone help!! I am trying to get the total of 3 fields from 4 tables into one report. I can get the total of each field correct in a report by itself, but I can not get the 3 fields to add together so I can pull this into one report. This is so frustrating! I am working on 4 surveys that have several questions that are ranked 1-5. Now I need to pull only 3 questions from each survey, (although one table/survey will have only 2 of the questions)add these totals together and divide by 3 or 4 (# of surveys). I created a query with just these 3 questions but I've written every formula that I know of (which is not many but I've tried!) but I can't get my totals correct. Thanks for your help.
doubler@doublerguns.com

 

[lespaul]

why don't you tell us about the tables and the field names and how they are related, maybe provide a few sample records from the tables and what you want your final results to be.

tblName
FieldName1 (PK - Autonumber)

tblName2
FieldName2(PK)
FieldName3 (FK to FieldName1)

That would be very helpful.


Leslie

 

[dutches]

thanks, i will try! this may be long!

tbl-emergencyroom
fieldname-erinstructionscareforself
fieldname-eroverallratingofcare
fieldname-erlikelihoodrecomservices

tbl2-inpatientsatisfaction
fieldname-psinstructionscareforself
fieldname-psoverallratingofcare
fieldname-pslikelihoodrecomservices

tbl3-outpatientservices
fieldname-opoverallratingofcare
fieldname-oplikelihoodrecomservices

tbl4-surgicaloutpatient
fieldname-sopinstructionscareforself
fieldname-sopoverallratingofcare
fieldname-soplikelihoodrecomservices

the surveys have many questions but these are the only 3 that i am trying to total and put into one report. the surveys are pulled by month.

the questions are ranked 1-5 on my forms. in the individual report, the totals are added and this the expression is used for my printed report: for each survey and each question. i don't think i would need this for what i am trying to do now but i wanted you to see this.
(IIf([ertimebeforeseen]=1,0,IIf([ertimebeforeseen]=2,25,IIf([ertimebeforeseen]=3,50,IIf([ertimebeforeseen]=4,75,IIf([ertimebeforeseen]=5,100))))) i don't think i would need this for what i am trying to do now but i wanted you to see this.

ok, i am looking at may03 and the total for the question from all surveys for - Instruction On How To Care For Yourself At Home - should be 125.

this comes from:

1.emergency room survey total is 6
2.patient satisfaction survey total is 65
3.outpatient surgery survey total is 54
this equals = 125

then i was told to divide this number by 3 (for the 3 surveys) - so then hospital wide score for my one report for this question would be - 42 (rounded off)

does this help??
thanks, again!

 

[lespaul]

No this doesn't help much! I'm still confused!

Where is the date field that you are trying to collect records between?

Are there no primary keys in any of these tables?

Do you have some kind of table that determines which questions are on which surveys? (You talk about the 'Instruction on How TO care for your self at home' is that the survey? How do you know which questions are on that survey? Which table are the answers stored in? The four tables you listed above?)

Are you working with a normalized table structure?

Leslie

 

[JonFer]

Does each column represent a question and each row represent a survey?

You can use a Union query to combine the results for all 3 or 4 tables:

Select "OS" as Type, Sum(25*(field1-1)) as Field1, Sum(25*(field2-1)) as Field2
From tblOS
Union All
Select "PS", Sum(25*(field1-1)), Sum(25*(field2-1))
From tblPS
...

Then write a query against the Union query to calculate the average.