Query and Pivot Table calculated items

[Sandro Santos]

I have 2 problems in getting a pivot table from query of a table that has the
following type of data:

Table1
Month Category Client amount
1 Cost1 Client1 56
1 Cost1 Client3 1231
2 Cost2 Client4 94
2 Cost1 Client1 456
2 Cost2 Client4 348
1 Sales Client2 1015
2 Sales Client4 657

First, I need to get, for a specified month, two value fields from the
amount field - the actual and the cumulated values:

Pivot table 1
Category TimeRef
| Cost 1 | Cost 2 | Sales
Client | Actual Cumul. | Actual Cumul. | Actual Cumul.
Client1 | 1179 2833 | 414 1230 | 1933 9564
Client2 | 5005 26703 | 2721 13619 | 10614 48350
Client3 | 247 380 | 118 469 | 587 953


And then I need to add to the pivot table 'calculated item type' fields for
the sum of categories (total_costs and profit) :

Pivot table 2
Category TimeRef
| Cost1 | Cost2 | Total_Costs | Sales | Profit
Client | … … | … … | Actual Cumul. | … … | Actual Cumul
Client1 | … … | … … | 1594 4063 | … … | 339 5501
Client2 | … … | … … | 7726 40322 | … … | 2888 8028
Client3 | … … | … … | 365 849 | … … | 222 104


The first question, I managed to solve it by a union query but I this that
there must be a better way of getting this information.
The second one, I tried to solve it in the same way but the query devolved
to many lines for being viable. I’ve verified that the excel pivot tables
have ‘calculated items’ that I could use for this, but I can’t find the same
tool in access.

Can you help me on this?

Thanks