Pivot Table Calclulated Fields Help

[cdogstu99]

I have a pivot table, which has a filter by company which displays by week a list of customers. Yet it is separated into two parts of my company...such as follows: (week #, Company, # Customers)

1 Association 4
1 Company 13
2 Association 3
2 Company 17
3 Association 1
3 Company 12

I want it to display by week, but with only one row, so that
it looks like this:

Week Company Association
1 13 4
2 17 3
3 12 1

Total 42 8

Would I need to create calculated fields on the pivot table? Maybe someone can give me a hint on the easiest way to do this? Thank you!

 

[dhookom]

It isn't clear if the first records above are a table or some sort of query. Assuming they are tblPivot, try this SQL:
TRANSFORM Sum(tblPivot.NumOfCustomers) AS SumOfNumOfCustomers
SELECT tblPivot.WeekNum
FROM tblPivot
GROUP BY tblPivot.WeekNum
PIVOT tblPivot.CompanyNum;

Use totals in a report footer to get the last required line of totals.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]