CODEWITHSUNDEEP
sqlms-access
Mathijs Gast
Mathijs Gast

Reputation: 3

Access SQL - Select same column twice for different criteria

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

Upvotes: 0

Views: 329

Answers (1)

iDevlop
iDevlop

Reputation: 25252

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.

Upvotes: 1

Related Questions