database - Simple inventory SQL query not listing all products MS-ACCESS -
so have simple select query: design view of query
select tblproducts.product_id, sum(tblintakes.intake_qty)-sum(tblexits.exit_qty) stock (tblproducts inner join tblexits on tblproducts.product_id = tblexits.product_id) inner join tblintakes on tblproducts.product_id = tblintakes.product_id group tblproducts.product_id;
it doesn't list products. list products had intake , exit. isn't useful since know stock level of products.
this query:
| product_id | stock | | 1 | 4 |
this wan't get
| product_id | stock | | 1 | 4 | | 2 | 10 | | 3 | 0 |
this shouldn't complicated i'm new access , sql , it's giving me headache. appreciated
these records:
products
| product_id | product_name | | 1 | pencil | | 2 | book | | 3 | marker |
intakes
| intake_id | intake_date | product_id | intake_qty | | 1 | 20/07/2017 | 1 | 10 | | 2 | 20/07/2017 | 2 | 10 |
exits
| exit_id | exit_date | product_id | exit_qty | | 1 | 21/07/2017 | 1 | 6 |
you looking left join
, nz()
select tblproducts.product_id, nz(sum(tblintakes.intake_qty), 0) - nz(sum(tblexits.exit_qty)) stock (tblproducts left join tblexits on tblproducts.product_id = tblexits.product_id ) left join tblintakes on tblproducts.product_id = tblintakes.product_id group tblproducts.product_id;
Comments
Post a Comment