Solved

using ms/access crosstab to obtain a value

Posted on 2015-01-17
2
92 Views
Last Modified: 2015-01-17
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?
0
Comment
Question by:jirdeaid
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40555055
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
 

Author Closing Comment

by:jirdeaid
ID: 40555124
Thanks!!!!!!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question