Crosstab with two fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
You need a totals query that concatenate the sum of QTY and sum of HRS into a
single field.
 
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];
 
Back
Top