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

[johnmerlino]

Hey all,



I'm trying to figure out how to update a field of print_ready table with the values of three fields delimited by commas where the fields being updated has characters that match the fields that are doing the assignment:

UPDATE print_ready SET 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    & "*" 
)
;

This unfortunately doesn't work, but the idea is just to search a string match between two queries (I believe they act as tables in Access) annd where there is a match in characters, then update print_ready with the combined values of the three aforementioned fields in temp_query.
I hope it makes sense. Thanks for response.

[slave138]

Since you didn't really say exactly what kind of problems you were having I'm just going to take a guess or two based on your query. First, I don't think Access has a Concat function but it appears you already know how to concatenate a string.

You could just let the temp query generate the name by typing this into a Field in the Query Builder:

full_name: last_name & "," & first_name & "," & middle_initial

Then the update query can be something like:

UPDATE print_ready SET names_1 =temp_query.full_name
WHERE ...

[johnmerlino]

Thanks for reply.
I get a "syntax error missing operator in query expression" error with this:

UPDATE print_ready 
SET full_name = "last_name" & "," & "first_name" & "," & "middle_initial" 
SET print_ready.names_1 = temp_query.full_name
WHERE 
(
contacts.names_1  Like "*" & temp_query.first_name    & "*" 
and 
contacts.names_1  Like "*" & temp_query.last_name    & "*" 

)
OR
(
contacts.names_2  Like "*" & temp_query.first_name    & "*" 
and 
contacts.names_2  Like "*" & temp_query.last_name    & "*" 
)
;

[slave138]

SET full_name = "last_name" & "," & "first_name" & "," & "middle_initial"

My SQL is a little shakey, but I think you should to add this to the temp_query, not the Update query.

[johnmerlino]

I just tried using CONCAT and it seems to support it:

UPDATE print_ready 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] & "*"));

However, a poup appears asking me to enter value for temp_query.first_name

[slave138]

That means it is not finding a field in temp_query called first_name (or it is not finding a table/query called temp_query)

[slave138]

I tried to recreate your scenario but I keep getting an error:

Undefined function 'CONCAT' in expression.

You may have some reference included which I do not, but it does not appear to be a native Access function.

[johnmerlino]

Ok I added the query table in design mode and it didn't give that popup. Does that do an implicit inner join? Because I see no inner join syntax added in sql mode. So I'm curious what magic is occuring when you simply add the query or table in design mode that creates the relationship. After all, there are no primary/foreign key connection in these two queries (tables).

[slave138]

I was able to replicate the desired result using:

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

I didn't use your exact WHERE expression because it seems like you would only get results if both the first_name and last_name fields match the names_1 field which didn't make a lot of sense to me.

[slave138]

I don't believe it is using a JOIN but the query is just comparing values from one table and updating the record if it finds one that matches the criteria. I would be curious to see what happens if there are two people with the same first and last names in the temp_query.

[johnmerlino]

I took a look at the query again because although the CONCAT worked (using Access 2010), it didn't seem to do it right:

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] & "*"));

It seems to be updating temp_query as well. I'm just looking for it to update the names_1 field of print_ready query with last,first, and middle initial fields of temp_query so that the names_1 field holds the concatenated strings of last_name field, first_name field, and middle_initial field of temp_query.
If I didn't add the temp_query to the new query I created which includes the above sql, then that's when the popup displays asking for a value for temp_query. So I am forced to add it, but then now it changes the sql, having it update that query as well. And I think that's why it's not working properly.

[slave138]

Is temp_query using print_ready as a datasource? I don't see anything which would indicate it should be updated, too. I haven't used 2010 yet so I don't know if it is unique to that version.

[johnmerlino]

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.

[slave138]

I'm still not seeing anything which would cause temp_query to update, too and I'm a little confused when you say temp_query is a field. Assuming temp_query is actually a query, what data source is it querying?

And from the way I'm reading the WHERE clause, it looks like it would only find a match if names_1 is like the first_name and last_name in temp_query (so "John" (names_1) would only match something like "John" (first_name) "Johnson" (last_name)). I assume this would result in a much smaller result set.

I didn't include a names_2 field or the same WHERE clause in my test DB, but I was able to replicate the functionality you described with the query I posted for you earlier. If I get a little time this afternoon, I'll try to recreate my test DB for you to see if it is replicating your scenario accurately.

[johnmerlino]

temp_query pulls information from an excel spreadsheet of last names and first names. Some of these names are somewhere in the names_1 or names_2 field of contacts. But contacts has a column called addresses which I need to associate with the temp_query first and last names. Hence, temp_query allows me to grab only the names from contacts that match the data source from excel. It gives me the addresses too. Problem with contacts is the names are jumbled in a field, so I want to update these fields with the exact first, last, and middle initials of the data from the excel that's held in temp_query.