Allocate Quantity

[Ahmed-eg]

hi all

i need your help to allocate the sold quantity on the stock balance .
for example :

ID Transactiondate Itemcode itemname QuantityIn QuantityOut AllocateQuantity
1 01/01/2000 1 Apple 50 50


2 01/02/2000 1 Apple 100 90
3 01/03/2000 1 Apple 100
4 01/04/2000 2 Orange 50
5 01/05/2000 1 Apple 60


I WANT TO ALLOCATE ( QUANTITYOUT ) 60 UNIT TO QUANTITY IN ( 50 + 10 )
AND THE BALANCE IS RUNNING IN NEW COLUMN 50-50 =0
100-10 = 90 ... ETC

HOW CAN I DO IT
I ATTACHED MY DB To EDIT

[hci_jon]

It's hard for me to understand exactly what you want.

Tables store record and transaction data
Queries are generally used to combine data from multiple tables and/or perform calculations on various fields for display in a form or report
Forms allow you to display data to the user and calculated fields and are used for inputting data.
Reports mainly are used to display, print, and export data and calculated fields.

It's not clear how you want the user to interact with the data.
Maybe you want the user to enter Quantity In and Quantity Out for a certain date, and also see the running total from previous transactions.
Maybe you also want the user to enter these for only a particular item (apple vs orange)

You could use a form to filter which item the user is working with and display a list of transactions for that item with a running total.

Is that what you are looking to do?

[Ahmed-eg]

I want to make query to allocate the quantity out based on oldest stock balance ..
And when i sell item it's reduce from the oldest stock balance first like first in first out
I want to do it in easier way iam not professional in vba .
If i bought 10 units as
3 unit at 1st march with 10$ per unit
7 unit at 3rd march with 15$ per unit
When i sell 7 units i want to make query allocate the sold units as :

3 units from 1st march with 10$ per unit and stock balance in 1st march become 0

4 units from 3rd march with 15$ per unit and stock balnce in 3rd march become 4 unit

I want to make like this using query based on purshas date and sold date .

[ArviLaanemets]

Having stock balance stored for every transaction is very bad idea. An example:
A couple of days ago some item(s) arrived, but weren't registered for some reason. This was discovered today, and arrival was registered today, but arrival date must be entered as real arrival date. Meanwhile there were additional deliveries of same item(s) yesterday/today, which were registered before. So when the missed arrival is registered, balances for all later arrivals must be recalculated too!

A better design is to have a transaction log for your main storage, where all arrivals/returns/etc. are registered as positive quantities, and all deliveries/uses/etc. are registered as negative quantities. A balance for any article at any time moment is calculated as sum of transactions for this article.

In case the number of transactions will make the calculation too slow, you have to make an inventory of your storage e.g. yearly, and save real balance at this date and inventory date for every article present into separate inventory table. Then a balance for any article at any time moment will be calculated as inventory quantity (or null in case the inventory entry is missing for this article) from latest inventory before balance date + sum of all transactions for this article between inventory date and balance date.

NB! The balance is calculated whenever it is needed, but not saved!

[NoellaG]

Hi, most warehouse databases (example: MS Dynamics) have a stock table where you can always have the current stock. Each time a transaction IN or OUT occurs: wrapped in a transaction, the transaction table(s) are put up to date AND the stock tables are put up to date using INSERT/UPDATE queries. During the yearly inventory, stock correction lines are inserted into the transaction tables and the actual stock quantity is corrected. Prices and costs (like repacking) are stored with the transactions.
In our country, when storing dangerous goods, it is even legally obliged to have up to date stock tables at any given moment.
So for any transaction , you always need 2 queries: 1 INSERT query to create the transaction + 1 INSERT (for new articles) or UPDATE query for updating the stock table. Both queries can be run together in a macro or a VBA procedure.

[hci_jon]

Thanks Agmed-eg. I understand better now.

So I am thinking you want the just the quantity right now based on your question, but this further expansion also indicates pricing.

Are the units for a particular account or item?

Like you might buy 10 units of company A (or Apples) stock over 2 transactions at different prices, but then you might also buy 5 units of company O (or Oranges) stock?

And you want to track the stocks separately?

Do you want to see how much you spent on the sold stock? Are you also going to track the sold stock price?
Sounds like you want to know that the 7 units sold in your example cost 3*10 + 4*15 (100$).

And when you say you want to use the query based on purchase date and sold date, you want to specify a particular date range and find all the transactions when the product was purchased or sold with a running balance? Does the running balance need to take into account the prior balance of stock in any way?