UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Record From 1 Table To Another, Access 2003    
 
   
PALum
post Nov 10 2019, 10:38 AM
Post#1



Posts: 17
Joined: 4-October 18



Hi, I have a record in table A that i want updated with the contents of a record in table B. The records in tables A and B have exactly the same layout. I feel there should be an easier way of doing this rather than using SET fieldA = FieldB for each field in the record in Table A?

Many thanks
Go to the top of the page
 
GroverParkGeorge
post Nov 10 2019, 11:01 AM
Post#2


UA Admin
Posts: 36,171
Joined: 20-June 02
From: Newcastle, WA


An update query is probably the most effective, most efficient way to update records in a table in a Relational Database Application, like Access is.

However, that begs the question as to WHY this would need to be done in the first place.

It's not typical in a Relational Database Application to have two tables with exactly the same layout. And here, it sounds like the task is actually duplicating records in the tables. Usually that kind of procedure turns up in an inappropriately designed set of tables, and presents a lot of challenges beyond the immediate problem of synchronization.

So, I'd like to urge a bit of thought and review before you go much further.

Why would you need two identical tables?
This post has been edited by GroverParkGeorge: Nov 10 2019, 11:02 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Nov 10 2019, 12:07 PM
Post#3


UtterAccess VIP
Posts: 11,250
Joined: 10-February 04
From: South Charleston, WV


Copy objects.

I also question the need to copy data. Is it a one-time thing?

--------------------
Robert Crouser
Go to the top of the page
 
Jeff B.
post Nov 10 2019, 01:00 PM
Post#4


UtterAccess VIP
Posts: 10,335
Joined: 30-April 10
From: Pacific NorthWet


I'll jump in too … two identical tables will, as George points out, beg the question of which table has the correct data. If you were limited to using a spreadsheet, I can easily envision copying data from one sheet to another to show a change in status. That is neither necessary nor good use of a relational database like Access.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
PALum
post Nov 10 2019, 05:55 PM
Post#5



Posts: 17
Joined: 4-October 18



Hi,
The duplicate record is moved to a temporary table where users can play with various values/parameters until the required results are found. These values are then recorded by copying back to the record in the main table.
Go to the top of the page
 
projecttoday
post Nov 10 2019, 06:11 PM
Post#6


UtterAccess VIP
Posts: 11,250
Joined: 10-February 04
From: South Charleston, WV


Okay. Are you just trying to avoid one multi-field UPDATE statement?

--------------------
Robert Crouser
Go to the top of the page
 
PALum
post Nov 11 2019, 05:21 PM
Post#7



Posts: 17
Joined: 4-October 18



Basically Yes! The main table records are rather big and its easy to miss a field. I can't just delete the main record while the copy is in the Temp Table as the main record needs to be available for enquiry and it will orphan the child records. Plus the changes in the temp table may not be used. I've inherited this DB so if i was designing it from scratch i would probably have done this in a better way. I have found a solution using a query:

UPDATE TableA INNER JOIN TableBTemp ON TableA.Keyfield = TableBTemp.Keyfield SET TableA.Field1 = [TableBTemp].[Field1], TableA.Field2 = [TableBTemp].[Field2] etc, etc...

It still means having a list of fields but its easier to follow in a query Design View.
Go to the top of the page
 
projecttoday
post Nov 11 2019, 06:39 PM
Post#8


UtterAccess VIP
Posts: 11,250
Joined: 10-February 04
From: South Charleston, WV


If you have a table called "TableA" then it might very well be a bad table design. But I have worked with a form bound to a temporary table, needing update for permanence. This was so the data won't be saved immediately upon entry.

I think you must specify the fields individually in UPDATE. You might get help from the designer, but the fields have to be specified.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 08:15 PM