Subtracting One Sum Total From Another

[ribena1980]

Hi,



The following are fields in a table I have

TransactionMonth
Section
Amount

What I am looking to do is create a query or report that will subtract the total for Section A from Section B and group it by month.

It sounds straightforward enough but there is something that I am missing.

Has anyone got any ideas?

[CJ_London]

is there is something that I am missing.

well, section is a reserved word so may be causing a problem, but what is missing is the sql that you are using at the moment in your query and some sample data and the result you want, otherwise we are just guessing

[Bob Fitz]

There is probably a more elegant way to do it with sub queries but I think this works:

Crete aa query called qryTotalA with the following SQL:

TRANSFORM Sum(YourTable.Amount) AS SumOfAmountSELECT YourTable.TransactionMonth
FROM YourTable
WHERE (((YourTable.Section)="a"))
GROUP BY YourTable.TransactionMonth
PIVOT YourTable.Section;

Crete aa query called qryTotalB with the following SQL:

TRANSFORM Sum(YourTable.Amount) AS SumOfAmountSELECT YourTable.TransactionMonth
FROM YourTable
WHERE (((YourTable.Section)="b"))
GROUP BY YourTable.TransactionMonth
PIVOT YourTable.Section;

Create a query with the following SQL which will give the required results:

SELECT qryTotalA.TransactionMonth, qryTotalA.a, qryTotalB.b, [a]-[b] AS ResultFROM qryTotalB INNER JOIN qryTotalA ON qryTotalB.TransactionMonth = qryTotalA.TransactionMonth;

[Bob Fitz]

Forgot to mention that you would need to substitute "YourTable" in the first and second query with the actual name of your table.

[ribena1980]

Hi Bob,

Many Thanks for that. I think it seems to be doing what I want on the 2 sub queries.

For the query to link them up, can you just explain the
qryTotalA.a, qryTotalB.b, [a]-[b] bit

as I understand that qryTotalA and qrtTotalB relate to the the name of the actual queries, but what do a and b relate to?

Thanks,
Mark

[Bob Fitz]

Hi Bob,

Many Thanks for that. I think it seems to be doing what I want on the 2 sub queries.

For the query to link them up, can you just explain the
qryTotalA.a, qryTotalB.b, [a]-[b] bit

as I understand that qryTotalA and qrtTotalB relate to the the name of the actual queries, but what do a and b relate to?

Thanks,
Mark

Hi Mark,
It may help to look at the third query in design view.
This query joins the other two using the month field. [a] and [b] are the fields from the other two queries which are shown in the third query and also used in the calculated field (called Result) [a]-[b].