Impossible to fix??? relationship problem

  • Thread starter Thread starter Ric Passey
  • Start date Start date
R

Ric Passey

Dear All,

I inherited a large purchasing database when I started a new job. Whoever
wrote it had not been to the database school that I went to so they had used
some odd things as primary key. There are alot of problems but the biggest
is that the supplier table has used the supplier name as the PK. Also
somewhat confusingly there is no relationship setup (I don't know how it is
held together). This means that supplier name cannot be changed without
breaking the relationship between the suppliers and the order. So if a
supplier changes its name all orders made by that supplier become blank.

The only work around is to make a new company and start again, which makes
life very hard for the guys doing the ordering if they need to check back at
a later date.

The ideal solution would eb to setup an autonumber as a pK and add a
relationship to an interger field in the related table. Obviously this is
where things get a little complicated. With 800 suppliers and 25000 orders
manually doing this would be a little time consuming. If anyone has an
automated approach I would be most grateful.

So is there some natty wizard someone has written or a clever piece of code
if so let me know.

Thanks,

Ric Passey
 
Start at the beginning...the supplier table. Run a distinct query on the
supplier name to see if in fact there are no true duplicates and resolve
them if there are...

Once you have that, then add a PK column of number (I don't recall if you
can add an Autonumber once there is already data in the table) to each
supplier.

Now, create an update query to update each row in the Order table with the
number from the Supplier table using the supplier name as the join field.
If everything checks out, drop the supplier name field from the Order table
and change the PK on the Supplier table to the new number field.

You will probably have some forms, events, etc. to change as well.

Of course, back everything up, and do all of this on copies of the data.

Good luck!

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Once you have that, then add a PK column of number (I don't recall if you
can add an Autonumber once there is already data in the table) to each
supplier.
Click to expand...

You definitely can.
 
Yes. Once data is entered into a table, you can't change a field to
AutoNumber from something else.
 
OK this sounds promising (I am overwhelmed at such a quick repsonse)

I've made the new field in suppliers and populated it as autonumber and
assigned it as PK (supplier_ID).

Made a new field in orders called supplier_Id (as number).

I'm a little hazy on the whole update query, could you point me in the right
direction.

Cheers,

Ric
 
sorry found it (update query that is)

Cheryl Fischer said:
Yes. Once data is entered into a table, you can't change a field to
AutoNumber from something else.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

if has piece
Click to expand...
 
Back
Top