Rex wrote:
I know this is not possible in table itself.. However I presume it
would be possible to do this in a form. But I dont know HOW
You *don't* want to do this in a form. Why not? Look at your schema.
Whether you have 1 or 4 or 50 records with an ID of 1, the Name is *always*
going to be xyz, or whatever you edit it to be in the future. (Name is not a
good name for a column by the way, as it's a reserved keyword.)
That's redundant data. You have a 1:1 relationship between ID and Name,
meaning it can go into a separate lookup table (the parent for this child
table) and either value can be placed in the current table as the foreign key.
That is, if you like to use surrogate keys. Otherwise, you can drop the
numeric ID column and keep the Name column natural key in the current table
and forget about creating a separate parent table, unless there are
additional attributes that need to be moved from the current table to the
parent table to normalize it.
If OTH you are in the middle of normalizing an imported spreadsheet and the
form is just one of the steps in transforming the data, then you could
instead use a single update query to set the corresponding values in all
records with an equijoin on the current table like this:
UPDATE tblCompanies AS C1 INNER JOIN
tblCompanies AS C2 ON C1.ID = C2.ID
SET C1.CoName = C2.CoName
WHERE (ISNULL(C2.CoNa me) = FALSE);
And you could then extract the records to create the lookup/parent table with
a make table query like this:
SELECT DISTINCT ID, CoName INTO tblCoNames
FROM tblCompanies;
But my recommendation is to transform an imported spreadsheet with this
structure by cutting out the intermediate step and just create the
lookup/parent table with a make table query like this (without first
assigning values to the empty columns):
SELECT DISTINCT ID, CoName INTO tblCoNames
FROM tblCompanies
WHERE (ISNULL(CoName) = FALSE);
If you like to use surrogate keys, that is. Skip the ID column if you prefer
natural keys.
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200610/1 
