combining multiple fields and comma delimi as strings into one field using update

johnmerlino

Registered User.
Local time
Today, 13:15
Joined
Oct 14, 2010
Messages
81
Hey all,

There's an initial table called contacts with 700,000 records
There's a field called temp_query, which contains 80 records.
Now print_ready matches the contacts and temp_query to check for similarities in first and last name and returns around 200 records because although there was 80, since it's using LIKE it finds more than 80 matches, so now I have 200.
So all I'm trying to do here is create a new query that assigns the concatenation of last,first,middle of temp_query to the names_1 column of print_ready. Hence, the names_1 should look identicial to the combined first_name, last_name, and middle_initial of temp_query where they have similar characters as determined by LIKE clause.
It's this new query, which is doing the UPDATE, where I am having difficulty:
Code:
UPDATE print_ready, temp_query SET print_ready.names_1 = CONCAT([`temp_query.last_name`],",",[`temp_query.first_name`],",",[`temp_query.middle_initial`])
WHERE (((print_ready.names_1) Like "*" & temp_query.first_name & "*" And (print_ready.names_1) Like "*" & temp_query.last_name & "*")) Or (((print_ready.names_2) Like "*" & temp_query.first_name & "*" And (print_ready.names_2) Like "*" & temp_query.last_name & "*"));
Thanks for any response.
 
AFIK There is no Concat function in Access.

Use the concatenation operator.
I would imagine you want the space between the names too.

Code:
SET print_ready.names_1 = temp_query.last_name & ", " & temp_query.first_name & ", " temp_query.middle_initial
 

Users who are viewing this thread

Back
Top Bottom