Query problem

[scottappleford]

I would like to do an in stock query on the database.

I have 109 products, however only 91 have had transactions against them in the transactions table..

I would like the query to report the 91 records stock take and show the rest as zero stock. this table as # ordered, # received, # breakages

I have another table for stock control which reports stock going in and out.

Now on the product form i have the transaction table and stock control table as sub forms and it reports the correct stock through calculated fields.

However with the query becasue i only have stock control 15 products that have been booked in and out, when i inculde these in the query it reduces even further.

I am hoping that my underlying table structure is correct and i am just setting up the query incorrectly.

Any advice would be great.

Thanks

Scott

 

[caz]

Have you tried changing the joins in to the tables in the query?

 

[neileg]

As caz says, you need to change the join. Access defaults to an inner join which returns only the records that match in both tables. You need the join that returns all of the records that appear in the table where you hold your products and the records from the transaction table that match.

 

[scottappleford]

Thanks - did not know you could do that.

Thanks again!!

scott