INVENTORY BALANCE

[JOSROS]

Hi,

i have an inventory program in access,

i want a report that show balance only for items in stock, but the formula i did is this: =Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage])) but i notice when i print the report that what it gives me is the in and out transcations so for example toilet paper 6 in 3 out so it minus and give 3 balance but it does not add to the previous balance.

how may i get the real balance by products?


thank you

 

[dhookom]

You need to pull the previous balance from somewhere.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JOSROS]

thank you,

how do i do that?

on units on hand in the product form, to get the balance this is the formula: =Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))


thank you

 

[dhookom]

I would expect that you could get a previous balance with a query like:

SELECT ItemID, Sum(nz([UnitsReceived],0)-nz([UnitsShrinkage],0)-nz([UnitsSold],0)) As Balance
FROM tblNoNameGiven
WHERE TransDateField<[Some Date Value]
GROUP BY ItemID;

This is only a WAG since we have very little information about your table structures and data.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]