How do I swap values of rows and the same column? (1 Viewer)

[cheberdy]

I have a table of item. Each item has a name, a number and a bin location. I want to have a form where I enter two item names/numbers and swap the bin locations of the two. How would this be done?

 

[jdraw]

Seems similar or an extension to your previous post.
Can you step back and describe in simple terms the business process where this arises?

From my view:
- you have "items" that
- you store in "bins" and sometimes
- you have to move an item(s) from one bin to another bin.
You have multiple Items, and multiple Bins, and you need to know which Items are stored in which Bins. This is a many to many relationship.

So, your setup would include
tblItem, tblBin, and another tblBinContainsItem

tblItem---->tblBinContainsItem<----tblItem

You can query tblBinContainsItem and locate where ItemA exists.
You can update ItemA's location by
- remove ItemA from current location
- update ItemA to new location.

To move an item from one bin to another:
-Update the BinID field in tblBinContainsItem for the specific ItemID to the new BinID.
-Update the Quantity field if necessary
-Or, delete the existing record from tblBinContainsItem and insert a new record with the updated information.

 

[CJ_London]

with lack of information, best I can suggest is copy/paste.

you could try having two combo boxes on a form to select each item then a button to execute the swap

if you need a more detailed response, provide some information such as example data. Have to say from what little you have provided, it sounds like your data is not set up correctly. I would expect 3 tables - items, bins and a joining table to record which bins an item is stored in.

 

[arnelgp]

I have a table of item. Each item has a name, a number and a bin location. I want to have a form where I enter two item names/numbers and swap the bin locations of the two. How would this be done?

it will be hard for you if you have not done this before either in Excel or manually.
is this a school project?

actually you don't just "swap" the locations between 2 items.
you actually "record" the activities of your inventory.
without documentation, one might ask why and who request for the transfer?

the demo maybe very hard for you.

 

[cheberdy]

But here is more information:
I have a table article:
ID / Item Name / Storage Bin / Stock
---------------------------------------------------------
1 __ red cable________2____________1
2 __ blue cable_______4____________1

I have a table storage bins
Storage Bin(ID) / Warehouse
-----------------------------------
2___________________1
4___________________1

and a few things more, but they are not really relevant.
I would like to swap Red Cable with Blue Cable in the Items table for example. So that red cable gets bin 4 without the blue cable getting bin 2 and once that the blue cable gets bin 2.

 

[cheberdy]

it will be hard for you if you have not done this before either in Excel or manually.
is this a school project?

actually you don't just "swap" the locations between 2 items.
you actually "record" the activities of your inventory.
without documentation, one might ask why and who request for the transfer?

the demo maybe very hard for you.

Can you explain what you are doing?

 

[cheberdy]

it will be hard for you if you have not done this before either in Excel or manually.
is this a school project?

actually you don't just "swap" the locations between 2 items.
you actually "record" the activities of your inventory.
without documentation, one might ask why and who request for the transfer?

the demo maybe very hard for you.

I think here is a problem. The LocationID and the Location in the ItemLocationOnHandQ and LastInventoryDateQ and LastinevotryQtyQ forms are not updated.

 

[Pat Hartman]

You do this with transactions. It takes two records. One to move the red cable to bin 4 and another to move the blue cable to bin2.

I know you have multiple threads going so I'm not sure how clear the advice has been regarding HOW to record inventory movement. It is ALWAYS done via transactions assuming you want auditability, you NEVER cut and paste or simply change the bin location by over writing.

So, the transaction has a number of fields and different applications might have different requirements. Essentially a transaction has these fields at a minimum plus an autonumber PK.

1. PartID
2. TransactionType - describes the movement type, Sale, Receipt, internal transfer, adjustment, etc
3. FromLocation - can be local inventory, remote inventory, or receipt from a vendor
4. ToLocation - can be local inventory, remote inventory, or sale to a customer
5. Quantity
6. SerialNumber - for serialized parts.

 

[cheberdy]

You do this with transactions. It takes two records. One to move the red cable to bin 4 and another to move the blue cable to bin2.

I know you have multiple threads going so I'm not sure how clear the advice has been regarding HOW to record inventory movement. It is ALWAYS done via transactions assuming you want auditability, you NEVER cut and paste or simply change the bin location by over writing.

So, the transaction has a number of fields and different applications might have different requirements. Essentially a transaction has these fields at a minimum plus an autonumber PK.

1. PartID
2. TransactionType - describes the movement type, Sale, Receipt, internal transfer, adjustment, etc
3. FromLocation - can be local inventory, remote inventory, or receipt from a vendor
4. ToLocation - can be local inventory, remote inventory, or sale to a customer
5. Quantity
6. SerialNumber - for serialized parts.

And then in the transaction table I swap the storage bins. How would one do that?

 

[Pat Hartman]

And then in the transaction table I swap the storage bins. How would one do that?

No. Nothing is "swapped". Something is moved from bin 2 to bin 1 and something is moved from bin 1 to bin 2. TWO separate actions. Just the way you would do it in real life assuming the two bins are not adjacent and you can't pick up one with your left hand and the other with the right, cross your arms, and drop them in to the "other" bin.