Hi - I'm creating a Excel pivot table from a Query in an Access database. I need it to count the number of products reviewed in a specific week, but the dataset has the review as the primary key:
Review ID Product ID Submission Date Week No. Avg Weekly Overall Rating
12345 2467 01/01/08 1 1/7 4
12346 2468 02/01/08 1 1/7 2
12347 2467 05/01/08 1 1/7 4
My pivot table so far picks up:
Week
1
Count of Review ID 3 = count of reviews that week
Sum of Weekly Average 3/7 = Avg no of daily reviews
Average Overall Review 3 1/3 = Avg review
Count of Product ID 3 = Should be 2, but is 3 as it's counting the number of instances rather than the number of Product IDs.
How do I get it to count the unique product IDs without creating a separate pivot table or standard formula within the worksheet - can it be done with a calculated field or can I add a new field to the query which can be included in the pivot table?
Please help!
Cheers.
Start Free Trial
