Update fields based on concatenate

[gstar]

Hi,
I have an access DB 2003 where I need to concatenate 3 columns, then
update the output to a new column in the table but am struggling to
achieve this.

For example from TblContacts I created a query with & concatenated
the 3 columns, Title, ForeName, Surname resulting in Mr John Smith
lets say. I have 21,000 records and need to insert them into
SalesFullName column in the TblContacts.

Seem to struggle either simply copying [File Lock errors], is there a
better method for this one off task?

Thanks

 

[Jeff Boyce]

You might want to rethink that "requirement".

Sure, you could do a one-time update, creating a value in a new column that
was the combination of three other columns... but what happens when you need
to change a single column's value? Say, if you're like me, with big fat
fingers, you committed a typo? Or if someone has their name changed?

You're buying yourself more work than you need to.

Instead of adding a column and updating the value and then making sure you
re-update the value every time something changes, how about just using a
query to derive the concatenation? That way, it's always up to date!

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I have an access DB 2003 where I need to concatenate 3 columns, then
update the output to a new column in the table but am struggling to
achieve this.

For example from TblContacts I created a query with & concatenated
the 3 columns, Title, ForeName, Surname resulting in Mr John Smith
lets say. I have 21,000 records and need to insert them into
SalesFullName column in the TblContacts.

Seem to struggle either simply copying [File Lock errors], is there a
better method for this one off task?

Yes: don't do it AT ALL.

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". You can create a Query
based on tblContacts, with a calculated field with the concatenated value,
e.g.

SalesFullName: ([Title] + " ") & [ForeName] & " " & [Surname]

You can use this query wherever you would use the table, or you can use the
same expression in the control source of a form or report textbox. Storing the
name redundantly in another field in tblContacts gets you no benefit, but
*does* give you the chore of seeing that the fields are kept up to date (say
if Miss Janet Watkins marries and becomes Mrs. Janet Heath).