Query

[PMJ]

Hi All,
Here is my problem:
I have a database with orders, order details and tblproduction. What i want
to do is the following:
Get a query to collect information from order details sorted by productID
and also information from the tblproduction table by productid and use the
end results into a form. What i want to do is get information from the order
details for sold quantities, quantities from tblproduction as far as
produced quantity and get the closing stock. Am i getting confused in the
whole issue or is there a simple way to tackle the problem.
Please help, my boss is putting lots of pressure on me.
Thanks to all
JM

 

[Guest]

I take you have also a table for products where you reference your ptoductid,
right?, if so you can create a query based on your products table, outer join
it to your order details and tblproduction, make the query a summary and sum
the quantities from order details and tblproduction, your sql statement will
look like this:

SELECT Part_Catalog_tab.Product_Id, Sum(Production_tab.Quantity) AS
Prod_Qty, Sum(Order_Detail_tab.Quantity) AS Order_Qty
FROM (Part_Catalog_tab LEFT JOIN Order_Detail_tab ON
Part_Catalog_tab.Product_Id = Order_Detail_tab.Product_Id) LEFT JOIN
Production_tab ON Part_Catalog_tab.Product_Id = Production_tab.Product_Id
GROUP BY Part_Catalog_tab.Product_Id;

You can highlight this statement and copy it to a new query in your database
in the SQL view, then replace the table names and fields with yours, save the
query, then create a form and make the record source the recently saved query,