423,680 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

purchases - sales problem

P: 2
Hello,

I am working on stock control DB. I have already reached multiple warehouses creation; purchasing; transfer between warehouses.

my problem now is selling item stock deduction. I will show here the query im using

Purchase Query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Warehouse.[Warehouse Code], 
  2.        Warehouse.[Warehouse Description 1], 
  3.        Item.[Item Code], 
  4.        Item.[Item Description 1], 
  5.        Sum([Purchase Item].[Stock Quantity]) + 
  6.            Nz((SELECT SUM(QuantityTransferred) 
  7.                FROM Transfers
  8.                WHERE Transfers.ItemCode = [Item].[Item Code]
  9.                AND Transfers.WarehouseCode = [Warehouse].[Warehouse Code]),0) 
  10.        AS [Stock Quantity], 
  11.        [Purchase Item].[Sum Stock Quantity]
  12. FROM Warehouse 
  13. INNER JOIN ([Purchase Invoice] 
  14. INNER JOIN (Item INNER JOIN [Purchase Item] 
  15. ON Item.[Item Code] = [Purchase Item].[Item Code]) 
  16. ON [Purchase Invoice].[Purchase Number] = [Purchase Item].[Purchase Invoice Code]) 
  17. ON Warehouse.[Warehouse Code] = [Purchase Item].[P Warehouse Code]
  18. GROUP BY Warehouse.[Warehouse Code], 
  19.          Warehouse.[Warehouse Description 1], 
  20.          Item.[Item Code], 
  21.          Item.[Item Description 1], 
  22.          [Purchase Item].[Sum Stock Quantity];
Selling Query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Warehouse.[Warehouse Code], 
  2.        Warehouse.[Warehouse Description 1], 
  3.        Item.[Item Code], 
  4.        Item.[Item Description 1], 
  5.        [Sales Item].[Sales Stock Quantity]
  6. FROM Item 
  7. INNER JOIN (Warehouse 
  8. INNER JOIN ([Sales Invoice] 
  9. INNER JOIN [Sales Item] 
  10. ON [Sales Invoice].[Sales Number] = [Sales Item].[Sales Invoice Code]) 
  11. ON Warehouse.[Warehouse Code] = [Sales Item].[S Warehouse Code]) 
  12. ON Item.[Item Code] = [Sales Item].[Item Code]
  13. GROUP BY Warehouse.[Warehouse Code], 
  14.          Warehouse.[Warehouse Description 1], 
  15.          Item.[Item Code], 
  16.          Item.[Item Description 1], 
  17.          [Sales Item].[Sales Stock Quantity];
The problem is in deduction

I just want Purchases-Sales

please take a look at my db in the following link
(Link removed - please read posting instructions carefully).
2 Weeks Ago #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 931
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     Item.[Item Code], 
  3.     Item.[Item Description 1], 
  4.     P.PurchasedQuantity - S.SoldQuantity
  5. FROM (purchasesquery) P
  6. OUTER JOIN (salesquery) S ON S.Item.[Item Code] = P.Item.[Item Code]
  7.  
For the fields 'PurchasedQuantity' and 'SoldQuantity' you can/should add aliases to the queries.

In place of 'purchasesquery' you could have the name of a view, which is defined as your query which returns purchases.
2 Weeks Ago #2

P: 2
Luuk please check out the db on the following link
2 Weeks Ago #3

Expert 100+
P: 931
I do not see a (working) link in your mail.
I do not have MS-Access, so I cannot test anything for you.
2 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 2,666
gcreed,

Your DB link has been removed, as it violated site policies. Posters should not simply post their DB to our forum unless specifically asked by a site moderator/expert.

Additionally, you have not offered any true effort in resolving this issue yourself--merely asking others to do the heavy-lifting for you. This is typically not how this forum works. If you would like specific help on a query or data manipulation problem that does not work, we ask you to post the work you have done so far and explain what about it that does not work.

Or, we can offer assistance by providing direction for how you might approach a particular challenge. In your case, what you are seeking to do is find out how many items were purchased and deduct that from the table that holds your current inventory. In theory, that is the solution, but I realize that you want to know how to do that. Most of us will not do this work for you, as we all have jobs that pay the bills, and Bytes is not one of those income producing activities.

I hope you understand.
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.