# Solved: Access Table: Copy Data from One Field To Another



## computerman29642 (Dec 4, 2007)

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.


----------



## slurpee55 (Oct 20, 2004)

An easy way is to create a query with all of the data from the first table in it. Then make an Expression that is equal to the column of data that you want to copy (e.g. if the data to be copied is in a column named text, the expressions would be "EXPR1: text"), so you end up with two columns containing the same data.
Then change the query to a make table query and make a new table that will have all the data - including the two with dupe data.


----------



## paulrw (Jun 9, 2008)

computerman29642 said:


> Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.


It's very easdy to do. Use an update query. Add the second field, if you need to. Open the query designer. Drag the source field and destination fields onto the query designer. Open the sql view and make your code look like this:

UPDATE field2
SET field2 = field1;
Now execute the query.

field2 is the field you want data copied to. field1 is where the data resides. Depending on how fast your computer is, it should run very fast.


----------



## slurpee55 (Oct 20, 2004)

The SQL for my suggestion is nearly as simple:
SELECT Table1.text, Table1.text AS EXPR1 INTO table2
FROM Table1
GROUP BY Table1.text;

Now, execute the query.


----------



## computerman29642 (Dec 4, 2007)

Will the query need to run each time the database is opened, or is this a one time type query?


----------



## slurpee55 (Oct 20, 2004)

Well, either way it would require the query to run again - the simpler would be to run the update each time. But, once you have done it for all 40,000 records once, why not just enter the new data twice?
For all that matter, why have dupe data at all?


----------



## computerman29642 (Dec 4, 2007)

There will only be dupe data for the old data. I have added a new field to one of my tables. I do not want the filed to be blank when the old data is reviewed, so I would like for the data in one field to be copied over to the new field.

Once that has been done, and new data is added to the database, there should no longer be any dupe data.


----------



## slurpee55 (Oct 20, 2004)

To avoid running the query each time, but not duplicating data, how about setting the new field's default value to something - either just something simple or perhaps something like =Now() ?


----------



## computerman29642 (Dec 4, 2007)

I just need to have the records copied and pasted one time. Is there not an easy way of doing this?


----------



## slurpee55 (Oct 20, 2004)

I think you could set a macro to run that update query each time the db was opened, would that be good enough?


----------



## computerman29642 (Dec 4, 2007)

I guess it would be worth a try.


----------



## slurpee55 (Oct 20, 2004)

has to be called autoexec...here is a link about it
http://www.databasedev.co.uk/autoexec-macro.html


----------



## computerman29642 (Dec 4, 2007)

Thanks, Slurpee. I will take a look at the link.


----------



## computerman29642 (Dec 4, 2007)

Is there a way to copy data in a text file into an Access Table?


----------



## computerman29642 (Dec 4, 2007)

I believe I have come up with a simpler way of doing this. I am going to mark this thread as solved, and open a new thread for help on this new idea.


----------



## computerman29642 (Dec 4, 2007)

Here is the new thread I opened.

http://forums.techguy.org/business-applications/762004-solved-access-table-report.html


----------



## jimr381 (Jul 20, 2007)

Paulrw was correct but there is no reason to do any SQL. When you change the query type to an update query it will show an "Update to" field within here type in the field name that you want to copy the values from in brackets like this [copyfromthisfield] if that was the name of the field. Hit the run button and it will update the values this one time.


----------



## computerman29642 (Dec 4, 2007)

So, do I still use the lines from Paulrw's post?

UPDATE field2
SET field2 = field1;
Now execute the query.


----------



## jimr381 (Jul 20, 2007)

About that. You will want to follow the steps that I specified before.


----------



## computerman29642 (Dec 4, 2007)

Jim, that worked! Thank you very much.


----------



## jimr381 (Jul 20, 2007)

Not a problem. I will try to keep you away from coding as much as I can.


----------



## computerman29642 (Dec 4, 2007)

LOL...Thanks.


----------



## slurpee55 (Oct 20, 2004)

If you open your update query in SQL view (go to design view first, then you can get to the SQL) you will see that it has that exact code written. So, technically, you did do the SQL thing - just did it through the interface.
And you can still do the autoexec macro to run that query each time upon opening.


----------



## jimr381 (Jul 20, 2007)

He did only want to do a one shot deal and that is why I had him shy away from the code and such. If I recall Access use TransactSQL in the background, which is very simple to use, but I will still keep my users away from it. 

Select
From
Where 
Ordered By
Grouped By 

If I recall.


----------



## slurpee55 (Oct 20, 2004)

computerman29642 said:


> ...*I do not want the filed to be blank when the old data is reviewed, so I would like for the data in one field to be copied over to the new field.*





jimr381 said:


> He did only want to do a one shot deal and that is why I had him shy away from the code and such.


Well, the code is minor - you are right, it is easy, but I generally avoid it too (just got brought in by someone else).
However, it isn't a one-shot deal.


----------



## jimr381 (Jul 20, 2007)

computerman29642 said:


> I just need to have the records copied and pasted *one *time. Is there not an easy way of doing this?


I read it as one time.


----------



## slurpee55 (Oct 20, 2004)

Yeah - you know, that made me think - this is a lot of records to redo each time. Does an update query compare the item in cell a with that in cell b and only update if their is a null in cell b? I suspect not - this would probably take VBA. But I think such code would be more efficient than re-updating 40,000 records every time upon opening.


----------



## slurpee55 (Oct 20, 2004)

Something a bit like the code here....
http://bytes.com/forum/thread846774.html


----------



## computerman29642 (Dec 4, 2007)

What are we trying to solve here?


----------



## slurpee55 (Oct 20, 2004)

Well, Cman (sorry, too lazy to type that much) I was thinking that you can create an update query and run it once on the 40,000 records. Great. 
But if you set up a macro to run that update query every time you open the db just so, as you enter new data you get that copied over to the other column, then the query has to check each of the 40,000+ records. Can't VBA get around this?


----------



## computerman29642 (Dec 4, 2007)

Yes, I am sure that VBA could get around that issue.


----------



## slurpee55 (Oct 20, 2004)

something like 
i=1 to n-1
IF [field1]<>[field2], [field2]=[field1]
Update
End IF
next i
???


----------



## jimr381 (Jul 20, 2007)

The update query updates all the records all in one shot. No need to loop or run it multiple times.  Try it out it is rather easy to do.


----------



## computerman29642 (Dec 4, 2007)

I can confirm that it does work.


----------



## slurpee55 (Oct 20, 2004)

The original one, yes. But with 40,000+ records, wouldn't doing it every single time slow down start-up??? Because Cman doesn't want to enter new data and have an empty cell in the other column.
(If OBP were here he would probably be going on about poor design - sure comes to my mind - but, oh well.)


----------

