Item Ledgering

[junne]

hey guys need a little help I'm creating inventory item in ms access just like the picture below there is a quantity, amount, total and balance I would want to multiply the quantity and the amount so I can get the total and the balance as well after I get the balance I will put a different amount to subtract in the balance the purpose of that I'm doing this is to monitor the balance. If there is a new item entry is the same way quantity multiply by amount equals total and balance and amount subtract balance again is just like a running subtraction but if there is a new item the current running subtraction will stop and a new entry will do the same way.


and this is the the query

SELECT t.*, (SELECT SUM(IIF(amount IS NOT NULL,amount,0))+ 
                 SUM(IIF(balance IS NOT NULL,-balance,0))
          FROM T t1 
          WHERE t1.ID <= t.ID
         ) AS balance
FROM T;

thanks :)

[NeoPa]

There's so much wrong with this.

Junne:
I'm creating a item entry in ms access

What? What does that even mean? Forget the lack of any punctuation; "a item entry" just doesn't mean anything.

It's good that you've posted your existing SQL. It's not good that you seem to believe that's as good as writing out what you mean in English. The SQL isn't going to work so trying to work out what you mean (in the absence of a real question) doesn't seem possible.

You can probably hear my frustration coming out. We want to help, but you make it so difficult by paying so little attention to what you're asking. Let's see a proper job and, in turn, we'll see what we can do to help.

[PhilOfWalton]

I agree with Neopa. He is brilliant, but unfortunately not Psychic.

Just 2 quick thougts.
What do you want your expected output to look like after 1000 entries?

Why, when you state Qty = 0, do you reduce the stock as if the Qty was 1?

Phil

[NeoPa]

PhilOfWalton:
but unfortunately not Psychic.

Well, some maybe. The point is I shouldn't be required to be to work out what the question is.