How to do offseting like this using Access SQL ? Thanks

[mrchonginhk]

How to do offseting like this using Access ? Thanks

Given
Type Month Amount
A 8 100
A 9 200
A 10 300
B 11 -100
B 12 -50

I want B to offset A as follows: Earliest B offset earliest A first
So desired output of query is like this

Type Month Amount
A 9 150
A 10 300
B 11 -100

What is the SQL to do this ? Or using 2 or 3 queries ?
Thanks

 

[Marbles]

Based on your five records, this seems to work. I've named the fields: ID, TheType, TheMonth, TheAmount.

Make two queries based on the table. One (called ForA) with A in the criteria line for TheType, the other (called ForB) with B in the criteria line. In both queries use this field to get the row number:

RowNumA: DCount("[ID]","[ForA]","[ID]<=" & [ID])

in ForA, and

RowNumB: DCount("[ID]","[ForB]","[ID]<=" & [ID])

in ForB. I found this here: https://www.599cd.com/tips/access/140703-row-number/

Next, a new query with both ForA and ForB included with the join taking all records from ForA and those that are equal from ForB. The next problem is the error for no record in ForB. When adding TheAmountA and TheAmountB I had to use this:

ForBAmount: IIf(IsNull([ForB]![TheAmount]),0,[ForB]![TheAmount])

Then this for adding them:

Results: [ForA]![TheAmount]+[ForBAmount]

Seems to get your results, as long as you keep the same sort order from the table. You can hide ForB error in the query to make it look less ugly, and stop error messages popping up if you click in it by mistake.

 

[Marbles]

I forgot to say the join should be RowNumA and RowNumB.

 

[mrchonginhk]

Are these supposed to be invoice amounts and payments (debits and credits) Or something like that? Offhand you might try just using a running total report. Otherwise it's tricky trying to do this without simply summing up to one number. Most databases that handle transactions like these would update the "cumulative total" as part of the process of posting an invoice or posting a payment received. At very least there would be some kind of closing process on a periodic basis. (Edit: I would also expect to have something like an account number in the table, unless it is a very simple set of data).

Yes this is exactly what I am working for. Thanks

 

[xenou]

Why do we have A's and B's in this data? They are clearly not account numbers! What are they?