Query needed to update a field on one of the tables on my db (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 11:21
Joined
Nov 23, 2019
Messages
75
HI all,
I need to update the ClientName field that is currently empty on one of the tables on my database using the ClientName field on the Clients table, I have been looking online but I'm not sure the best way to do it so any help with how to create the query would be appreciated.

Table layouts
Table 1 - tblClient

ClientID (Auto number field - Primary key)
ClientName (Short text field)

Plus a selection of other short text fields for the address etc

Table 2 - tblClientContacts

ClientContactID (Auto number field - Primary key)
fkClientID (number field - joined to the ClientID field on tblClient)
ClientName (Short text field that is currently empty, this is the one I would like to fill)
ClientContactName (Short text field)

Plus a selection of other fields for phone number, mobile number & email address etc

How would I write a query to copy the ClientName from tblClient into the ClientName field on tblClientContacts?

Thanks in advance
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:21
Joined
May 7, 2009
Messages
19,169
you Don't need to do it.
you don't even need a Duplicate ClientName field.
this is called Normalization.
what you need is to Create a Query that Join table2 To table1 on table2.fkClientID = table1.clientID:

select table2.clientContactID, table2.fkClientID, table1.ClientName From table2 Left Join table1
on table2.fkClientID = table1.ClientID;
 

PaulD2019

Registered User.
Local time
Today, 11:21
Joined
Nov 23, 2019
Messages
75
Thanks for your reply, I know it is normalization & a join is already in place, I need to fill the field for another reason.

Any help with the query would be appriciated
 

Minty

AWF VIP
Local time
Today, 11:21
Joined
Jul 26, 2013
Messages
10,354
I'm afraid I don't understand what possible reason there could be?
If you want to display or export that data in one place simply create a query with a join.

Storing it twice not only is a waste of time, you then have the even bigger headache of keeping the two versions of the same data synchronised.

If one or other is different how do you know which is correct? Massive headaches will follow.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:21
Joined
Jan 23, 2006
Messages
15,361
Please explain "your reason" --there may be options. But most situations of getting a field value from a related table would be resolved with a query with a join as has been stated.
 

Users who are viewing this thread

Top Bottom