Inventory Calculations Problems

[storebay]

Hey ya'll,
I did an inventory database in access for someone but it has a problem. It has a products database which contains the products. It has a purchases database which is to record the products bought and a sales database which has the products sold.

Now to get the current inventory one has to take the initial quantity (in the products database), add the amount purchased and subtract the amount sold. To do this I did a calculated query where by I created a query that used the product id and so on from the products table but included the purchased amount from the purchased table and the sold amount from the sales table. The trick is that the query does not work properly. It only lists products that have entries both in the purchase table AND sales table. If entries for the product are missing in one or both of those tables then that product does not show up.

Can anyone please help me sort this out? What I want it to do is list ALL the product regardless of whether they have entries in the products and sales (seeing that they all have an initial quantity anyway and that figures in the calculation). Thanks in advance!

Thomas

 

[Malchik]

You have 3 types (well 4, but 3 most used) of joint

ex:
Tables: Product(Pro_ID, Pro_Desc, Pro_Price)
Purchase(Pur_Pro_ID, Pur_HowMany)

Data for PRODUCT
Pro_ID Pro_Desc Pro_Price
1 A 5.25
2 B 45.69
3 C 456.00

Data for PURCHASE
Pur_Pro_ID Pur_HowMany
1 2
3 10
5 7

1) INNER JOIN (In order to show the linked data MUST be in all tables)

Query:
SELECT Pro_ID, Pro_Desc, Pro_Price,PurHowMany AS Cost,Pur_Pro_ID FROM
Product INNER JOIN Purchase
ON Pro_ID = Pur_Pro_ID

Result:
1 A 5.5 2 1
3 C 456.00 10 3

2) LEFT JOIN (Get all data from the left table even if the link data are not in the right one)

Query:
SELECT Pro_ID, Pro_Desc, Pro_Price,PurHowMany,Pur_Pro_ID AS Cost FROM
Product LEFT JOIN Purchase
ON Pro_ID = Pur_Pro_ID

Result:
1 A 5.5 2 1
2 B 2
3 C 456.00 10 3

3) RIGHT JOIN (Get all data from the right table even if the link data are not in the left one)

Query:
SELECT Pro_ID, Pro_Desc, Pro_Price,PurHowMany,Pur_Pro_ID AS Cost FROM
Product RIGHT JOIN Purchase
ON Pro_ID = Pur_Pro_ID

Result:
1 A 5.5 2 1
3 C 456.00 10 3
7

4) OUTER JOIN (when you want to link tables freom different database)


How it can help! Mal'chik

 

[storebay]

Hey thanks Mal'chik,

The information you gave was extremely helpful! I tried it and when I run the query it displays all the products now. It is still giving problems though. The problem now is that the products that have a sale display the total sold quantity in the total sold calculated query field, and the products that have a purchase record display the quantity in the total bought calculated query field. If a product doesn't have a bought record or sold record then the appropriate calculated fields are blank.

The final calculated field is Total Inventory. This has the syntax:

Total Inventory: ([Initial Quantity]+[Total Bought])-[Total Sold]

But due to the fact (I think) that the fields (Total Bought or Total Sold) may be empty it is displaying no result. So even if a product has a total bought value (all have an initial quantity value) OR a total sold value the Total Inventory field shows blank. How do I fix this?

Thanks again for your help and I hope that you all can help me again.

Thomas

 

[Malchik]

hi,

try to put condition in you calculation:
Total Inventory: (iif(isnull([Initial Quantity]),0,[Initial Quantity])+iif(isnull([Total Bought]),0,[Total Bought])
)-iif(isnull([Total Sold]),0,[Total Sold])


Mal'chik