Inter–rows relationship in a query

[ariansman]

A query/table shows our monthly financial status. There are expenses and incomes fields. I want to make a new field named:”sumup”. I want this field to be the result of current month incomes minus expenses plus “sumup” from the previous month record.
For example on June we had 3000 income and 2000 expenses. We had 3500 left from May (shown on “sumup” field in May record) . So the “sumup” record for June will be 4500. It look like a bank count turnover report.
In other words, I want to know how can we make a query to use data from previous records on the same table/query.

 

[Galaxiom]

Use a self join. (Drag the table in twice to create an alias.)
The join based on one month difference can be entered directly into the SQL but not the design view.

Otherwise if you are more comfortable with the designer, use a query to derive a fields with values that will be the same on the records you want to connect then join on those fields in selfjoined query besed on those fields.

 

[VilaRestal]

Without knowing the names of your table and fields, something like this:

SELECT Table1.*, Table1.Incomes - Table1.Expenses + (SELECT SUM(T1.Incomes - T1.Expenses) FROM Table1 AS T1 WHERE T1.iYear < Table1.iYear OR (T1.iYear = Table1.iYear AND T1.iMonth < Table1.iMonth)) AS sumup FROM Table1

or

SELECT Table1.*, (SELECT SUM(T1.Incomes - T1.Expenses) FROM Table1 AS T1 WHERE T1.ID <= Table1.ID) AS sumup FROM Table1

if you can be sure ID is in sequence.

It could get slow to run it with lots of records (and the more that are added the slower it will get).

 

[ariansman]

Otherwise if you are more comfortable with the designer, use a query to derive a fields with values that will be the same on the records you want to connect then join on those fields in selfjoined query besed on those fields.

thank you,
is there a simpler explanation for the second paragraph?