How to swap records in separate tables using form.

[Wooden_Shoes]

Hello,



I am having a hard time trying to figure out how to do the following:

I have two separate tables in my DB. I would like to take a record from one table and swap it with a record from another table using a form. The form would recognize each record by a serial number. So essentially the records would just change location inside of the DB.

Any help would be appreciated.

Thanks!

[Bob Fitz]

Can you tell us why you need to do that. I'm just wondering why you have two tables which hold similar data.

[Wooden_Shoes]

I am wanting to do this for inventory purposes. One table lists equipment installed and the other table is for equipment not installed. At different times of the year, we replace the installed equpiment with the non-installed equipment for service purposes. There are usually a good amount being changed out, so I thought a form would be a good way to update my database, instead of constantly copying and pasting between the two tables.

Thankss,

Greg G

[Bob Fitz]

Why not keep all the data in one table and just add a field that can be marked to indicate the item's status.

[Wooden_Shoes]

Why not keep all the data in one table and just add a field that can be marked to indicate the item's status.

Don't mean to shoot down your idea, but this would not work because of the different types of information associated with each table. For example, the install location of the equipment is described by 2 separate fields, however each piece of equipment can change location when it is re-installed based upon certain criteria. This is why i would be looking for some type of method to "swap" the the records of two seperate pieces of equipment by using a form. The form would save time from having to fill in the updated information of the equipment being swapped.

I hope i did not confuse.

Thanks again,

Greg G.

[Bob Fitz]

Hi Greg,
In post #1 you speak about copying and pasting between the two tables. Tables should only be used to store data. Changing the stored data should be done using a form but I don't really understand what you want to swap. Can you explain in a little more detail about the data stored in each table and what it is that is to be "swapped" between them. Are you talking about whole rows of data, or maybe just certain fields.

[Wooden_Shoes]

Hi Greg,
In post #1 you speak about copying and pasting between the two tables. Tables should only be used to store data. Changing the stored data should be done using a form but I don't really understand what you want to swap. Can you explain in a little more detail about the data stored in each table and what it is that is to be "swapped" between them. Are you talking about whole rows of data, or maybe just certain fields.

Example:

Table: Installed Table: Not-Installed
Name Location Size Serial Number Reading Date Installed DateRemoved | Size Serial Number Reading DateRemoved


In the form, I would be wanting to "swap" the serial numbers. The rest of the field I would update using the form by mean of manual entry.

Form Ex:

Old Install S/N: (These would swap values between tables) New Install S/N:
Removal Date: (These two would be linked) Install Date:
Reading: (Manual Entry) New Reading:
Removal Date: (manual entry)

So, basically the only thing being swapped are the serial numbers. The rest of the fields are being updated based upon the current serial number associated with them via manual entry on the form.

Let me know if you this makes sense.

Thanks,

Greg G.

[Bob Fitz]

Let me know if you this makes sense.

Not quite, but I think I'm nearly there .

At different times of the year, we replace the installed equpiment with the non-installed equipment for service purposes.

Can you just explain this little bit more.

Also:
Does each table have a Primary Key and if so, what are they called?

BTW: I think it would be better, not to have a hyphen or space in a table name (Not-Installed). NotInstalled would be my choice.

[Wooden_Shoes]

Sorry for the response delay,

I let Access asign a primary key form me, although i could use my S/N field since each one is unique. I am going to be more clear on what I am looking for. I have added a table below that shows a rough example of how my form would look.

Form: Unit Record Updates

Old Unit S/N	#####	New Unit S/N	#####
Removal Date	#/##/##	Install Date	#/##/##
Counter Reading	#####	Removal Date	#/##/##
Old Unit Size	#####	Counter Reading	#####
		New Unit Size	#####

So this is how the form should work: The form is referring to two separate tables (Units-Installed and Units-Inventory). We change out units on a yearly bases depending on the size of the unit (2yrs, 3yrs, 4yrs, etc.). When it comes time to change, I would like my form to recognize where the Old Unit S/N (located in 'Units-Installed table') and New Unit S/N (located in 'Units-Inventory table') are at, and then swap their locations ('Old Unit S/N' would go to 'New Unit S/N's' location in the Units-Installed table and vise versa). The removal date for the 'Old Unit' will become the 'Install Date' for the 'New Unit'. The 'Counter Readings' will done as follows: The old unit 'Counter Reading' will be entered into the form and when submitted, it will take the place of the new unit's 'Counter Reading' in the Units-Inventory table, and vise versa for the new unit 'Counter Reading.'


These are not the actual names that i will be using in my tables and forms, but for clearity I have chosen these names. I hope my explanations are fairly clear. If there is a way I can do this inside of Access, that would be awesome.

Thanks for your help,


Greg G.

[orange]

I'm just reading the thread and have similar questions to Bob. It seems an awkward process - at least quite uncommon.

Consider, if I have a pieces of equipment with serial number, model number, and some unique number assigned by my company. Then I have various locations, each identified and named.
Why can't I just move Equip ABC to location L27, and move Equip XYZ to Inventory?

Just curious why it seems so complex???
Swapping "serial numbers" does not seem to be a realistic strategy in my view. Swap "equipment is at location" data.

[TG_W]

I have a similar situation what Wooden_Shoes has, so I was just going to chime in that I have one massive table with multiple forms that pull up the equipment name and only a certain group of fields per form. If you need to switch the equipment between installed and inventory, it would be best to follow Bob's advice and create a field (probably a check box would suffice) for when a peice of equipment goes from inventory to installed. You could then have your forms filter the equipment based on the installed/inventory field. You could then have a specific from for switching a piece of equipment between installed and inventory, then other forms to edit location, etc.

Hope that makes some sense.

BTW - massive table means 87 fields, so all of the info CAN be put into one table. Just need to figure your swtich.

[orange]

For anyone following this thread, here is a mockup of equipment being moved from and to Inventory to/from various Locations. I thought the set up offered by the poster seemed awkward, by constantly changing serial numbers. I think this design is relatively simple.

The attached jpg shows Tables and relationships, the MainForm which controls movements and has buttons to display various items. The display shows the Full set of Equipment Move Transactions, and shows that equipment WQ789 is currently at location L002 which is Westwood Bldg Room 217.

I have a sample database in Acc2003 if anyone wants to see more.

[Wooden_Shoes]

Orange,

If possible, i'd like to see your sample database.

Thanks!

[orange]

Here is the database in mdb format.

I used the info from the posts to do the development. It's been a while since it was created. I hope it's useful to you.