Need Help Averaging A Pivot Chart

[azeotrope]

I have been hacking at this for a few days. This is what I'm working with.



Me![varx] = DLookup("[field_name]", "tblsurveyfields", "[Description] = '" & Me!
[ListAppealQuestions] & "'")


GphCRV.RowSource = " PARAMETERS [forms]![frmAppealSummary]!
[ListModelYear] Value, [forms]![frmAppealSummary]!
[ListAppealQuestions] Value, [forms]![frmAppealSummary]![varx] Value; " _
& " TRANSFORM count(Summary.ID) AS CountOfID " _
& " SELECT tblResponse.Result " _
& " FROM Summary INNER JOIN tblResponse ON Summary." & [Forms]![frmAppealSummary]!varx & " = tblResponse.Result " _
& " WHERE ((([Forms]![frmAppealSummary]!
[ListAppealQuestions]) Is Not Null) And ((Summary.MFRMODEL) = 'crv' ) And ((Summary.MFRMDLYR) = [Forms]![frmAppealSummary]!
[ListModelYear]))" _
& " GROUP BY tblResponse.Result " _
& " PIVOT Summary.MFRMODEL; "
GphCRV.Requery

I'm trying to use this same query to average the columns.

Thanks

[rpeare]

Can you provide a sample database with some junk data for analysis. just the parts that you're mentioning, not the whole database.

[azeotrope]

ID_# R1 R2 R3 mfrmodel mfrmdlyr
1234 2 7 5 prius 2010
1455 4 9 8 focus 2004
1334 7 10 7 crv 2008
1664 9 3 10 yukon 2012
The models and years appear multiple times. The R* columns are various questions that have been answered with a number between 1 and 10.
So I basically want to select a certain model year and a question(r1,r2,r3,etc), graph the question results sorted by year(mfrmdlyr) and model(mfrmodel) and then display the average of the results either on in a text box or ideally a chart displaying each individual model's average.
The code I posted above graphs the results of a selected question and model year.

[rpeare]

can you provide a sample of the database I'd rather not re-create everything from scratch

[azeotrope]

https://user.iter.org/filesharing/?u...6-392bdc5e64a8 thanks

[rpeare]

Simple example
Database2.zip

[azeotrope]

That looks great. Am I able to use that type of query with a variable?
I have a second table with the specific question name which call the associated 'R' value question column from the other table. My above code might be a little more explanatory

[rpeare]

assuming your table storing the question text is using the same numbering system as your question (i.e. 1 = the first question, 10 = the last question) and you're not using an autonumber PK that doesn't coincide with the actual question numbers you can use a dlookup or something like that to populate your label

[azeotrope]

Thanks a lot rpeare. I appreciate the help and everything worked out.