using ms/access crosstab to obtain a value

I have a table with three columns. The first column has a reference to a table. And the second, to another table, like this:

Table Before:

Reference          Type        Code
Ref1                    A             C001
Ref2                    A             C002
Ref3                    A             C003
Ref4                    B             C005
Ref1                    B             C007
Ref5                    B             C006
Ref3                    B             C008
Ref2                    B             C009

In need to produce a table containing:

Reference         A         B
Ref1                  C001    C007
Ref2                  C002    C009
Ref3                  C003     @null
Ref4                  @null   C005
Ref5                  @null    C006

it is a bit like the crosstab but, instead of a sum ou a count I do need to have the actual values. Crosstab does not allow me for that (only accepts counts/sums as the value, and I do need the values as they are key to another table for a report.

can anyone help?
João serras-pereiraAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this query

TRANSFORM First(tblRef.[Code]) AS FirstOfCode
SELECT tblRef.[Reference]
FROM tblRef
GROUP BY tblRef.[Reference]
PIVOT tblRef.[Type];

Open in new window

0
 
João serras-pereiraAuthor Commented:
Thanks!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.