I am making my own Supplier Report by querying MSDE. How do you query where each supplier shows the quantity sold and quantity in stock? I am joining TransactoinEntry, Item, and Supplier tables and I can get the quantity sold from TransactionEntry table, but the quantity (in stock) in Item table do not seem to be right when I query both sold and in stock quantities in one statement.
I am trying to make a simple Sales, Cost, and Profit query in one statement that has monthly column. How do I do that without creating one statement for each month?
Take a look at the Detailed Sales Report. It contains all of the linkage between tables. You can open it with Notepad or any text editor. Make a copy of it and use the copy, if you decide to play. It may also do the report that you are trying to create. Run the report, right click on the body of the report, go to Show/Hide columns and check the Supplier. If you want to see the report by Supplier, drag the Supplier column to the left of the report, making it the first column.
I am outputting query results via MS Query to Excel. One worksheet currently has two queries: One for transactioned numbers and another for inventory numbers.
For the transactioned report I use the following query.
SELECT Supplier.SupplierName AS 'Supplier', Sum(Transactionentry.Quantity*Transactionentry.Price) AS 'Sales', Sum(TransactionEntry.Cost) AS 'Cost', Sum(Transactionentry.Quantity*Transactionentry.Price-Transactionentry.COST) AS 'Profit', Sum(Transactionentry.Quantity*Transactionentry.Price-Transactionentry.COST)/Sum(Transactionentry.Quantity*Transactionentry.Price) AS 'Margin%', Supplier.LastUpdated AS 'OnFloor', Sum(TransactionEntry.Quantity) AS 'QtySold'
FROM NB_rep.dbo.Item Item, NB_rep.dbo.Supplier Supplier, NB_rep.dbo.TransactionEntry TransactionEntry WHERE Supplier.ID = Item.SupplierID AND Item.ID = TransactionEntry.ItemID GROUP BY Supplier.SupplierName, Supplier.LastUpdated HAVING (Supplier.SupplierName Like 'LQ%') OR (Supplier.SupplierName Like 'LR%') OR (Supplier.SupplierName Like 'LS%') ORDER BY Supplier.LastUpdated
Then for the inventory info, I use: SELECT Sum(Item.Quantity) AS 'InvQty', Sum(Item.Quantity*Item.Cost) AS 'InvValue', Supplier.SupplierName AS 'Supplier', Supplier.LastUpdated AS 'Created On' FROM NRFB_REP.dbo.Item Item, NRFB_REP.dbo.Supplier Supplier WHERE Item.SupplierID = Supplier.ID GROUP BY Supplier.SupplierName, Supplier.LastUpdated HAVING (Supplier.SupplierName Like 'LQ%') OR (Supplier.SupplierName Like 'LR%') OR (Supplier.SupplierName Like 'LS%') ORDER BY Supplier.LastUpdated
If I put the OnHand Item Quantity (Item.Quantity)in the first query (transaction), quantities are duplicated because transactionentry table shows the same item info in every transaction so it would look like:
Aggregate of QtySold is '2' and that's fine but aggregate of QtyOnHand should be '5' not '10'.
You can see the same problem in the Detailed Sales Report in RMS Manager.
So my question is how do you combine these two queries?
My 2nd question is how do you query so that you can output monthly breakdown of Sales/Cost/Profit to Excel? I don't want to use Quickbooks for reports. It would look like: Jan Feb Mar Apr...
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.