Weird Query Problem! Plz Help!

[xxrixx]

Ello all,

Firstly, sorry for the long post! Its very hard getting the right advice about this so I wanted to be thorough. Any help will be appreciated!!

I have a problem with a query and just when I thought I was done! Im creating an 'on hand' inventory control system. I have a value for when I receive stock from an order. I have a value for the initial amounts of stock I first enter in a table (Items table) and I have a value for stock that I have sold.

I have put this all into a query so it gives me amount of stock in Real time. However, I have just noticed the following problem:

When I add a product eg 10 PENS, at that moment I havent sold any pens and I havent had a delivery for any pens. So, the values I have are: 10,0,0

When I run the query, the number that comes up is ' ' ie nala, nothing, zip!

Reason for this is because I am performing this calculation in the query:

Items in Stock: Sum(-Newstockpart1.Quantity+Items.ItemStock+NewAcqPart1.Quantity)


My question is, is there a way of making the query return a value in a combination of situations ie:

SITUATION 1
10Pens added originally, 0 from a delivery but ive sold 1.. so the calculation should be [-1+10+0=9]

SITUATION2
10Pens added originally, 10 from a delivery but ive sold 0.. so the calculation is: [-0+10+10=20]

SITUATION3
10Pens added originally, 0 from a delivery and sold 0.. so the calculation is: [-0+10+0=10]


If any access guru out there can help I will be very appreciative!!


Thanks

 

[PHV]

Use LEFT joins for Newstockpart1 and NewAcqPart1.
Then your formula:
Items in Stock: Sum(-Nz(Newstockpart1.Quantity,0)+Items.ItemStock+Nz(NewAcqPart1.Quantity,0))

BTW, are you sure you need the Sum aggregate function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244