data from Four table

[sri sri]

i want a stock report from 4 tables
sales table consist of multiple rows of itemcode,itemname,qty,tdate
purchase table consist of multiple rows of itemcode,itemname,qty,tdate
stock table only one row of code and itemcode,itemname ,qty,
salesreturn table itemcode,itemname,qty,tdate
now i want to display data for selected date like below format
************************************************
code,itemname,openingstock,purchaseqty,stock atpresent(Purchase-sales),salesqty,salesreturn,closingstockqty
************************************************
when iam tryin with joins it will returns multiple rows/multiple qty
Thanks in advance

[Rabbit]

Sounds like what you want is an aggregate query. But you haven't given enough information for me to know either way.

[C CSR]

Here's a query to get the data you want. Convert it to an Append Query for a report table with a ReportID corresponding to your list of stock items to separate reports (& create your "date" fields accordingly for all your tables).

This uses 3 tables: sr1Stock, sriSales & sr1Purchase, and their IDs are respectively, Stk_ID, SO_ID & PO_ID.

This uses the "sum" for sales across separate invoices and itemizes by Stock items and their related Purchase Orders.

SELECT sriStock.Item_ID, sriStock.ItemName, sriStock.Qty, sriPurchase.Qty, Sum(sriSales.Qty) AS SumOfQty
FROM sriSales RIGHT JOIN (sriStock LEFT JOIN sriPurchase ON sriStock.Item_ID = sriPurchase.Item_ID) ON sriSales.Item_ID = sriStock.Item_ID
GROUP BY sriStock.Item_ID, sriStock.ItemName, sriStock.Qty, sriPurchase.Qty;
 

I gave you a query, but did not use it to do the append yet. See if this gives you the data you want, and let us know if you need further assistance.

[sri sri]

thanks for reply but i need all sales qty(from sales table),purchase qty from purchase table,sales return qty from sales return table, opening qty from purchase qty-sales qty for selected date ,closing stock purchase -sales -sales return

[Rabbit]

I'll say it again, you don't give us enough information to know what you want.

[C CSR]

Hang in there. I've got a query to do what you want, but I'm reviewing an integrated update process that you'll need to refresh your starting inventory and provide tracking for current transanctions and subtotals as they occur within a given period (if you haven't given up on me). No reply necessary, I need the same configuration for something else that's similar anyway.

[C CSR]

The query below won't make much sense without looking at the structure of the database for this problem and a basic process for conducting business based on your example. You hadn't presented me with much detail so I did the construction myself. Here we go!

Explanation: Upon opening the books, the Inventory Table has to be rejuvenated with the Closing-Inventory from the previous period and preserved as the Starting-Inventory for future period analysis (reports). In real-time, you would be dealing with transaction ids where tables are appended with new records constantly and Inventory is updated. So, for each transaction, a "dynamic inventory" would be updated by {Starting-Inventory + Purchases (PO) - Sales (SO)+ Returns (SR)}, and your Closing Inventory will equal the Dynamic Inventory at the end of any given period; that's where the Inventory is assigned as Starting-Inventory for the next period. By benefit, your Dynamic-Inventory can be queried from the results following the last recorded transaction or otherwise based on the results of a specific closing condition or report (see "Special consideration..." further down). Your function is going to look like this:

New (StartingInventory) = (ClosingInventory) = DynamicInventory = (StartingInventory plus PurchaseOrders plus SalesReturns minus SalesOrders).

For future reference, I may refer to {StartingInventory + PurchaseOrders} as "Opening Available Inventory" (OpenAvl_Inv). The following query will render a snapshot report for a single period using a date (ignoring dynamic inventory for now) based on the table structures that will follow below:

 
SELECT Inv_Tbl.Item_ID, Inv_Tbl.ItemName, Inv_Tbl.Inv_Qty AS StartInv, PODATA.SumOfPO_Qty AS Purchases, nz([Inv_Tbl.Inv_Qty])+nz([PODATA.SumOfPO_Qty]) AS OpenAvl_Inv, SODATA.SumOfSO_Qty AS Sales, SRDATA.SumOfSR_Qty AS Returns, nz([SODATA.SumOfSO_Qty])-nz([SRDATA.SumOfSR_Qty]) AS Net_Sales, (nz([Inv_Tbl.Inv_Qty])+nz([PODATA.SumOfPO_Qty]))-(nz([SODATA.SumOfSO_Qty])-nz([SRDATA.SumOfSR_Qty])) AS Close_Inv FROM 
((Inv_Tbl LEFT JOIN (SELECT PO_Tbl.Item_ID, Sum(PO_Tbl.PO_Qty) AS SumOfPO_Qty, PO_Tbl.OrderDate, Transactions.TransDate FROM PO_Tbl INNER JOIN Transactions ON PO_Tbl.Trans_ID = Transactions.Trans_ID GROUP BY PO_Tbl.Item_ID, PO_Tbl.OrderDate, Transactions.TransDate HAVING (((Transactions.TransDate)=[close date] Or (Transactions.TransDate) Is Null))) AS PODATA ON Inv_Tbl.Item_ID = PODATA.Item_ID) 
LEFT JOIN (SELECT SO_Tbl.Item_ID, Sum(SO_Tbl.SO_Qty) AS SumOfSO_Qty, SO_Tbl.SalesDate, Transactions.TransDate FROM SO_Tbl INNER JOIN Transactions ON SO_Tbl.Trans_ID = Transactions.Trans_ID GROUP BY SO_Tbl.Item_ID, SO_Tbl.SalesDate, Transactions.TransDate HAVING (((Transactions.TransDate)=[close date] Or (Transactions.TransDate) Is Null))) AS SODATA ON Inv_Tbl.Item_ID = SODATA.Item_ID) 
LEFT JOIN (SELECT SR_Tbl.Item_ID, Sum(SR_Tbl.SR_Qty) AS SumOfSR_Qty, SR_Tbl.ReturnDate, Transactions.TransDate FROM SR_Tbl INNER JOIN Transactions ON SR_Tbl.Trans_ID = Transactions.Trans_ID GROUP BY SR_Tbl.Item_ID, SR_Tbl.ReturnDate, Transactions.TransDate HAVING (((Transactions.TransDate)=[close date] Or (Transactions.TransDate) Is Null))) AS SRDATA ON Inv_Tbl.Item_ID = SRDATA.Item_ID 
GROUP BY Inv_Tbl.Item_ID, Inv_Tbl.ItemName, Inv_Tbl.Inv_Qty, PODATA.SumOfPO_Qty, nz([Inv_Tbl.Inv_Qty])+nz([PODATA.SumOfPO_Qty]), SODATA.SumOfSO_Qty, SRDATA.SumOfSR_Qty, nz([SODATA.SumOfSO_Qty])-nz([SRDATA.SumOfSR_Qty]), (nz([Inv_Tbl.Inv_Qty])+nz([PODATA.SumOfPO_Qty]))-(nz([SODATA.SumOfSO_Qty])-nz([SRDATA.SumOfSR_Qty])); 
 

It may look daunting but its really pretty basic. The Outer query uses the data from 3 Inner queries (aliased as PODATA, SODATA and SRDATA) and then performs the calculations for Open "Available" Inventory, Net Sales and Closing Inventory. The query requests an input-date that is the basis for the desired daily demarc, past or present. Your results would be a list of Inventory Items displaying the following stats for each item: Item_ID, ItemName, StartInv, Purchases, OpenAvl_Inv, Sales, Returns, Net_Sales & Close_Inv.

You are going to need a main "Transactions" Table recording the "nature" of each input despite the fact that you have individual tables for separate types of transactions. This facilitates summing multiple entries of one type (POs, SOs or SRs) within the same period and presenting all types together (POs, SOs and SRs) within the same report, and on the same line per each inventory item. Here is an outcome possibility table for a report based on using the Inventory table for starting quantities, and linking it to the other 3 tables, showing the Date of each table's entries in the order of PO, SO, SR:

ItemID #1: null, 1/28/2012, 1/28/2012,
ItemID #2: 1/28/2012, 1/28/2012, 1/28/2012,
ItemID #3: 1/27/2012, 1/28/2012, 1/28/2012.

Without a Transaction table your results may be blended, as in example #3, or, where no transaction occurs on a given date within a particular table (POs, SOs or SRs), joining the Inventory table with the others for a report will force a rejection of any transactions contained in the other tables that do have entries for the requested date. In other words, since you have to maintain dated entries in your PO, SO and SR tables, a problem arises where your results would only include #1 and #2 as you query for dates across all three categories--the categories complying with the date 1/28/2012 in #3 would NOT be considered and the Inventory item would be missing.

The transaction table could be created as a temporary table while producing the report, but that's another exercise for discussion. Here are table definitions to better diagram where the data is being queried from:

Table definitions - (all IDs are numbers, and input Dates must go in and come out in the same format--I use shortdate in this case). There are other specifications to adhere to for input, but are not immediately mentioned here.

Transactions Table [Transactions]: Trans_ID, Item_ID, PO_ID, SO_ID, SR_ID, TransDate.

Inventory Table [Inv_Tbl]: Item_ID, ItemName, StartInv_Qty, OpenDate (and when fully implemented, Dyn_Qty).

Purchase Orders [PO_Tbl]: PO_ID, Item_ID, ItemName, PO_Qty, OrderDate, Trans_ID.

Sales Orders [SO_Tbl]: SO_ID, Item_ID, ItemName, SO_Qty, SalesDate, Trans_ID.

Sales Returns [SR_Tbl]: SR_ID, SO_ID, Item_ID, ItemName, SR_Qty, ReturnDate, Trans_ID (SO_ID is in this table for future reference to original SO).

Special consideration has to placed on the beginning of any given period (or new date) to carry over the previous period's Closing Inventory and prepare for the next, as illustrated in the function I spoke of at the top. We have not gone that far into your process of opening and closing the books, conducting updates necessary to reassign your starting quantities, and computing the dynamic changes in inventory during the given period. Obviously, trying to produce subsequent reports from different dates on the same starting inventory would be misconduct. You have the options of appending to the Inventory table the new date and closing data, or storing past data elsewhere and just updating the current records.

You asked for a report based on dates revealing all transactions, and I'm giving you a solution based on some existing and assumed business practices. You still have to choose (or at least explain to me) how you need to input/update the data per each transaction and how or when in the string of transactions you want to "jiggle" the dynamic and periodic updates to keep your inventory data stable. The answer provided here is a "single query" relying on the precise structure I made available and can be supplemented with other procedures to accommodate the issues that remain. Hope this has all been instructive.