I have a table of transactions that shows the amounts of stock that are moving between depots. I have 2 queries set up, one that returns all of the stock that goes to a particular depot, and one that returns all stock that has gone from the depot. My thrid query takes one from the other to return an actual qty in stock.

My Problem: If there is no record 'From' the location, then the third query will not display the amount in stock, which logically is all the record To the location.

Any ideas of a way round this, I don't want to have to put in a record for each product that has '0' in it.

Help please!

Make the join a left join between stock TO location and stock FROM location (ie black arrow from the TO LOCATION to the FROM LOCATION tables)

four fields in query. Item name, Number to location, Number from Loc, remain (Number to - number from)

The Number from Loc should be OutG: Iif(IsNull([FROM LOC]),0,([FROM LOC])). This is so that if nothing has left the location it will return 0 to enable the fourth field to carry out the calculation.

HTH

PS Please don't use From and TO as fields as it makes for a very screwed up reply!!