Query to Move Records from one table to another?

[Mikeyboi92]

Good Afternoon,

OK i have had a quick browse on this forum but cannot really find anyone with the same problem.

Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.

For example.

User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'

(all the tables have the same structure etc. identical apart from the name of the table and records within)

I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over muliple tables then edit that record and move it to another Table.

I have tried to use a Union Query which works when searching but i cannot edit or move the record

any help would be great.

 

[spikepl]

BAck up a moment. You do NOT normally move data around or store it in differently named containers, simply to discern bunches of data. Such data goes all into one bucket, with a tag telling you what is what. The principle is called data normalization - google it. The price for not follwing it is eternal PITA in terms of redundant work in creating quereis, forms, reports and in maintenance.

 

[markiequarkie]

BAck up a moment. You do NOT normally move data around or store it in differently named containers, simply to discern bunches of data. Such data goes all into one bucket, with a tag telling you what is what. The principle is called data normalization - google it. The price for not follwing it is eternal PITA in terms of redundant work in creating quereis, forms, reports and in maintenance.

I want to do the same action only the data will go to a table that functions as an Archive so the data has little to no meaning

 

[plog]

I want to do the same action only the data will go to a table that functions as an Archive

You've reframed the problem a little, but still get the same answer as spike's: It stays where it is and you use another field to designate the row of data as archived ([Archive] = True).

If you have two tables with the same structure you are doing it wrong. Then when you want to move data between them you are really doing it wrong. Often the right answer is a little harder to implement, but in this case the right answer is so much easier. Add a new field to the table and just designate the data however you need to mark it as different from the rest. Get rid of that second table completely.

 

[Pat Hartman]

Then, you control the data you retrieve by using criteria in your queries. If you want active records, the criteria is:
Where ActiveYN = True
If you want inactive, then
Where ActiveYN = False

If you don't care, then omit that bit of the criteria.

Union queries aggregate data which means that they are never updateable. You can use them as the select part of an append query though.

 

[The_Doc_Man]

The ONLY time I ever had to move data wasn't from table to table. It was to mark some of the table's data to be TRULY archived i.e. not even in the same database any more. After marking the records I ran a query to select that data for an ExportToText action. After that succeeded, I ran a Delete on the marked info.

Before you ask, even though it was no longer active data, I had to keep it in SOME format because it was for the U.S. Navy which requires what only SEEMS to be eternal record keeping. They actually DID allow destruction of data more than 10 years old. Don't remember which form you have to use, but there is a form defined in the Federal Records act. If you fill it out, that form declares the data to be obsolete. Then you identify the data's specific media in a long list. Then you can actually destroy once the list is officially approved. THEN you fill out another form to record the approved destruction of data named from the first form. And THAT form, as far as I know, NEVER gets destroyed.