Query Won't Return Zero Value

[Guest]

Hi,

I have two tables joined in a query to allow product stock to be monitored.
First table lists each stock item, second table lists stock movements against
each stock item. The tables are joined through the stock item number. Idea is
I run the query to have all stock movements against an item summed and the
query returns the current stock for each stock item. Works perfectly as long
as there has been stock movement on an item - if there has been no stock
movement on an item then the query does not return an entry for that item - I
assume because there is no entry in my second table equal to the joined field
in the first table then the join type prevents any record being returned in
the query. What do I need to do to have my query return current stock on
every item whether or not there has been stock movement on that item?

TIA

 

[Guest]

Go to the query design view.

Now there should be a join between your two tables that are being queried.
Shown in the top section of the window. The join is displayed as a thin line
linking the two fields.

What you need to do is right click on this line, click join properties. Then
in the window that pops up you will see that there are 3 radio option
buttons. Option 1 will currently be selected. Select the one that say display
all records from stock items table.

You know if you've selected the wrong one cus would wont beable to see the
query's results, it will display an error. If this happens, repeat the above
steps and choose the other option.

HTH

Rico

 

[Guest]

As rico stated, it is the way your query is constructed. Assuming the Stock
table is on the left and the movement table is on the rigth, then option 2
would be the correct option. In any case you want it to say "Show all
records for Stock table and only records that match from Movement table"
Now, there is one other thing you must do to make this work correctly.
Since there will be Stock items with no matching Movement records, The Stock
number will show, but all other fields for rows with no movement will be
Null. The solution is to wrap the Nz function around your fields. This will
mean field names will change, so you will have to modifiy whatever objects
reference them:
Ins: Nz([MoveTable]![Received],0)
Now you will get 0 instead of Null.