Crosstab with two fields

[Guest]

Is is possible to create the following report using Part Number for column
headings, code for row headings, and quantity and hours for fields to
summarized. Grazie!


Part#1 Part#2 Part#3 Part#4
Qty Hrs Qty Hrs Qty Hrs Qty Hrs
Code1
Code2

 

[Guest]

You need a totals query that concatenate the sum of QTY and sum of HRS into a
single field.

 

[Guest]

SELECT Tally.Code, Tally.Part, Sum(Tally.QTY) AS SumOfQTY, Sum(Tally.HRS) AS
SumOfHRS, Sum([QTY]) & " - " & Sum([HRS]) AS [QTY-HRS]
FROM Tally
GROUP BY Tally.Code, Tally.Part;

TRANSFORM First(TallyInput.[QTY-HRS]) AS [FirstOfQTY-HRS]
SELECT TallyInput.Code
FROM TallyInput
GROUP BY TallyInput.Code
PIVOT "Part" & [Part];

 

[aaron.kempf]

you guys are crazy i think

you need TWO crosstabs and then you join them together

 

[Guest]

How do you join two crosstab queries?

Did you try my solution before replying?

 

[aaron.kempf]

uh you can join them together if they have common keys and the same
granularity.