Hi,
I will try my best to keep this as simple as possible but I find this very hard to explain!
I am trying to create a database to control stock in my wife's shoe shop. I would like to be able to calculate the current number of each of unique item in stock but I am having issues with working out how to do this correctly.
There are two tables working together that are the subjects of this question, tblStock and tblSales. In tblStock is a field for the total number of a stock item purchased by the store (for example year one - 10 purchased, year two - 10 purchased = 20 total), and in tblSales is a field for the number sold in each transaction. The way it works is, when there is a sale, the record is stored in tblSales and linked to the item in tblStock. This means that over time, there will be multiple records in tblSales that relate to one item in tblStock.
My problem is then calculating the resultant number in stock. I would like another field that calculates the current number in stock by subtracting the number of each unique item sold over all transactions from the the total number of historic stock.
I tried a query with a field:
NumberInStock: ([TotalPurchaseQuantity]-[AmountSold])
But this understandably only works for individual transaction, and doesn't give a running total.
I also tried this, but this threw up an error:
NumberInStock: Sum([TotalPurchaseQuantity]-[AmountSold])
I think ideally I would like a column in a query that could calculate the total number sold of each individual item, by adding up all of the individual transaction amounts.
I know that probably isn't the best explanation, but any help would be greatly appreciated.
I can share the database with anyone willing to take a look into this.
Thanks in advance!
Alex.
I will try my best to keep this as simple as possible but I find this very hard to explain!
I am trying to create a database to control stock in my wife's shoe shop. I would like to be able to calculate the current number of each of unique item in stock but I am having issues with working out how to do this correctly.
There are two tables working together that are the subjects of this question, tblStock and tblSales. In tblStock is a field for the total number of a stock item purchased by the store (for example year one - 10 purchased, year two - 10 purchased = 20 total), and in tblSales is a field for the number sold in each transaction. The way it works is, when there is a sale, the record is stored in tblSales and linked to the item in tblStock. This means that over time, there will be multiple records in tblSales that relate to one item in tblStock.
My problem is then calculating the resultant number in stock. I would like another field that calculates the current number in stock by subtracting the number of each unique item sold over all transactions from the the total number of historic stock.
I tried a query with a field:
NumberInStock: ([TotalPurchaseQuantity]-[AmountSold])
But this understandably only works for individual transaction, and doesn't give a running total.
I also tried this, but this threw up an error:
NumberInStock: Sum([TotalPurchaseQuantity]-[AmountSold])
I think ideally I would like a column in a query that could calculate the total number sold of each individual item, by adding up all of the individual transaction amounts.
I know that probably isn't the best explanation, but any help would be greatly appreciated.
I can share the database with anyone willing to take a look into this.
Thanks in advance!
Alex.