Hello all
I have the following SQL statement to fill a datagrid from a mdb:
Code:
SELECT modProducts.Product_Name, SysProductCategories.Product_Category, SysProductSubCategories.Product_SubCategory, modProducts.ProductDescription,
modContacts.Company_Name, modProducts.VendorCode, modProducts.ProductPrice, modProducts.ProductCost, modProducts.ProductProfit, modProducts.StockLimit,
SysMetrics.MetricsValue
FROM ((((modProducts LEFT OUTER JOIN
modContacts ON modProducts.ProductVendor = modContacts.RecordID) LEFT OUTER JOIN
SysProductSubCategories ON modProducts.ProductSubCategory = SysProductSubCategories.RecordID) LEFT OUTER JOIN
SysProductCategories ON modProducts.ProductCategory = SysProductCategories.RecordID) LEFT OUTER JOIN
SysMetrics ON modProducts.MetricUnit = SysMetrics.RecordID)
ORDER BY modProducts.Product_Name
To get the sum column I'm searching for, I have to use this SQL:
Code:
SELECT SUM(modTransaction_Items.Quantity) AS TotalSales
FROM (modProducts LEFT OUTER JOIN
modTransaction_Items ON modProducts.RecordID = modTransaction_Items.Product_ID)
HAVING (modTransaction_Items.InvoiceType = 1)
Another sum is this:
Code:
SELECT SUM(modTransaction_Items.Quantity) AS TotalBuys
FROM (modProducts LEFT OUTER JOIN
modTransaction_Items ON modProducts.RecordID = modTransaction_Items.Product_ID)
HAVING (modTransaction_Items.InvoiceType = 2)
and finally I would like to have the difference between the 2 sums: totalbuys-totalsales
How can all of these have place in one and only statement? How can I fix the first SQL query to include the rest 3 sums?
Thank you.
dBforums
>
Join tables with additional Sum Query
01-16-12, 18:33
Linear Mode
