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

 

[Micron]

I might have a hunch, but I'm skeptical because I don't get what you're saying you have or need.
Earliest B is B 11 -100. How can/does B "offset" A? In the example of desired results you gave, there isn't even a 150 value for anything above??

 

[mrchonginhk]

Sorry you are right. I did not make it very clear.
I added ID and month name now:.

Given I have this

ID Type Month Amount
1 A Jan 100
2 A Feb 200
3 A Mar 300

4 B May -100
5 B June -50

I want earliest Type B item to offset earliest item in Type A
So ID 4 offset ID 1 in total. Then
ID 5 offset part of ID 2 leaving 150 net behind. ID 3 has nothing to offset so is intact.

So desired result is:-
ID Type Month Amount
2 A Feb 150
3 A Mar 300

Thanks a lot

 

[Micron]

Well I still don't get it. First, Offset is an Excel function that produces a move left or right and/or up or down from the current cell. AFAIK, there is no Access equivalent, nor is there an Offset function. With that in mind I tried to arrive at the same conclusion as you show for a result, but cannot. I just don't see how anything in A can "offset" anything in B or vice versa.
The earliest type b row holds ID:4; Type:B; Month:May; Amount:-100 (negative 100). There is NO Item so I have no clue as to what you are referring to when you say item, much less how to "offset".
The best I can do is point you to what I had in mind, but I can't begin to understand how you'd use it. Perhaps you have to come up with a better term than 'offset' to describe what you want to do. Nor do I see how you get 150, even if I add -100 and -50, it does not equal 150. Here's the link:
Microsoft Access tips: Subquery basics

 

[james_lankford]

this is what he means

"I want earliest Type B item to [be connected to the] earliest item in Type A, and then I wnat to add the amounts together "

ID Type Month Amount
1 A Jan 100
2 A Feb 200
3 A Mar 300

4 B May -100
5 B June -50

so 4 (May)is linked to 1 (Jan)
now add the amounts in 1 and 4 together and you get zero --- they "offset" each other

now look at the next earliest item for A and B

so 5 (June) is linked to 2 (Feb)
now add the amounts in 5 and 2 together and you get 150 --- they "offset" each other, but not completely

now look at the next earliest item for A and B

there isn't one for B, but for A it would be #3 (Mar)
and since there is no B to add the amount to the amount stays the same (300)

there is probably way to do this, but I don't know what it is
if there is I'd be interested in seeing it just for learning

 

[Micron]

Thanks. Not sure what prevents me from arriving at the conclusion you did, but I've been told that I'm somewhat 'black and white' in my approach or analysis of things. OK, so the term was not actually "somewhat". Sometimes it's a blessing...
Anyway, this really looks like we're looking for a way to put lipstick on a pig as the saying goes, because normalization went out the window (assuming it was in the room in the first place). What comes to mind is this:
A 'master' query could call an outer aggregate query (which uses FIRST as the aggregate function) to get the first B record. An aggregate subquery of that query could get the FIRST A record, and the outer query could add the two values together, using an IIF statement to deal with records that have no pairs. This query would also have to set a flag so that on the next pass, it ignores the FIRST record it finds WHERE the flag is True. This record would be passed to the 'master' query. However, I'm pretty sure the query/subquery combination would only return one record (a message to that effect would probably pop up upon each execution of the main query) so that might blow up the whole idea.

My focus would first be on attempting to somehow normalize the data. If not possible, the only option that the OP might have a chance of creating and maintaining a solution (a HUGE consideration is maintaining this) would be to create a form that lists A records in one listbox and B records in another. The user selects one from each and clicks the button to pair these records. The code could do the math at the same time while this "concatenated" record is written to a table. However, neither solution is something that I would be willing to create as a volunteer (sorry).

BTW, did I ever mention that my wife says I always look at things while wearing my 'complicated' glasses? Hopefully, someone has a far simpler solution that I tend not to see.

 

[james_lankford]

.. However, neither solution is something that I would be willing to create as a volunteer..

same here
I'd be interested in seeing how its done
but I'm not going to spend time trying to figure it out

 

[mrchonginhk]

Thanks all. I will try to do it myself and post it here if I success. Right now I can think making first query by summing all amounts excluding the Type first to find the desired net amount (ie 450 in my reply #3). What I am having hard time is to list out the 2 items in the Answers in reply #3. I will keep trying... but if somebody can make it, would appreciate it to share here.

Thanks

 

[mrchonginhk]

The direction I am thinking now is to add a new column holding an accumulative value for each item for Type A,
and so long it is less than 150 (100+50 of Type B) then have to appear.

ie

ID Type Month Amount AccumulativeAmt
1 A Jan 100 100
2 A Feb 200 300
3 A Mar 300 600

My hard time is how to generate this AccumulativeAmt Column ? If success,
Item 1 should be displayed because 100 is less than 150.
Then come to item 2. How to make it 200-50 = 150

But I cannot generate the net 50 in the answer in reply #3.

 

[xenou]

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).