Calculating Sum in Query

[chriscook]

Hi All,

I have been working on a stock database for my company. It needs to hold stock data, Any adjustments in the storesperson may do (QtyIn or Out). And allow Customer PO's to be raised (just to show prices and quantites sold to customers)

I have 4 Tables:

tblStockList
PartNumber (Primary Key)
Description
Location
StockCheck
StockListUnitPrice
PriceUpdate

tblAdjustments
AdjustmentID
PartNumber
DateAdj
QtyIn
AdjPONO
QtyOut
AdjContractNo
QtyTotal (Calculated from QtyIn-QtyOut)
Notes
AdjPerson

tblCustomerPO
POID
PONo
CustomerName
Person
RigNo
PADNote
ComInv
POComplete (Yes/No)
CurrencyPO
DatePO
ContractNoPO

tblCustPoPN (subform for customer PO)
PoSubID
PartNumber
Quantity
Supplier
SupplierPoNo
ContractNo
Shipped (Yes/No)
Currency
PricePerUnit
TotalPrice (Calculated Quantity x PricePerUnit)
ShippedDate

I then want to create a query to work out the total stock.

At the moment i have two queries, one to Sum the adjustments and one to sum the PO's. (The stock is not removed from the Customer PO until invidual line is shipped)

I then need to combine both these queries to work out the total stock value.

There will always be a value in the Sum of Adjustments but not in the Customer PO. So when i run the query to =SumofAdjustments - SumofCustomerPO's, if there is no record in the SumOfCustomerPo's, no value will appear.

Is there any way i can solve this?

Sorry if i haven't explained it very well. :banghead:

I can attach my current database if that helps?

 

[Minty]

If the query to calculate the sum of customer PO's is correctly constructed you should get a 0 value which will mean there is a valid calculation.

I also think you should probably only have a transaction qty and then a field to indicate if it's stock or out, rather than seperate in / out qtys per line, it doesn't scale as well.

I assume you have also not stored the totals in the table as indicated? it is redundant as you have the data stored to make the calculation.

 

[chriscook]

https://www.dropbox.com/s/pltbijzx7ozneh9/Stock Database 3.accdb?dl=0

See above link to the database.

The sum of customer PO's value does not appear as 0 if there is no record for that part number.

For example, if the storesperson creates a new part number, then adjusts a quantity in.

There is no record of this part number in the customer PO table, so when i do a sum of Customer PO's, it is not referenced at all.

 

[Minty]

Okay It's little clearer now.

Your stock balance should not really include a reference to the customers orders. It should only be what's in and out in the one transaction (Adjustments) table.

Your order table is exactly that for recording orders; you should book stock out from your adjustments to the order once it's fulfilled.

You can adjust your query to be an outer join however and that would solve your immediate problem.

SELECT qrySumAdjustments.PartNumber, tblStockList.Description, [SumOfAdjustmentsQtyTotal]-(IIf([Expr1]>0,[Expr1],0)) AS TotalStock, tblStockList.Location, tblStockList.StockCheck, tblStockList.StockListUnitPrice, tblStockList.PriceUpdate
FROM (qrySumAdjustments LEFT JOIN qrySumPO ON qrySumAdjustments.PartNumber = qrySumPO.PartNumber) INNER JOIN tblStockList ON qrySumAdjustments.PartNumber = tblStockList.PartNumber
GROUP BY qrySumAdjustments.PartNumber, tblStockList.Description, [SumOfAdjustmentsQtyTotal]-(IIf([Expr1]>0,[Expr1],0)), tblStockList.Location, tblStockList.StockCheck, tblStockList.StockListUnitPrice, tblStockList.PriceUpdate
HAVING (((qrySumAdjustments.PartNumber)=[Enter Part Number]))
ORDER BY qrySumAdjustments.PartNumber;

 

[Minty]

Having had a longer look - I think you need to step back and look at the way your data is organised. It's not quite going to work the way you have it currently set up.

Your adjustments table should simply record what was moved, in or out and from or who to.

I would set it up something like (simplified)
MovementID, (PK - Autonumber)
PartNo,
MoveDate,
Qty, This should be negative if you ship it out or write it off, positive if you restock or it's initial stock.
ReferenceNo , either the PO you supplied it on or the PO you bought it on.
MovementType, This should link to a lookup table, with things like sale, write off, new stock, wastage etc in it. This will help with reporting.

You then simply add up all movements and you have your balance.

 

[CJ_London]

I agree with Minty re your stock movements table.

With regards customer PO's I presume you want to calculate 'current stock LESS any outstanding customer PO's'. If this is the case then you should have another table or two relating to your suppliers and your PO's to them so your calc would be

'current stock PLUS stock due in LESS any outstanding customer PO's=free stock'

 

[chriscook]

Hi,

Thanks for your suggestions, i will now work at encorporating them into my database.

We don't need a free stock field as the purpose of this database is just to record any movements in and out.

The reason why i have an adjustment and a customer PO table is, the person who will be making adjustments will be the storesperson. He will only be booking things in, but occasionaly may need to adjust some out.

The person who loads the Customer Po's, will once the goods are ready to ship, give the storesperson a packing note, and once he has got the packing note back, tick the shipped box on the Customer PO to remove the items from stock.

I hope this makes sense.

 

[chriscook]

All Working Now!!

Thank you very much for your help!!!!