In and out qty

[roystonteo via AccessMonster.com]

Dear All,

Please advise me if this is wrong.
I have a table.
PartNo, Qty, Movement

The movement has IN which has a positive number.
The movement which is out has a negative number.

If i were to get the balance quantity left, i should use innerjoin.

What do i do, for the total quantity for IN

 

[John W. Vinson]

Dear All,

Please advise me if this is wrong.
I have a table.
PartNo, Qty, Movement

The movement has IN which has a positive number.
The movement which is out has a negative number.

If i were to get the balance quantity left, i should use innerjoin.

What do i do, for the total quantity for IN

I'm not sure I understand. What is qty? What is Movement? You say it's a
positive or negative number: does a qty of 100 and a Movement of +1 mean a
movement in of 100? Or if a Movement of +100 means a movement in of 100, what
is qty?

To sum all of the qty values for which Movement is positive, just do a Totals
query with a Where totals operator on Movement and a criterion of >0, and sum
Qty; it's not clear whether you want to Group By PartNo or not.

 

[Dale Fye]

Row,

I assume this is for some sort of inventory management database. To get the
current balance, assuming that you don't have another table that has the
starting balance as of a particular date, and assuming that the number in
your [Movement] column is either 1 or -1, then your query would look
something like:

SELECT PartNo, Sum([Qty] * [Movement]) as Balance
FROM yourTable
GROUP BY PartNo

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[roystonteo via AccessMonster.com]

Hi Fye,

I got it, thanks

Row,

I assume this is for some sort of inventory management database. To get the
current balance, assuming that you don't have another table that has the
starting balance as of a particular date, and assuming that the number in
your [Movement] column is either 1 or -1, then your query would look
something like:

SELECT PartNo, Sum([Qty] * [Movement]) as Balance
FROM yourTable
GROUP BY PartNo

Dear All,

[quoted text clipped - 8 lines]

What do i do, for the total quantity for IN