Normalising Records

[Rob Brookbanks]

I want to improve my tables. At the moment, my supplier_ref is stored as a
text string. I have redesigned this so it just stores a unique SupplierID
and gets the suppliers name via an ODBC query at display time.

Most of my records currently still store the supplier_ref as a text string
and I want to update the column which has the SupplierID. As my text strings
match, I can produce a query which will draw up the same records, but I get
the error query needs to use an updateable recordset.

Once I have done this, I can delete the supplier_ref column and calculate
the rest at run time.

Any ideas what I am doing wrong?

I would have thought an update query would work, but I can't seem to get the
results I would like.

TIA,

Rob

 

[Roger Carlson]

In order to have an updateable recordset from Joined tables, the Join field
on the "one" side of the join MUST have a unique index on it. So you will
need to create a unique index on the supplier_ref text field in the lookup
table, THEN join that back to supplier_ref field. Once your recordset is
updateable, the Update query will work.