By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Creating a report with a crosstab query.

P: 33
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

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![AuditTotal]![start_Date] DateTime, [Forms]![AuditTotal]![end_Date] DateTime;
  2. TRANSFORM Sum(ScrapCollectionTireType.amount) AS SumOfamount
  3. SELECT ScrapCollection.regNum, ScrapCollection.PTEtotal
  4. FROM ScrapCollection INNER JOIN (ScrapTireType INNER JOIN ScrapCollectionTireType ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID
  5. WHERE (((ScrapCollection.date2) Between [Forms]![AuditTotal]![start_Date] And [Forms]![AuditTotal]![end_Date]))
  6. GROUP BY ScrapCollection.regNum, ScrapCollection.PTEtotal
  7. PIVOT ScrapTireType.description;
  8.  
But it is wrong. I only want the data grouped by regNum. But Access will not let me do the following...

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![AuditTotal]![start_Date] DateTime, [Forms]![AuditTotal]![end_Date] DateTime;
  2. TRANSFORM Sum(ScrapCollectionTireType.amount) AS SumOfamount
  3. SELECT ScrapCollection.regNum, ScrapCollection.PTEtotal
  4. FROM ScrapCollection INNER JOIN (ScrapTireType INNER JOIN ScrapCollectionTireType ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID
  5. WHERE (((ScrapCollection.date2) Between [Forms]![AuditTotal]![start_Date] And [Forms]![AuditTotal]![end_Date]))
  6. GROUP BY ScrapCollection.regNum
  7. PIVOT ScrapTireType.description;
  8.  

Any help will be greatly appreciated.

Thank you
Attached Images
File Type: jpg Relationships.jpg (31.2 KB, 108 views)
Jul 11 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,355
Take out PTETotal. You can't have a non-grouped, non-aggregated field in a pivot.
Jul 12 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.