Transfer stock to location

[Gismo]

Hi all,

I am struggling to get this one going and was hoping to get some assistance

I have a table where all qty's of products are recorded for stock procured and stock sold

I want to be able to transfer manufactured stock between different locations

So what I need to do is have a form where the current stock and the locations reflect
When I select the main office, sock needs to be taken from the main office location as a minus qty and post it to the location to selected as a positive qry

Stock can also be transferred from any other location back to the main office location

So depending on the from location and the to location the products in the temp file needs to be updated to the transactions table

I an not sure on how to do the update query with the different option for the from and to locations

if transferred from the main office, then the QTY must be updated as a minus
Then the location to must be updated to the product as a positive Qty

If from and to any other location except from the main office the location and qty must be adjusted accordingly

My fields in the transactions table are:
Product
Qty
From Location
To Location
Location Qty

 

[arnelgp]

ofcourse this is done manually.
if you have transfer, then you record it using pertinent
document like transfer form or Gatepass.
the transaction type is Transfer.

 

[Minty]

It's simply a pair of transactions in the movement table, of the type Stock Movement?
Out from the "From" location and in to the "In" location.

Unless I'm missing something? You would still want to know that Laura has it?

 

[Gismo]

I am just not sure how to do this via an update without have multiple queries for each instance
Main to hub
Main to airport
main to Laura

Or even airport to Laura ext.

this would then be a multiple of about 25 queries which I dont want

so what I am thinking of is when from = Main office and to is any other location
the query should minus the transaction from transactions table Qty
Then add this to the transaction table under location Qty and give a location ID

How to contenate this formula I am not sure off

The below is from the temporary table and mist update the transactions query
Most probably with 2 transactions
Stock out and stock in

 

[Minty]

Your transaction table isn't really correct. The from and to are complicating things unnecessarily.
It simply needs a Location and a value. It certainly shouldn't have the location quantity.

So to remove something you put in a negative transaction, to add something you use a positive transaction.
Your location stock quantity is the sum of the transactions by grouped by product and location.
You don't need any update queries at all, just the correct transactions.

You can build a form to gather the data as per your example and just run an insert to make the appropriate transactions.

 

[Solo712]

Your transaction table isn't really correct. The from and to are complicating things unnecessarily.
It simply needs a Location and a value. It certainly shouldn't have the location quantity.

So to remove something you put in a negative transaction, to add something you use a positive transaction.
Your location stock quantity is the sum of the transactions by grouped by product and location.
You don't need any update queries at all, just the correct transactions.

You can build a form to gather the data as per your example and just run an insert to make the appropriate transactions.

No. Each transaction definitely needs to record source of the goods and their destination as a pair. To have any control over the operation you need to do this (and do that explicitly), so your inventory does not get out of balance. Whether that is done the way the OP does it or through a header-detail pairing is not important. But you would definitely want to know where each specific product packet came from and where it went.
Your inventory at each location would then be

[LocationX].[StartQuantity] + DSum("[Qty]", "Transactions", ["To Location"] = & LocationX) - DSum("[Qty]", "Transactions", ["From Location"] = & LocationX)

Best, Jiri

 

[Minty]

@Solo712 I disagree, and I've built complicated inventory systems.

When you receive new goods in, what do you put as the location it came from?
It didn't have one before it arrived?
Similarly when you sell something which location do you send it to? Customer is not an inventory location point.

It adds a layer of complication that isn't required.
You add a movement type to each transaction (In, Out, Adjustment, StockCheck etc.) and if required a sales or purchase reference for audit, but that isn't part of the inventory transaction.

 

[plog]

I have a table where all qty's of products are recorded for stock procured and stock sold

I want to be able to transfer manufactured stock between different locations

Change 'procured' and 'sold' in the first sentence to 'debits' and 'credits' and your initial table works for transfers.

So when you move product from Location X to Location Y, you add a record in your table to debit Location X and you add a record in your table to credit Location Y. You don't need an additional table, you don't need to store prior and current inventories, you just use your table for both of those.

From a form perspective, your can look exactly as it does now, but it needs to be unbound. After you enter data you click a button and via VBA your form's code adds the necessary records to the table you already have in the manner I described above (2 records--1 for credit one for debit of the quantity..

 

[CJ_London]

When I select the main office, sock needs to be taken from the main office location as a minus qty and post it to the location to selected as a positive qry

Stock can also be transferred from any other location back to the main office location

so to be clear, you never have stock transfers between locations where neither is the main office (given the example products I would presume not)

I have a table where all qty's of products are recorded for stock procured and stock sold

so what happens here when stock is sold?

And whilst we're at it, given the example data provided has a limited life, what about stock writeoffs etc?

 

[Solo712]

@Solo712 I disagree, and I've built complicated inventory systems.

When you receive new goods in, what do you put as the location it came from?
It didn't have one before it arrived?
Similarly when you sell something which location do you send it to? Customer is not an inventory location point.

It adds a layer of complication that isn't required.
You add a movement type to each transaction (In, Out, Adjustment, StockCheck etc.) and if required a sales or purchase reference for audit, but that isn't part of the inventory transaction.

Look, this is so basic that I am not about to debate it. Obviously, when you build an inventory system it will have not just internal transfers of finished goods but purchases, production, sales. You are trying to change the conversation. You have been advocating split transactions, which I take an exception to.

Best,
Jiri

 

[Minty]

You have been advocating split transactions,

No, I'm recommending a single transaction in or out per record.
Your method cludges two together and makes the subsequent queries and processing a great deal more complicated.

 

[plog]

Look, this is so basic that I am not about to debate it....

However...here's bullet points of my argument:

 

[Solo712]

No, I'm recommending a single transaction in or out per record.
Your method cludges two together and makes the subsequent queries and processing a great deal more complicated.

It's the same thing. Previously,

Minty: So to remove something you put in a negative transaction, to add something you use a positive transaction.

Among experts, the transfer of goods or funds from one location / account to another is a single transaction. Whether in a manual or automated system, transaction is a resource transfer event recorded in a way that identifies both the source and receiver. The double-entry bookkeeping system is an age-old proven tool that eliminates (,or best prevents,) loss of information. You should look into it!

Best,
Jiri

 

[Minty]

Double entry booking keeping was invented to accommodate the inherent problems of a paper-based reconciliation system and to keep accountants in work.

It is unnecessary in today's world but keeps a lot of people employed, I assume you have seen the hoops commercial accounts systems jump through to maintain such an antiquated system running.

 

[Solo712]

Double entry booking keeping was invented to accommodate the inherent problems of a paper-based reconciliation system and to keep accountants in work.

It is unnecessary in today's world but keeps a lot of people employed, I assume you have seen the hoops commercial accounts systems jump through to maintain such an antiquated system running.

You live in a parallel universe, Minty. Have you ever heard the expression "balancing the books"? You simply don't understand the basic concepts at work in the double-entry system. I have not yet seen a viable commercial accounting system that would do away with the principle. The double-entry concept may be obscured in today's user (friendly) interfaces but it is essential in the back end for fiscal functions and control.

Jiri

 

[Pat Hartman]

I use a combined method. An internal transfer has two transactions. A from and a to but both records reference the "other" location. For external transfers, same thing except that you have an In that points to a PO as the from and an out that points to the order as the to. That lets me sum one column for a net balance at any location but it also allows auditability between the various subsystems so you can see which inventory transactions went with each purchase or sale and see internal transfers if you need to. Adjustments don't have a second location.

 

[arnelgp]

I agree with Solo on this one.
If you will document (record) the transfer, you put as much info to it.
the starting point and the end point, so there is no ambiguity.
the record will answer all inquiries about the transfer.
this is not about unknown customer but a known a defined location.

i don't know why this is hard for the OP.
do system analysis first before coding.
talk to people involved in this activity and
those who keep records them (manually).
whatever the current system, should only be
translated to code. you don't create New system
that everyone will follow. you stick to the business rule
and proven system.

 

[Gismo]

Thank you everyone for the input
I managed to get the required result

My transactions table has one Qty field
I added a stock location field
All stock received and Invoiced comes from Location 1 unless sold from another location
When transferred from / To, I do a 2 line transaction, debit and credit

Seem so work well

 

[gemma-the-husky]

A similar issue is being discussed here
Table & Form design - Stock Transfer Multiple Locations | Access World Forums (access-programmers.co.uk)

fwiw, I agree with @Minty
This is analogous to a general ledger transfer journal.
I would just make sure the "batch" of transfers sums to zero, to maintain the integrity of the inventory tables.

But even if you enter the transfer as single from and to - you want to record it as two stock transactions, one a minus (out), and one a plus (in)