423,682 Members | 1,348 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Item Ledgering

P: 10
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
Expand|Select|Wrap|Line Numbers
  1. SELECT t.*, (SELECT SUM(IIF(amount IS NOT NULL,amount,0))+ 
  2.                  SUM(IIF(balance IS NOT NULL,-balance,0))
  3.           FROM T t1 
  4.           WHERE t1.ID <= t.ID
  5.          ) AS balance
  6. FROM T;
thanks :)

Attached Images
File Type: jpg expected output.jpg (40.8 KB, 159 views)
Jul 13 '18 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,084
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.
Jul 13 '18 #2

PhilOfWalton
Expert 100+
P: 1,373
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
Jul 13 '18 #3

NeoPa
Expert Mod 15k+
P: 31,084
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.
Jul 13 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.