Crosstab query problems

[Livid_Enema]

Hi there,

I have seen the great help you guys give to others and wonder could you help me out with a crosstab problem I’m having.

I have a table tbl_clients, with a currency field (either EUR, GBP or USD from a lookup table) and a table tbl_purchases with a currency field (the same currencies as above).



I have a 3x3 crosstab being produced from a 9x3 query (there will be no additional records in this query as it sums the values of client currency compared to order currency). The column headings of the crosstab are EUR, GBP and USD (from a currency table) and the row headings are EUR, GBP, USD (from the same currency table) which looks something like:

Purchases

EUR GBP USD
​

EUR 0 x1 y1
​

Client GBP x2 0 z1

USD y2 z2 0
​

Is there any way I can create a query which adds the values x1 + x2 (EUR-GBP and GBP-EUR), y1 + y2 (EUR-USD + USD-EUR) and z1 + z2 (GBP-USD and USD-GBP) to give me just those 3 values in a new query?

Sorry if this is a little unclear. Edit: Sorry the table above didn't come out very well either

Thanks
Liam