Two records in the same table from one form

[springa]

Hi all!

I have a database with a table record inventory movements. The columns are as follows:
ID
Date
Entered By
Item ID
Transaction Detail
Order Number
CR Number
Warehouse Location
UnitsOpening
UnitsReceived
Units Dispatched
UnitsDisposedOf
Remarks

I have everything running perfectly except the inventory movements within the warehouse. It is simple enough to get a form to record the correct quantities in the UnitsReceived and UnitsDispatched columns to balance out the movement, however, I cannot enter in an old and a new warehouse location. For my database to be able to correctly show where this stock is, I would need to make one record for the UnitsDispatched field which would remove it's current warehouse location and one record into UnitsReceived which would record the new warehouse location.

Is there any way that these two records can be created on one form?

Thanks for your help

 

[Peter Reid]

How are you adding the one record when the transaction doesn't involve a movement within the warehouse?

 

[springa]

Any movement out or into the warehouse is recorded against a warehouse location. When moving items into our out of the warehouse they are recorded against a specific warehouse location so that they are added or deducted to that location. My problem with internal transfers comes into play because I cannot record two warehouse locations against one transaction i.e. to deduct the unit from one location and add it to another.

 

[CEH]

It sounds like it might be the structure of your tables. Too much in one table. It's hard to tell with what you have shown... Are these fields from a query or table? If it is a table all of these fields should not be in the same table. Just at a glance I can see 3 tables.... Items... Warehouse...Transaction. If the warehouse table is separate.... a simple table with only WarehouseID, warehouse location... Then the transaction could be tied to the warehouse on a one to many. Then a transaction could be split between warehouses.
Look at a transfer as a sell without the money...

 

[Peter Reid]

Any movement out or into the warehouse is recorded against a warehouse location. When moving items into our out of the warehouse they are recorded against a specific warehouse location so that they are added or deducted to that location. My problem with internal transfers comes into play because I cannot record two warehouse locations against one transaction i.e. to deduct the unit from one location and add it to another.

I understand that a movement in or out is recorded in a single record and (IMO) your suggestion to create 2 movements for an internal movement is correct.

I read your original post as 'How do I create 2 movement records?' and my reply was intended to be read as 'after doing whatever you do to create 1 movement record, do it again but with different [warehouse location] and Units Opening/Received/Despatched data'

 

[gemma-the-husky]

is that because they would be sharing the same id/location, and therefore causing an index problem

ordinarily i would suggest adding the quantity to the index, but this would need plus and minus movements to be in the same column

so what about adding another field - movementin as a yesno, and adding THAT to the index

then if you transfer stuff, one line will be an "IN", and one an "out", so you woont get index errors

 

[CEH]

Gemma,
I think he already has that..... "In...Out" "Unit Received" "Units Dispatched" If you were to add a "Transaction Type" field... then you could pick "Sale" or "Transfer" Quantity AND cost really needs to be known for future inventory valuation ...... I think Springa, you might have opened a project bigger than you knew it would be Inventory DB's get pretty hairy!

 

[springa]

Hi all!

Thanks for your replies! CEH, you're right, this project has become a monster but is completely functional now with order requests, collection requests and everything else you could possibly imagine! I am still just stuck with this issue. I am running two tables, one for inventory movements and one for item details as you assumed. I am not running a seperate warehouse location table as I would technically be sitting with the same issue. We aren't running any sort of cost figures soquantities are recorded in the movements fields i.e. Unit Received etc...

A possible solution to this would be to create a form that records the first movement from the WH Location i.e.
ID 112 (Autonumber)
Date 04/02/2009 (Today)
Entered By Me
Item ID 69
Transaction Detail Internal Transfer
Order Number <Blank>
CR Number <Blank>
Warehouse Location 01.01.01
UnitsOpening <Blank>
UnitsReceived <Blank>
Units Dispatched 10
UnitsDisposedOf <Blank>
Remarks EG Consolidation of pallet spot

The second transaction would record:
ID 113 (Autonumber)
Date 04/02/2009 (Today)
Entered By Me
Item ID 69
Transaction Detail Internal Transfer
Order Number <Blank>
CR Number <Blank>
Warehouse Location 01.01.02
UnitsOpening <Blank>
UnitsReceived 10
Units Dispatched <Blank>
UnitsDisposedOf <Blank>
Remarks EG Consolidation of pallet spot

What seems to me to be a simple solution would be to have a form where the user would be required to enter:
Entered By
Item ID
Transaction Detail (Default value set to Internal Transfer so would not actually have to select this Category)
Warehouse Location (FROM)
Warehouse Location (TO)
Quantity
Remarks

And somehow come out with two records? Any ideas?

 

[springa]

is that because they would be sharing the same id/location, and therefore causing an index problem

ordinarily i would suggest adding the quantity to the index, but this would need plus and minus movements to be in the same column

so what about adding another field - movementin as a yesno, and adding THAT to the index

then if you transfer stuff, one line will be an "IN", and one an "out", so you woont get index errors

Thanks for your reply

That's pretty much what I'm trying to get, however, I don't want the user to have to manually enter a transaction for dispatching from one location and receiving to another. It reduces the space for error if they simply enter the information as I've detailed above. A quantity column is not feasible as we have multiple items per location as well as multiple locations per item. This would become a nightmare, plus the database is too far down the road to go back and change that... Also, even if I were to run a quantity column, we would still be in a position where you would have to manually enter two transactions for internal movement.

The database works fine, tables are well structured (at least I like to think so ). The only issue is manually having to record two transactions for an internal movement

 

[springa]

CEH, back to your query on table structure, all of the fields in this table directly relate to item movements, there are seperate tables for Collection Requests, Orders, Item Details, as well as list tables used for list boxes in forms for employees, transaction types, item categories. The fields in this table cannot be seperated into additional tables and we need to record each individual inventory movement so recording quantity against a warehouse location is not really an option either.

 

[CEH]

What seems to me to be a simple solution would be to have a form where the user would be required to enter:
Entered By
Item ID
Transaction Detail (Default value set to Internal Transfer so would not actually have to select this Category)
Warehouse Location (FROM)
Warehouse Location (TO)
Quantity
Remarks

And somehow come out with two records? Any ideas?

I think your getting close here... What I am picturing is a form and subform... and instead of just "Quantity field" you would have quantity In, quantity out, The main form connected to subform by TansactionID. In the mainform Quantity out, dropdown to pick warehouse, in subform same... TransactionID, Warehouse, and Quantity in. Think of it like a check register.
Some might use positive and negative in one Quantity field....but I like to see my debits and credits in seperate fields Old school This should also allow you to calculate quantity on hand and any given time.

 

[springa]

Hi CEH,

I like the concept of a Q in Q out, however in this situation we would have the same situation where if we make an internal movement we would require one record from a location and one to a location. The problem is not being able to record two locations on one record, putting in a Location From and Location To field but I am trying to avoid this as it means a major restructuring to the database...

 

[gemma-the-husky]

ID
Date
Entered By
Item ID
Transaction Detail
Order Number
CR Number
Warehouse Location
UnitsOpening
UnitsReceived
Units Dispatched
UnitsDisposedOf
Remarks

to go back to this - i'll clarify my earlier post

you say you cant have duplicate items, presumably because you are moving items within the same warehouse

so why can you not have the same transaction included twice- is it because your unique key does not include the transaction quantity - in which case just change the key to include the transaction quantity, then you will be able to record the in and out

personally, i would have a single column for transaction qty, showing ins as +, and outs as -, as it makes totalling a lot easier

 

[springa]

Hi all, thanks for your help with this, I've managed to work it out, I now have a form with a subform which automatically includes all details from the main form and then creates a second transaction. So the user basically selects an item and where it's going to and the database records two transactions, one movement out of the old location and one movement into the new location. Works 100%.

Gemma, even if you have a single column, the issue is that there is only one Warehouse Location column and my difficulty was transferring items from one location to another i.e. if I have 10 items in Location A and want to move 5 items to location B.

Sorted out now, thanks everyone for your ideas, they put me on the right track!