Vertical Report

[MN]

Hello,
I have a table like:
ID UnitName Task1 Score1 Task2 Score2 ... Task10 Score10
Task1 - Task10: Number (from 100 to 200)
Score1 - Score10: Number (1-6)
Q: How can I do a report like:

Task 1 2 3 4 5 6 Name
100 2 1 1 Unit 1
200 2 2 Unit 2
199 1 Unit 3
Thank you for any reply-MN

 

[Duane Hookom]

You would first normalize your data so you don't have repeat groups of
fields. If you can't or won't normalize your tables, you can create a union
query to normalize.

SELECT ID, UnitName, Task1 as Task, Score1 as Score, 1 as Grp
FROM tblNoName
UNION ALL
SELECT ID, UnitName, Task2, Score2, 2
FROM tblNoName
UNION ALL
SELECT ID, UnitName, Task3, Score3, 3
FROM tblNoName
---- etc ---
UNION ALL
SELECT ID, UnitName, Task10 Score10, 10
FROM tblNoName;

YOu can then create a crosstab query with Task and UnitName as Row Headings,
Score as the Column Heading, and Count Of ID as the Value.

 

[MN]

Hi Mr. Duane,
Thank you for taking your time. Yes, you are right. I would normalize my DB
but this design is my boss want it. I have recomment to diferrent way but he
didn't want it. Anyway, it's work and help me a lot. Again thanks a lot. I
greatly appreciate your time.- Regards,
MN