Update Table Column off of Query Column

Question

I am trying to Update a column in my table Inputcounts called concatenate off of a query called InputConcatenates that has a column also called concatenate. I am running an update query with the field name as concatenate the table name as InputCounts and the update to field as [InputConcatenates].[Concatenate]. But every time I run the query it pulls back that 0 records will be updated. Is my syntax wrong possibly?

Update Query SQL:

UPDATE InputCounts INNER JOIN InputConcatenate
ON InputCounts.CONCATENATE = InputConcatenate.CONCATENATE 
SET InputCounts.CONCATENATE = [InputConcatenate].[CONCATENATE];

InputConcatenate Query SQL:

SELECT InputCounts.FLEET, InputCounts.AMMs, [FLEET] & [AMMs] AS CONCATENATE
FROM InputCounts;

Answer 1

You reported this query accomplishes what you want ...

UPDATE InputCounts
SET CONCATENATE = [FLEET] & [AMMs]
WHERE CONCATENATE Is Null;

That may be fine. However CONCATENATE is not updated until you execute the UPDATE, and does not get updated (after having previously received a value) in response to changes in FLEET or AMMs

Decide whether CONCATENATE really needs to exist as a field in your table. You could use a query to derive it whenever you need it:

SELECT *, FLEET] & [AMMs] AS CONCATENATE
FROM InputCounts;

With the query, CONCATENATE will always be up to date.

If your database is ACCDB format and your Access version is >= 2010, another possibility is to make CONCATENATE a "calculated field" type in the table's design:

If you prefer CONCATENATE be Null whenever FLEET or AMMs is Null, change the field's Expression property to [FLEET] + [AMMs]

The advantage of a calculated field is that Access automagically updates its value without further effort (like executing an UPDATE) from you.

A disadvantage is that you can't index a calculated field. That means it's not suited for joins, WHERE criteria, ORDER BY, etc. You'll have to decide whether it's a reasonable fit for your application. :-)