Combined analysing chart!!!

[kp1279]

Here is a puzzler.

I have a database which has in it a number of participants, a number of products, and the testing grades given.

The participants test a number of products, usually 3, a number of times, each time they grade the products for look, which is A - E, Useability which is 1 - 5, and Value which is also 1 - 5.

I want to create a report which will show me the participant, and the 3 products. I want the report to show me how many times they have tested each product, and also count the amount of times that they have graded for looks A, B, C etc. how many times graded useability 1, 2, 3 etc. how many time graded value 1, 2, 3 etc. so that the report looks something like:

-----------------------------------------------------------
Participant: ????????

Product: 1st 2nd 3rd
??? ??? ???

Look: A B C D E
?? ?? ?? ?? ??

Useability: 1 2 3 4 5
?? ?? ?? ?? ??

Value: 1 2 3 4 5
?? ?? ?? ?? ??

----------------------------------------------------------

Each series of ?? is a value that is in a table

I have a number of crosstab queries that I have created which gives me the results for each item, however how do I get these onto one report?? because when I try the report crashes for a number of reasons.

Is there a way, any ideas would be apreciated, even a stab in the dark will hopefully give me a pointer.

 

[dhookom]

Would you mind sharing your table structure(s) and some sample data? It's nearly impossible to even "stab in the dark" without some clue as to what we are initially working with.

It would also help to see the resulting report display based on the sample records that you provide.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kp1279]

I cant import the data but I can try and explain further

From the table I have the following fields that I want to have the report give me.

Participant - Text field - Combo box from another table
Product - Text field - Combo box from another table
Look - Text field - Combo box from another table, but value static A - E
Useability - Text field - Combo box from another table, but static value 1 - 5
Value - text field - Combo box from another table, but static value 1 - 5

There is a task number which is a unique random autonumber which separates all the records.

From the fileds above I want a report which shows me a layout similar to that already shown, so I can see the participant, the 3 products, and how many times they have graded the look, a, b etc, and useability, and value.

I can create a crosstab query for each participant, and the products but only one of the others.

If I do a crosstab query for the look, I create a simple query to bring the results back for all 5 fields, then from this I create the crosstab with the following values:

Participant - Group By - Row Heading
Product - Group By - Row Heading
Look - Group By - Column Heading
Look - Count - Value
Look - Where - In criteria - Is Not Null

This then create a datasheet with exactly what I want, it gives me the participant in the first column, against the 3 products, (So 3 Rows) then after each product in the row, the column headings are A - E and in each of these is the number of times that this product has been graded so it may look something like:

Participant | Product | A | B | C | D | E |
--------------------------------------------------------
smith | First | | 2 | 3 | | |
smith | Second | 2 | 1 | 1 | 1 | |


From this you can see that smith has tested 2 products, 5 times each, and graded the looks.

In the first example he graded the look B twice, and C three times etc.

It works exactly the same to the other field, (Useability & Value), but how do I create a report with all these in together, there the Participant only comes in once, the three products come in once each, and the grade or scores come in below?

Hopefully this has explained a little better, sorry I can't give the the exact data, but I have to create this database on a standalone machine which has no internet access, or active external drives.

Thanks

 

[dhookom]

I would first normalize the table with a union query (quniKPTest):

SELECT Participant, Product, "Look" AS Cat, Asc([Look])-64 AS Score
FROM tblKPTest where Look is not null
UNION ALL
SELECT Participant, Product, "Value", Value
FROM tblKPTest
WHERE Value is not null
UNION ALL
SELECT Participant, Product, "Useability", Useability
FROM tblKPTest
WHERE  Useability is not null;

Then create a crosstab from your union query:

TRANSFORM Val(nz(Count([Product]),0)) AS Expr2
SELECT quniKPTest.Participant, quniKPTest.Cat
FROM quniKPTest
GROUP BY quniKPTest.Participant, quniKPTest.Cat
PIVOT "Score" & [Score];

You can use a mulitple column subreport in your Participant header to list all the products.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kp1279]

Thanks for your help, I have tried this, and with and it seemed to work fine.

Thanks for your time and help.

KP