Crosstab Help 1

[niteraven]

Hello

I am trying to write a crosstab query to sum the total warranty parts used [partsnum], qualifying it by product[ikusi]. I have not been able to get the part numbers to total correctly.

Here is the sql:

TRANSFORM Sum(tblparts1.partqty) AS SumOfpartqty
SELECT tblparts1.partsnum
FROM tblrma INNER JOIN tblparts1 ON tblrma.rmanum = tblparts1.rmanum
WHERE (((tblrma.producttype)="ikusi") AND ((tblparts1.parttype)="warranty") AND ((tblrma.repairdate) Between #1/1/2008# And #12/10/2008#))
GROUP BY tblrma.producttype, tblparts1.partsnum, tblrma.repairdate
PIVOT tblparts1.parttype;

rmanum is the primary key for tblrma

I think it has something to do with the primary key, but i cannot figure it out.

Any help would be greatly appreciated
Raven

 

[PHV]

Replace this:
GROUP BY tblrma.producttype, tblparts1.partsnum, tblrma.repairdate
With this ?
GROUP BY tblparts1.partsnum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[niteraven]

THanks PHV

That worked like a champ!

THank you
Raven