Query to compare two quantities from different tables

[ManC]

Hi!!
I am stuck with a query.
I have one table with company name, product name, and quantity (This is a list of all products that have been purchased). I have another table which also has all these fields and a few other related fields (This table stores the list of products which have been assigned to someone, in other words, they are being used).I want the user to input the company name and product name.I need to compare total quantity of the product(value passed by user) purchased for the particular company(comes from table1) and how many of the products(user input product) are in use(comes from table 2). I want all this to come from a single query.


Is it possible?
Please let me know if my requirement is not clear.
Thanks.

[jgelpi16]

Ok....so you have Table1.CompanyName, Table1.ProductName, Table1.Quantity and Table2.CompanyName, Table2.ProductName, Table2.Quantity. You want input from the user (Where is the input coming from?). Once you get the input you want to compare Table1.Quantity and Table2.Quantity......
i.e. I input Company1, Product1 and a quantity of 500. You want the query to say "Table1.Quantity is 250 and Table2.Quantity is 250"?

[ManC]

okay let me put it in real simple terms.
I input the product name via a combobox on a form.
I extract the total quantity of product purchased for each company from table 1.
And i extract the total quantity of product InUse for each company from table 2.
Now i want to compare the 2 quantities.Because the number of products purchased might be more than those 'In Use'.
So i want to make a comparison...i want a report which might look like:
QuantityPurchased , QuantityInUse, Difference (group by company name)

Is it possible to get something like this?
Thanks a lot for your help.

[objNoob]

 
SELECT instock.company as Company, instock.productAmount as [On Hand], SUM(inuse.productAmount) as [In Use], instock.productAmount - SUM(inuse.productAmount) as [Difference]
FROM tblPurchase as instock, tblEmployees as inuse
WHERE instock.company = inuse.comany AND instock.productID =  inuse.productID
GROUP BY instock.company, instock.productAmount;

This is the idea....

[ManC]

This is my query:
SELECT tbl_ConsolidatedAddItem.CompanyName, tbl_ConsolidatedAddItem.ProductName, Sum(tbl_ConsolidatedAddItem.ProductQuantity) AS productQuantity, Sum(tbl_ConsolidatedAddItem.LicenseQuantity) AS licenseQuantity, Tbl_Assignment.CompanyName, Tbl_Assignment.ProductName, Sum(Tbl_Assignment.Quantity) AS AssignedQuantity
FROM tbl_ConsolidatedAddItem, Tbl_Assignment
WHERE (((tbl_ConsolidatedAddItem.PurchaseStatus)="Purcha sed") AND ((Tbl_Assignment.UsageStatus)="In Use") AND ((tbl_ConsolidatedAddItem.CompanyName)=[Tbl_Assignment].[CompanyName]) AND ((tbl_ConsolidatedAddItem.ProductName)=[Tbl_Assignment].[ProductName]))
GROUP BY tbl_ConsolidatedAddItem.CompanyName, tbl_ConsolidatedAddItem.ProductName, Tbl_Assignment.CompanyName, Tbl_Assignment.ProductName;

I hope you can make sense out of it.
It displays the correct sum for productquantity and licensequantity but it adds up AssignedQuantity 3 times since there are three entries for this company in 1st table...
where am i going wrong??

[maximus]

I have some questions
1) do you maintain seperate tables for company and products.
2) If yes then do you have a product and company link table.
3) if you don't use this structure that I have mentioned then how do you check that e.g. Product A from Company X is Issued But actually Product A is Bought From Company Y.
4) Are Company in Both the tables refer to the company from which the product is purchased or In product IN table represents the Company from which the product is Purchased and in Product is Company to whom the Product is being sold. (I assume they are the same)

Remember the most important thing about a databes is its table structure if it is sound then its very simple.

[ManC]

I'll tell you what my application does then maybe you can suggest me some changes.
My company usually does purchasing for its sister concerns. So i need to develop an application to record all this info.
There is one table to store which product was bought for which company. Say a router was bought FOR company X. So this table includes fields invoice number, invoice date,companyname(for which the product is purchased),vendor,productname,productquantity,prod description,purchasestatus(could be purchased/not purchased/transferred),unitprice,licensekeylist,licensequant ity,comments....
Now when a product from the above table is used, there is an entry to me made in another table called tbl_Assignment which stores info as to whom was the product(say router) was assigned to.This table includes fields Companyname(which company does the assignee belongs to),assignedto (the assignee),vendor, product name(its a combo box which is filled from the 1st table), productdescription,licensekey,usagestatus,quantity ,comments.
Now it is possible that a company can start using a product without buying it.So i need a report which states the total quantity of a particular product(say, routers) purchased for a company X[comes from the 1st table] ,and the total quantity of routers assigned(or InUse) by company X [comes from the 2nd table]....
please let me know if all this makes no sense ...
I am kinda stuck on this for quite sometime now...
thanks.

[DenverGuy]

Ever find your answer to this? this is my exact problem too

This is my query:
SELECT tbl_ConsolidatedAddItem.CompanyName, tbl_ConsolidatedAddItem.ProductName, Sum(tbl_ConsolidatedAddItem.ProductQuantity) AS productQuantity, Sum(tbl_ConsolidatedAddItem.LicenseQuantity) AS licenseQuantity, Tbl_Assignment.CompanyName, Tbl_Assignment.ProductName, Sum(Tbl_Assignment.Quantity) AS AssignedQuantity
FROM tbl_ConsolidatedAddItem, Tbl_Assignment
WHERE (((tbl_ConsolidatedAddItem.PurchaseStatus)="Purcha sed") AND ((Tbl_Assignment.UsageStatus)="In Use") AND ((tbl_ConsolidatedAddItem.CompanyName)=[Tbl_Assignment].[CompanyName]) AND ((tbl_ConsolidatedAddItem.ProductName)=[Tbl_Assignment].[ProductName]))
GROUP BY tbl_ConsolidatedAddItem.CompanyName, tbl_ConsolidatedAddItem.ProductName, Tbl_Assignment.CompanyName, Tbl_Assignment.ProductName;

I hope you can make sense out of it.
It displays the correct sum for productquantity and licensequantity but it adds up AssignedQuantity 3 times since there are three entries for this company in 1st table...
where am i going wrong??