Transpose Query

[dastr]

Hi all,
I am trying to figure out a transpose query in access; I have the below table:
Name Score Rank
Inna 4 1
Anna 5 2
George 2 1
And I would like to have something like the following; I have only two Ranks: Rank 1 and Rank 2 – these I would like to be my new columns in a new table and under the relative rank I would like to have the score for that person:
Name Rank 1 Rank 2
Inna 1 X
Anna X 5
George 1 X
Is it possible to achieve this?
Any tips will be welcome, thank you.

 

[plog]

You will want a Cross Tab Query. Try this SQL:

TRANSFORM Sum(YourTableNameHere.Score) AS SumOfScore
SELECT YourTableNameHere.Name
FROM YourTableNameHere
GROUP BY YourTableNameHere.Name
PIVOT YourTableNameHere.Rank;

Also, 'Name' is a bad field name because it is a reserved word.

 

[dastr]

Hi there,

Suddenly I got an error msg upon executing this query:

Data type mismatch in criteria expression.

Do you know how to solve it out?

Thank you

 

[plog]

Data type mismatch in criteria expression.

My guess is that the Score field is saved as text or possibly contains null values. What is the field type of Score?

 

[dastr]

Thank you, may I also ask you to help me with the below; I have the:

TRANSFORM Sum(YourTableNameHere.Score) AS SumOfScore
SELECT YourTableNameHere.Name
FROM YourTableNameHere
GROUP BY YourTableNameHere.Name
PIVOT YourTableNameHere.Rank;

and I would like ideally to have:

Name Rank 1 Rank 2
Inna 10 X
Anna X 5
George 1 X

However, if I have two identical lines - say George Rank 1 - value for Rank 1 - 10 (say I have twice that line) the value under Rank 1 - is 20 - access adds up the two values for Rank 1. Can anyone advise what I may do?

Thanks.

We'd have a better shot if you posted the query. The error message is telling you that you are comparing some text value to a numeric column or vice versa.

PS - Your table is currently properly normalized. Leave your table structured the way it is. Use the crosstab to piviot it for reporting. Your proposed change will de-normalize your table and ultimately be harder to work with.