Stock Query

[Keith Hawes]

Query does not return all stock. In basic form the database has 3 tables. One is “stock” another is “stock in” and the third is “stock out”.
I want to run a query using all three tables so that I can take stock out away from stock in. The problem is the column stock in (in the query result) only shows stock that has a value in stock out. If any stock items have not had stock go out the stock does not appear in the query result. I want stock in to show all items regardless if stock for that item has gone out. I think this can be done but I cannot figure it out.
Sorry if I have not made myself clear.
Please Help. Thank you Keith Hawes.

 

[neileg]

This happens because you are using an inner join to connect your tables. This returns records only where there are matching records in the tables on both sides of the join. There are several ways of tackling this depending on what output you want to achieve. Here's a couple of ideas
1) You need to construct a dataset that contains all of the valid stock IDs. This could be your Stock table, if this contains all possible stock IDs. Then join this to your stock in table using a left join (a type 2 join in Access speak). This will return all of the stock IDs in the stock table and any stock in records that match. Also join the stock out table to the 'all stock ID' record set with a left join. You will then get all stock lines regardless of whether they have stock in or stock out values.
2) My preferred alternative is to create a query for each table that includes the stock ID and the value of the movement and make stock out a negative value. Then you can create a union query that brings all three queries together and sum the transactions. This will give you the stock balance by ID.

 

[Keith Hawes]

Query Fixed

Hi Neil
Thank you for your prompt reply. I did not know what a left join was as I am fairly new to access. I have since looked it up and it works great. I now have one further problem to solve. I have used an expression to take the “out stock” from the “in stock” and it works fine until there is nothing in the corresponding “stock out” field. It seems in access when you subtract a Null value from a number you get a Null result. I can resolve this by inserting a further column and using the Iif function. Or I can insert a zero in all the “Stock Out” fields where no stock has gone out. Is there a simpler solution.
Thank you once again Keith Hawes.

 

[Keith Hawes]

Hi Neil
Thank you for your prompt reply. I did not know what a left join was as I am fairly new to access. I have since looked it up and it works great. I now have one further problem to solve. I have used an expression to take the “out stock” from the “in stock” and it works fine until there is nothing in the corresponding “stock out” field. It seems in access when you subtract a Null value from a number you get a Null result. I can resolve this by inserting a further column and using the Iif function. Or I can insert a zero in all the “Stock Out” fields where no stock has gone out. Is there a simpler solution.
Thank you once again Keith Hawes.

 

[neileg]

You need the Nz() function. This converts a null to something else. By default it turns a null to a zero (hence the name). So you would have Nz([in stock]) - Nz([out stock])

 

[Keith Hawes]

Hi Neil
What can I say "BRILLIANT" It seems with you I get what I want and need. I have been quite a few hours over the last few days trying to solve these problems.
Does this site have a buddie type listing (Ive only just joined). If it does I would like to add you to mine (if you have no objection).
Once again many thanks for your prompt replies

 

[neileg]

Happy to help. I usually take a special look at stock control questions being an accountant first and a programmer second (well programmer about seventeenth, really).

There probably is a buddy system, but I've never been 'buddied' so I don't know what it does!

 

[Keith Hawes]

Thanks Neil
I have found the buddy tab and have added you. Not sure how it works yet but the next time I have a problem I will try contacting you through the site.
Regards Keith Hawes