PK mess...

[KT]

It's nasty when you need to take somebody's work and modify
it...that's what I am doing.

This database i am working on has a main form and a subform. There
isn't any primary key nor relationship defined. The subform is linked
to the main form with the AccountNumber field (using Link child
field/Link master field). However, since it's not set as PK key, some
of the records today has blank AccountNumber.

I would like to add an AutoNumber field to these table as the PK. Is
there suggestions as to how I could clean up this mess???

Thanks a lot!

[Pieter Linden]

ka****@rocketmail.com (KT) wrote in message news:<fe***********************@posting.google.com >...

It's nasty when you need to take somebody's work and modify
it...that's what I am doing.

This database i am working on has a main form and a subform. There
isn't any primary key nor relationship defined. The subform is linked
to the main form with the AccountNumber field (using Link child
field/Link master field). However, since it's not set as PK key, some
of the records today has blank AccountNumber.

I would like to add an AutoNumber field to these table as the PK. Is
there suggestions as to how I could clean up this mess???

Thanks a lot!

Maybe create a new table structure with a PK field in it (Autonumber)
Append the UNIQUE values from the current parent table.
Create a new child table structure with the new numeric PK and the old
text pseudo PK or whatever. join the two tables together (probably an
outer join) and generate all the child records with the new FK, but
NOT as autonumber - you want that value to go into the FK (so it'll be
LONG instead of AutoIncrement/Unique). Append and you should be ready
to go.

And don't try this on a live copy. that could get you into some
serious hot water... and fast.

[Bernard Peek]

In message <fe***********************@posting.google.com>, KT
<ka****@rocketmail.com> writes

It's nasty when you need to take somebody's work and modify
it...that's what I am doing.

This database i am working on has a main form and a subform. There
isn't any primary key nor relationship defined. The subform is linked
to the main form with the AccountNumber field (using Link child
field/Link master field). However, since it's not set as PK key, some
of the records today has blank AccountNumber.

I would like to add an AutoNumber field to these table as the PK. Is
there suggestions as to how I could clean up this mess???

Adding an AutoNumber won't fix the problem, just hide it. As a short
term fix you could create a new dummy account just for those orphaned
records, and set their AccountNumber to the matching value. Then alter
the table to make AccountNumber required, so that new records can't be
created without one.

The real problem is that you have a number of orphan records. Whoever is
responsible for the data needs to tell you what account number to assign
to each and every one of those records or give you authority to delete
them. It's possible that when the records were entered without an
account they simply disappeared, and whoever entered the data would then
simply enter it again, hopefully getting it right the second time. If
that happened then you should find that for each bogus record there is a
record that has the same data, but with a valid AccountNumber.

This appears to be a management problem not just a technical one, and
you shouldn't just be looking for a technological fix. The data is
corrupt and it's up to the owners of the data to fix it. If the data in
this system is financial then someone somewhere has to sign a piece of
paper that tells you what to do with the orphaned data, and they should
be prepared to show that paper to the auditors.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.