purchases - sales problem

[gcreed]

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:

SELECT Warehouse.[Warehouse Code], 
       Warehouse.[Warehouse Description 1], 
       Item.[Item Code], 
       Item.[Item Description 1], 
       Sum([Purchase Item].[Stock Quantity]) + 
           Nz((SELECT SUM(QuantityTransferred) 
               FROM Transfers
               WHERE Transfers.ItemCode = [Item].[Item Code]
               AND Transfers.WarehouseCode = [Warehouse].[Warehouse Code]),0) 
       AS [Stock Quantity], 
       [Purchase Item].[Sum Stock Quantity]
FROM Warehouse 
INNER JOIN ([Purchase Invoice] 
INNER JOIN (Item INNER JOIN [Purchase Item] 
ON Item.[Item Code] = [Purchase Item].[Item Code]) 
ON [Purchase Invoice].[Purchase Number] = [Purchase Item].[Purchase Invoice Code]) 
ON Warehouse.[Warehouse Code] = [Purchase Item].[P Warehouse Code]
GROUP BY Warehouse.[Warehouse Code], 
         Warehouse.[Warehouse Description 1], 
         Item.[Item Code], 
         Item.[Item Description 1], 
         [Purchase Item].[Sum Stock Quantity];

Selling Query:

SELECT Warehouse.[Warehouse Code], 
       Warehouse.[Warehouse Description 1], 
       Item.[Item Code], 
       Item.[Item Description 1], 
       [Sales Item].[Sales Stock Quantity]
FROM Item 
INNER JOIN (Warehouse 
INNER JOIN ([Sales Invoice] 
INNER JOIN [Sales Item] 
ON [Sales Invoice].[Sales Number] = [Sales Item].[Sales Invoice Code]) 
ON Warehouse.[Warehouse Code] = [Sales Item].[S Warehouse Code]) 
ON Item.[Item Code] = [Sales Item].[Item Code]
GROUP BY Warehouse.[Warehouse Code], 
         Warehouse.[Warehouse Description 1], 
         Item.[Item Code], 
         Item.[Item Description 1], 
         [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).

[Luuk]

SELECT
    Item.[Item Code], 
    Item.[Item Description 1], 
    P.PurchasedQuantity - S.SoldQuantity
FROM (purchasesquery) P
OUTER JOIN (salesquery) S ON S.Item.[Item Code] = P.Item.[Item Code]
 

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.

[gcreed]

Luuk please check out the db on the following link

[Luuk]

I do not see a (working) link in your mail.
I do not have MS-Access, so I cannot test anything for you.

[twinnyfo]

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.