Must use updatetable query - work around

[NuBie via AccessMonster.com]

I want to set SNumber field of Table01 using a concatenation of two fields
Fsn and AllFsn from Table03. Fsn link these two tables, below is my query
however I'm getting a Must use updatetable query. Any work around on this?

UPDATE Table1 set SNumber = (SELECT Fsn & AllFsn FROM Table03 WHERE Table1.
Fsn = Table03.Fsn)


Thanks,

 

[KARL DEWEY]

Try this --
UPDATE [Table1] INNER JOIN [Table03] ON [Table1].[Fsn] = [Table03].[Fsn] SET
[Table1].[SNumber] = [Table03].[Fsn]&[Table03].[AllFsn];

Or try it with two queries --
qryConCat --
SELECT [Fsn], [Fsn] & [AllFsn] AS Alias_1
FROM Table03;

UPDATE [Table1] INNER JOIN [qryConCat] ON [Table1].[Fsn] = [qryConCat].[Fsn]
SET [Table1].[SNumber] = [qryConCat].[Alias_1];

 

[John W. Vinson]

I want to set SNumber field of Table01 using a concatenation of two fields
Fsn and AllFsn from Table03.

Why?

Storing two pieces of information from one table, redundantly in a single
field of another table, violates two basic rules of relational design!

What's in AllFsn? What result do you want if one value of Fsn corresponds to
several different values of AllFsn?

Fsn link these two tables, below is my query
however I'm getting a Must use updatetable query. Any work around on this?

UPDATE Table1 set SNumber = (SELECT Fsn & AllFsn FROM Table03 WHERE Table1.
Fsn = Table03.Fsn)

Use a Join:

UPDATE Table1 INNER JOIN Table3
ON Table1.Fsn = Table3.Fsn
SET Table1.Snumber = Table3.Fsn & Table3.AllFsn;

THis assumes that there will be only one matching record, and that you
actually want to violate the rules and risk data anomalies in your database.

 

[NuBie via AccessMonster.com]

Thank you both Karl & John,

What's in AllFsn?

Fsn and AllFsn combined together is like an ID Number to a product (let's
call it Nsn), however a COBOL application separated the Nsn to two parts 1.
the Fsn which is the first four digit of Nsn and 2. the AllFsn which is the
remaining part of Nsn. Now , i need this two combined to get the full Nsn in
my VB Application.

What result do you want if one value of Fsn corresponds to several different values of AllFsn?

Fsn is always unique to AllFsn, however one AllFsn may have two different Fsn.

 

[John W. Vinson]

Did this work? It should if Fsn has a unique Index in Table1 (or, ideally, in
both tables).

 

[NuBie via AccessMonster.com]

Yes, it did. Thank you John.

Did this work? It should if Fsn has a unique Index in Table1 (or, ideally, in
both tables).