Crosstab query with two column headers (1 Viewer)

[foxtrot123]

I have data like so, in a junction table between tblPatients and tblComplications:

PtID PtCompID  Complication CompDate
1    1         CompABC      1/2/05
1    2         CompXYZ      5/2/05
2    3         CompABC      3/2/10
2    4         Comp123      3/2/10
etc.

I need it restructured like so:

PtID  CompABC  CompABC_Date CompXYZ CompXYZ_Date Comp123 Comp123_Date
1     Yes      1/2/05       Yes     5/2/05      
2     Yes      3/2/10                             Yes     3/2/10

I tried a crosstab, but that allows only one column header (e.g., Column Header "Complication" would return columns for CompABC, CompXYZ, etc.)

Any suggestions on how to also return CompDate as a column header? At some point in the process I can concatenate the two fields using: CompDateColumn: [Complication] & "_" & [CompDate]

Much thanks for any pointers.

 

[JHB]

One way to do it is with help of a Union-query, (call it UnionQuery).
Remember to change the tablename and fieldname to what you call them:

SELECT PtID, PtCompId, Complication,"Yes" AS DataField
FROM Complication;
UNION SELECT PtID, PtCompId, Complication & "_Date",CompDate AS DataField
FROM Complication;

And then use the query as base for the crosstab query:

TRANSFORM First(DataField) AS FirstOfDataField
SELECT PtID
FROM UnionQuery
GROUP BY PtID
PIVOT Complication;

 

[Pat Hartman]

You need two crosstab queries. One to pivot each field. And a third query to join the two crosstabs.

 

[Nicesoni_ash]

You need two crosstab queries. One to pivot each field. And a third query to join the two crosstabs.

How to do that in third query?

Can you elaborate more please. I am trying to do the same with my own two crosstab queries, here is the link. If you could put some insight.

www .access-programmers.co.uk/forums/showthread.php?t=253660

 

[orchbelt00]

Is there any way to accomplish this (see attached png file)? Yes, it would be easier to switch the row and column headers, but that particular view won't accomplish my end users request.

Thanks!

 

[JHB]

Is there any way to accomplish this (see attached png file)?

No - not without a good deal of VBA code!
Another problem you'll get putting a crosstab query in a form is that the amount fields/controls would variate, depending of the data.

 

[stopher]

You can get close quite easily like this

Just create a query to combine (concatenate) the state and city

TRANSFORM Sum(Data) AS SumOfData
SELECT Product
FROM tblData
GROUP BY Product
PIVOT [State] & " " & [City];

 

[Nagesh]

I have data as indicated below ;

and require cross-tab query as per below;

Please help

 

[plog]

That's a bad example because each col heading 1 only has one column heading 2 value. Ergo, you don't really have 2 column headings. A simple crosstab could produce your results--just concatenate the CompID with the Component value and use 1 column heading.