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!

 

[Steve Schapel]

KT,

If I understand you correctly, I am sorry to say the answer is No. If
there is no data which you can use to identify which of the "child"
records is associated with which of the "master" records, then there is
no way you can build this association. The association is already in
place with the records where there is an entry in the AccountNumber
field, but where there is no AccountNumber you have a problem.
Depending on your business processes, it may be appropriate for you to
enter an AccountNumber in the blank "master" records, but I assume you
still will not be able to know which of the "child" records are related.
Adding an AutoNumber field will not help, and I would see no benefit
in doing this... your AccountNumber field is perfectly suitable to be
used as the Primary Key in the master table. However, you should set
its Validation Rule property to...
Is Not Null
.... to ensure that a record can't be created with a blank AccountNumber.

 

[Arvin Meyer]

Add the Autonumber field to the 1 side table and a corresponding Long
Integer field in the many-side table. Link the existing keys in a query and
update the empty Table2 foreign key field with the matching autonumber. You
will not be able to resolve anything which has no existing link, so when
you're done, if there are any records which don't match on the many-side,
you might as well delete them. Better yet, make a copy of the database and
archive it before you start, then you can always try to reconstruct anything
that you delete later.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[KT]

Thank you! Both Steve and Arvin! I think I will just delete those
records that don't tie to anything...cos they wouldn't make sense
anyway!

Many thanks!

Kanley