Creating a report with a crosstab query.

[Dave0291]

Hi there,

A customer has asked me to create a report based on a cross-tab query. The data base schema is attached below.

The query must contain the following

ScrapCollection.regNum,
ScrapCollection.PTEtotal,
ScrapTireType.description (As the Pivot)
ScraptCollectionTireType.amount (as a sum aggregate for each description)

This is the query I have at the moment

PARAMETERS [Forms]![AuditTotal]![start_Date] DateTime, [Forms]![AuditTotal]![end_Date] DateTime;
TRANSFORM Sum(ScrapCollectionTireType.amount) AS SumOfamount
SELECT ScrapCollection.regNum, ScrapCollection.PTEtotal
FROM ScrapCollection INNER JOIN (ScrapTireType INNER JOIN ScrapCollectionTireType ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID
WHERE (((ScrapCollection.date2) Between [Forms]![AuditTotal]![start_Date] And [Forms]![AuditTotal]![end_Date]))
GROUP BY ScrapCollection.regNum, ScrapCollection.PTEtotal
PIVOT ScrapTireType.description;
 

But it is wrong. I only want the data grouped by regNum. But Access will not let me do the following...

PARAMETERS [Forms]![AuditTotal]![start_Date] DateTime, [Forms]![AuditTotal]![end_Date] DateTime;
TRANSFORM Sum(ScrapCollectionTireType.amount) AS SumOfamount
SELECT ScrapCollection.regNum, ScrapCollection.PTEtotal
FROM ScrapCollection INNER JOIN (ScrapTireType INNER JOIN ScrapCollectionTireType ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID
WHERE (((ScrapCollection.date2) Between [Forms]![AuditTotal]![start_Date] And [Forms]![AuditTotal]![end_Date]))
GROUP BY ScrapCollection.regNum
PIVOT ScrapTireType.description;
 

Any help will be greatly appreciated.

Thank you

[Rabbit]

Take out PTETotal. You can't have a non-grouped, non-aggregated field in a pivot.