Two Crosstab Queries Combined by Sum in to One Query (1 Viewer)

[Malcolm17]

Hey All,

I've created 2 crosstab queries and I need to combine them in to one query to give me correct values
Eg: Table 1 record 1 total is 100.00 and Table 2 record total is -25.00, I need to sum them together to give me a query which shows 75.00.

I am looking at a Union Query to do this, but I am really new to unions, please can you look at this and see if I am on the right lines, what should my query code be please? (I need to sum Total and Group Year then MonthNo) Should it even be a union query?

Thank you,

Malcolm

SELECT
BreakfastPurchasesInvoiced.Year,
BreakfastPurchasesInvoiced.MonthNo,
BreakfastPurchasesInvoiced.Total
FROM BreakfastPurchasesInvoiced
UNION SELECT
BreakfastPurchasesCredit.Year,
BreakfastPurchasesCredit.MonthNo,
BreakfastPurchasesCredit.Total
FROM BreakfastPurchasesCredit
ORDER BY Year, MonthNo;

 

[plog]

A crosstab should be the last query in a series of queries, not an intermediate step. You should UNION before any crosstab. The simple way to do that is uncrosstab BreakfastPurchasedInvoiced and BreakfastPurchastCredit so that they are just rows of data, save them, then build your UNION, then build another query on top of that UNION query to crosstab them.

However, why are these 2 different queries to begin with? Generally Credits and Debits are in the same table, which means they can be in the same query. Do you have similarily structured tables for credits and debits?

 

[Malcolm17]

They come from the same table, records are marked PI and PC but both amounts are minuses, so I have used Abs in the field Amount to make the PI a plus then I was hoping to add them together to get my correct value. (PI = 100.00 and PC = -25.00, the total value that I want is 75.00. I'll have another think as I think your right, I have complicated this too much.

 

[plog]

Sounds like you don't need 2 queries which eliminates the UNION.

 

[Malcolm17]

Oh deer, I've spent all night looking at this and made it far to complicated, I have just done what I need with a simple query!!
Thank you