0

I've been struggling with the following table for a while now. Hopefully anyone can help me out. 

Item  Type  Value
A     X     2
B     X     3
C     X     4
D     X     5
A     Y     0.1
B     Y     0.3
C     Y     0.4
D     Y     0.6

The result I would like to see is this:

Item  X     Y
A     2     0.1
B     3     0.3
C     4     0.4
D     5     0.6

Is it possible to fix this in one query? I tried Union queries and IIF statements, but none of it gives me the desired result. Another option might be to split it up in multiple queries, however I would rather have it done in once.

Looking forward to any answer.

Many thanks!

Best,

Mathijs

jarlh
  • 42,561
  • 8
  • 45
  • 63
Mathijs Gast
  • 3
  • 1
  • Possible duplicate of [Pivoting data in MS Access](https://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access) – Radim Bača Oct 09 '17 at 09:21

1 Answers1

1

That's a job for a Crosstab query. 

TRANSFORM Max(Table1.Valu) AS MaxOfValu
SELECT Table1.item
FROM Table1
GROUP BY Table1.item
PIVOT Table1.type;

ps: Value is a reserved word and cannot be used as a field name. And I would never used Type or Item either.

iDevlop
  • 24,841
  • 11
  • 90
  • 149