Subtract Rows in Query Results

[Sengenbe]

Hi,

I have a query which gives me the following information (simplified here):

PropID --- AccountName --- 1Q2010 --- 2Q2010 ---> 4Q2020


1000 --- RevenueAcct A --- #### --- #### --- ####
1000 --- RevenueAcct B --- #### --- #### --- ####
...
1000 --- ExpenseAcct A --- #### --- #### --- ####
1000 --- ExpenseAcct B --- #### --- #### --- ####
...
4929 --- RevenueAcct A --- #### --- #### --- ####
etc.

I want to either create a new query or append this query which will subtract each of the expenses and add each the revenues to get operating income. I have been playing trial and error here but only succeeded in adding another column containing errors, instead of my ideal result which would look like:

PropID --- AccountName --- 1Q2010 --- 2Q2010 ---> 4Q2020
1000 --- OperatingIncome --- #### --- #### --- ####
4929 --- OperatingIncome --- #### --- #### --- ####
1239 --- OperatingIncome --- #### --- #### --- ####
etc.

I need to keep the results to quarterly data separated by PropID. Is there an easy way to do this, or is it difficult in Access to do math between rows without using a function on the entire column like SUM()?

Thanks!

[llkhoutx]

Use multiple queries to stack the data (in a temporary table) with appropriate signs in appropriate columns.

[Sengenbe]

llkhoutx,

Thanks for your reply. Just so I am clear, do you mean that I should have a query for each revenue and expense line which applies the sign to each, then sum the lines in another query?

[llkhoutx]

Misread your post.

Use a Select Query (click the toolbar group by icon, the Sigma), then in the QBE frame Group By row, select sum for each of the Qtrs.

The query, in design view, is in the attached jpg.

[NassauBob]

The way that llkhoutx has instructed will work perfectly, as long as the Expenses are recorded in the table as negative values. That way, when they are added to the revenue values, they will actually be subtracted.

[Sengenbe]

PMs sent to both of you with the query details. I am limited in what I can show publicly and my summary may not convey what I need to.