Impossible to fix??? relationship problem

[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

 

[Cheryl Fischer]

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.

You definitely can.

 

[Cheryl Fischer]

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

 

[Ric Passey]

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

 

[Ric Passey]

sorry found it (update query that is)

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

 

[Ric Passey]

got the query to work. just have to pick through forms etc to fix the screw
ups

 

[Cheryl Fischer]

Good!

Good luck with your project.