If I have data column (CraftCode) in one table and there are already records stored in there but it is not a foreign key or relational to any other table, am I able to cut that column from that table and drop it into another table with out messing up any of the data? I really don't want to rebuild the database if I don't have to. I just realized this critical error after entering in nearly 10,000 records that this piece of information should really be in another table. Please help!!
Can you move data from one table to another?
- Thread starter KristenD
- Start date
You may mean Fields. No Column in a Table.
You should be able to do this with Action Queries quite easily But..... there is a good chance of
so, test on a copy database first.
Create a Select Query that will collect the data you want to Append to another table - or Update to another table.
Append will add a record. Update will change a field in an existing record.
If you need to add the field to the table, then add the field first in Table Design and then run the Update Query to change the Null field to what ever is in the other table.
Then run an Update query to make the old field Null and then in table design, delete the field.
You may be able to delete the field with data in it.
In case you missed this point - Work on a Copy Database first.
You should be able to do this with Action Queries quite easily But..... there is a good chance of
so, test on a copy database first.Create a Select Query that will collect the data you want to Append to another table - or Update to another table.
Append will add a record. Update will change a field in an existing record.
If you need to add the field to the table, then add the field first in Table Design and then run the Update Query to change the Null field to what ever is in the other table.
Then run an Update query to make the old field Null and then in table design, delete the field.
You may be able to delete the field with data in it.
In case you missed this point - Work on a Copy Database first.
I ran the query and appended it to the field that it was supposed to go to but when I go to the table, the field is still empty. The data is still in the original table not in the new table.
I created the Select query with the field that needed to be appended, then in the design view made it and Append query and ran it. But the data did not move from the field in the original table to the new table. Am I missing a step?
I created the Select query with the field that needed to be appended, then in the design view made it and Append query and ran it. But the data did not move from the field in the original table to the new table. Am I missing a step?
- Local time
- Today, 06:57
- Joined
- Sep 12, 2006
- Messages
- 15,980
the thing is that the craftcodes in the current table, will be part of other data in the same table - one columnb of which will be an ID column (or some other column)
for an update query to work, access will need to match the common data in both tables.
it sounds like you are not quite understanding what the queries are doing, and also that your table design is not going to be correct
i think you should post your table design, and then we can probably help you more
---
so to answer your question - no you cannot "move" data. you can perform a series of processes that may have that effect. but what you are really doing is managing your data a SINGLE row/field at a time
so a query effectively processes each/every row in accordance with the rules you determined that all those rows (or a subset of rows) should be processed
if you are hoping to cut and paste, as you can with Excel - then you are on completely the wrong track
for an update query to work, access will need to match the common data in both tables.
it sounds like you are not quite understanding what the queries are doing, and also that your table design is not going to be correct
i think you should post your table design, and then we can probably help you more
---
so to answer your question - no you cannot "move" data. you can perform a series of processes that may have that effect. but what you are really doing is managing your data a SINGLE row/field at a time
so a query effectively processes each/every row in accordance with the rules you determined that all those rows (or a subset of rows) should be processed
if you are hoping to cut and paste, as you can with Excel - then you are on completely the wrong track
Last edited:
Thank you! I sincerely appreciate you helping me. I did check and double check that the ID field in the design view were the same. Was I wrong in assuming that the data would populate in the new field in the table? When you do an append query does it just make it point to that table? I am fairly new and pretty much learning by trial and error in creating and maintaining databases. I so very much appreciate this forum and all the help I've received so far!
I have attached a copy of the database I am working on. I need to get the craftcode from the tblEmp to the tblEmpRating.
Thank you again!
I have attached a copy of the database I am working on. I need to get the craftcode from the tblEmp to the tblEmpRating.
Thank you again!
- Local time
- Today, 01:57
- Joined
- Jan 23, 2006
- Messages
- 15,525
The accdb is only useful to people with Acc2007 or 2010. You may get more responses if you show us your latest tables and relationships as a jpg or pdf.
Did you make any changes since previous posts and comments from Pat Hartman?
Did you make any changes since previous posts and comments from Pat Hartman?
Remember there is a big difference to an Append Query and an Update Query.
Append will add a New Record to an exisiting Table. ie you have 10,000 records in table A and 5,000 in table B. Append will add Table A to Table B and end up with 15,000 records. Or, 12,500 if you select only some records to Append. Append will not change any existing records.
Update queries will not increase the number of records in a table. It will change some or all of them.
eg, If you add field to Table B ZipCode. If Table A has the Zip Code for some or all of the records in Table B then an Update Query can change the data in Table B where there is a match.
What ever you do, Append or Update, you are Copying the data. It will still be in Table A.
If you no longer need some or all of Table A's records, you can do a Delete Query which will delete a full record or millions of full records.
To clear a Field in a table, you can run an Update Query and change the value to Null or "" for some or all of the records or if the field is no longer needed, delete it in Design View.
Append will add a New Record to an exisiting Table. ie you have 10,000 records in table A and 5,000 in table B. Append will add Table A to Table B and end up with 15,000 records. Or, 12,500 if you select only some records to Append. Append will not change any existing records.
Update queries will not increase the number of records in a table. It will change some or all of them.
eg, If you add field to Table B ZipCode. If Table A has the Zip Code for some or all of the records in Table B then an Update Query can change the data in Table B where there is a match.
What ever you do, Append or Update, you are Copying the data. It will still be in Table A.
If you no longer need some or all of Table A's records, you can do a Delete Query which will delete a full record or millions of full records.
To clear a Field in a table, you can run an Update Query and change the value to Null or "" for some or all of the records or if the field is no longer needed, delete it in Design View.
You should get 1 or both of two messages when you run an Action Query.
An Error message or a message that a number of records have been changed/deleted etc.
Did you get any messages ?
The Fields don't need to have the same name. They do need to be the same or similar data type and if Text, at least as big as the data being appended.
An Error message or a message that a number of records have been changed/deleted etc.
Did you get any messages ?
The Fields don't need to have the same name. They do need to be the same or similar data type and if Text, at least as big as the data being appended.
Have a look at the attached database.
I made a new query qryTest1
I added a new field to tblEmpRating - CraftCodeNew, text 5 chrs (you have 255 chrs for a 3 chr field)
I ran qryTest1 and it updated 100 records.
Then you need to change the name of CraftCodeNew to CraftCode and Wala!
I made a new query qryTest1
I added a new field to tblEmpRating - CraftCodeNew, text 5 chrs (you have 255 chrs for a 3 chr field
)I ran qryTest1 and it updated 100 records.
Then you need to change the name of CraftCodeNew to CraftCode and Wala!
The lost S in the database name was an error
That's ok...I type fast too and some letter always gets left out.
Actually they can when using an Update with a Outer Join between the source and destination tables. Unmatched records in the source table will be appended to the destination.
Thanks, Galaxiom, I didn't realise this trap.
Explanation should be " An Update Query, done correctly, will not increase the number of records in a table."
Any such records created, in error, no doubt will be a cause of concern as they most would be expected to have a Primary Key and maybe some Default fields but could well be missing important fields and also would be expected to be un wanted records that will show up somewhere they are not expected.
It isn't about doing it "correctly" but realising that the Update query has this ability when using outer joins. There is nothing incorrect about it at all.
Indeed it is a very useful feature for updating existing records and inserting new records in a single query when synchronising imported data. If you only want to update existing records but not add new ones then use an inner join.
Similar threads
