Normalising Records

  • Thread starter Thread starter Rob Brookbanks
  • Start date Start date
R

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
 
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.
 
Back
Top