redesign/reassign key that links dependent tables

[janaki]

OK, i created a problem for myself, and am trying to figure out how to fix it
(and wishing i could blame someone else for my lack of foresight!!!)

my database contains 9 tables (actually, 12, but 3 of them are just for use
by calculations in queries). the table, [Baseline], contains the bulk of the
information on individual record (one record per person). 2 of the others
are linked to provide grouping by location (city and country codes)-those
work great and aren't a problem. it's the other 6 tables, which i originally
had linked to [Baseline] by the autonumber field [Baseline].UID. i decided,
at the beginning, to use the autonumber because i couldn't rely on any user
entered data that would be reliably unique, and not create duplicate keys.

my problem now is that i have to merge data from 6 different countries,
where users have entered data into separate Access files....of course, the
autonumber is field is preventing the import of records with identical UID
values.

i patched a crummy work-around, which simply adds 1000, or 2000, or 3000,
etc to the UID in the db files that i'm importing from (after changing the
field properties from "autonumber" to "number", of course!), and then
importing into the merged db....but this obviously has limitations.

is there a better, more elegant way to fix this?? i am SO regretting using
autonumber now!!!

TIA-

 

[John Vinson]

is there a better, more elegant way to fix this?? i am SO regretting using
autonumber now!!!

You'll need to still change the Autonumber to a Long Integer, but you
can use a two-field Primary Key consisting of the UID and the Country
code.

John W. Vinson[MVP]

 

[Jeff Boyce]

If each "country's" database includes a field to indicate [Country], you
could create a new table that has (don't shriek!) an Autonumber primary key,
a [Country] field, and [CountryUID] field. Append the country-specific
records to the new table, with the UID (an autonumber) going into the
[CountryUID] field (a long integer) and the name of the country into
[Country]. The new record will get a (roughly) master Autonumber.

You will still be able to find all of one country's records, and the unique
ids assigned (via autonumber) in those locations.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[janaki]

could you clarify what you mean by a 2-field primary key?? this may answer
my question, i just don't know how to implement your suggestion....

 

[janaki]

Jeff's suggestion looks even closer to what i was thinking of doing.......i
actually have a field in the main table which is almost guaranteed to yield a
unique number (barring human error with input), and i was trying to figure
out how to pull that value into the dependent tables to replace the
autonumbered UID as the key..

i guess i was thinking about the solution backwards....rather than pushing a
key down to the dependent tables, it looks like you're suggesting creating a
higher level "parent" table which contain all the records, and assign new
UIDs to them?

 

[janaki]

another wrinkle on the problem: i just discovered that in my painful
workaround, i forgot about a second level of linked tables, dependent on the
first, which now seems to be totally randomly linked (really "un-linked") to
the resulting records in the main table.

my architecture was:

main table - UID generated by autonumber
->second level 1 - linked by UID
->second level 2 - linked by UID
->second level 3 - linked by UID
->second level 4 - linked by UID
->second level 5 - linked by UID, SID generated by autonumber
-> third level 1 - linked by SID to second level 5
-> third level 2 - linked by SID " " " "
-> third level 3 - linked by SID " " " "

i completely forgot about that SID, and now all those third level records
seem to be inconsistently linked with random records in the main table!!
ugh!!

John Vinson mentioned 2-field primary keys? can i link the third level to
the main table?? or to the second-level table #5 by 2-keys to keep it linked
to the correct main records?

 

[John Vinson]

could you clarify what you mean by a 2-field primary key?? this may answer
my question, i just don't know how to implement your suggestion....

A Table's Primary Key can be just one field - or it can be two, or
even ten. If you open the table in design view and ctrl-click two
fields, and click the Key icon, then those two fields jointly uniquely
identify the record. Either one of the fields is allowed to have
duplicate values - you just can't create a record if BOTH values
duplicate the values in another record.

John W. Vinson[MVP]

 

[janaki]

OK, i see.....so, then, do i include both those fields in the query that
links the dependent tables to the main table??

 

[John Vinson]

OK, i see.....so, then, do i include both those fields in the query that
links the dependent tables to the main table??

Yes. Each field should be joined to its corresponding foreign key
field in the child table; in the query design window you'll see two
join lines.

John W. Vinson[MVP]