Solved

using ms/access crosstab to obtain a value

Posted on 2015-01-17
2
88 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 119

Accepted Solution

by:
Rey Obrero 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now