Update foreign key before appending records to central database

[PC Datasheet]

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be completed
for each inspection and the forms would be sent to a central location to be
entered into the database. 50 to 250 inspections would be done at a site and
50 to 250 forms would be entered into the database for each inspection.
There are approximately 70,000 records in the database currently. The number
of inspections has quickly grown to where it will soon be impossible to
enter all the forms into one central database. The plan now is to enter the
inspections into a dozen satellite databases consisting of TblOwner and
TblInspection and then after each inspection send the database file to the
central database location and append the records in TblOwner and
TblInspection into the same named tables in the central database. The
sattelite database location would then be sent an empty satellite database
to repeat the process. OwnerID and InspectionID would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite database
arrives, the OwnerID in TblOwner will start at 1 and end at 250.
Correspondingly, the foreign key, OwnerID in TblInspection, will contain
values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
will be appended to TblOwner in the central database. The Owner records will
have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs to be
updated to the corresponding OwnerID values (70001 to 70250) before the
Inspection records can be appended. The values OwnerID need to be updated to
will continue to increase as each sattelite database arrives. I need
suggestions on how to determine what values to update OwnerID in
TblInspection in the sattelite database to considering that OwnerID in the
TblOwner in the sattelite database may not be contiguous due to the nature
that it's an autonumber field.

Thanks,

Steve

[pietlinden]

Does order of the OwnerID really matter, or can you just create a
random ID for each customer? If you can use random numbers for the
IDs, then I see no reason why creating another field in the parent and
child tables is that big of a deal. Then you can populate the parent
values and cascade them to the children using the existing
relationships. Then if you use the RandomID as your PK in the
different databases, you shouldn't have a problem...

or did I miss a key element here?

[david epsom dot com dot au]

Create a set of staging tables with cascading updates
from the primary key field. Import the records to the
staging tables, update the primary key values so that
they don't clash with existing values, then import to
the destination table.

Or use a multifield primary key with a site index.

Or use replication.

Or use random autonumber if you have only 2 or 3 sites.

(replication uses guid's instead of random longs for
more replication sites, but you will regret it if you
try to use guid autonumbers yourself: they are poorly
supported in queries and code)

(david)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:B_******************@newsread2.news.atl.earth link.net...

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be completed
for each inspection and the forms would be sent to a central location to
be entered into the database. 50 to 250 inspections would be done at a
site and 50 to 250 forms would be entered into the database for each
inspection. There are approximately 70,000 records in the database
currently. The number of inspections has quickly grown to where it will
soon be impossible to enter all the forms into one central database. The
plan now is to enter the inspections into a dozen satellite databases
consisting of TblOwner and TblInspection and then after each inspection
send the database file to the central database location and append the
records in TblOwner and TblInspection into the same named tables in the
central database. The sattelite database location would then be sent an
empty satellite database to repeat the process. OwnerID and InspectionID
would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite
database arrives, the OwnerID in TblOwner will start at 1 and end at 250.
Correspondingly, the foreign key, OwnerID in TblInspection, will contain
values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
will be appended to TblOwner in the central database. The Owner records
will have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs
to be updated to the corresponding OwnerID values (70001 to 70250) before
the Inspection records can be appended. The values OwnerID need to be
updated to will continue to increase as each sattelite database arrives. I
need suggestions on how to determine what values to update OwnerID in
TblInspection in the sattelite database to considering that OwnerID in the
TblOwner in the sattelite database may not be contiguous due to the nature
that it's an autonumber field.

Thanks,

Steve

[Steve Jorgensen]

My favorite strategy is to use a composite key in the central database that
includes a batch ID and the ID value generated in the remote database. You
generally want the batch ID anyway, so it's not an extra field, though it is a
larger PK index.

On Mon, 12 Sep 2005 14:09:37 GMT, "PC Datasheet" <no****@nospam.spam> wrote:

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be completed
for each inspection and the forms would be sent to a central location to be
entered into the database. 50 to 250 inspections would be done at a site and
50 to 250 forms would be entered into the database for each inspection.
There are approximately 70,000 records in the database currently. The number
of inspections has quickly grown to where it will soon be impossible to
enter all the forms into one central database. The plan now is to enter the
inspections into a dozen satellite databases consisting of TblOwner and
TblInspection and then after each inspection send the database file to the
central database location and append the records in TblOwner and
TblInspection into the same named tables in the central database. The
sattelite database location would then be sent an empty satellite database
to repeat the process. OwnerID and InspectionID would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite database
arrives, the OwnerID in TblOwner will start at 1 and end at 250.
Correspondingly, the foreign key, OwnerID in TblInspection, will contain
values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
will be appended to TblOwner in the central database. The Owner records will
have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs to be
updated to the corresponding OwnerID values (70001 to 70250) before the
Inspection records can be appended. The values OwnerID need to be updated to
will continue to increase as each sattelite database arrives. I need
suggestions on how to determine what values to update OwnerID in
TblInspection in the sattelite database to considering that OwnerID in the
TblOwner in the sattelite database may not be contiguous due to the nature
that it's an autonumber field.

Thanks,

Steve

[PC Datasheet]

David,

Thanks for responding. Could you give more details about your staging tables
concept.

Thanks!

Steve
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:43***********************@lon-reader.news.telstra.net...

Create a set of staging tables with cascading updates
from the primary key field. Import the records to the
staging tables, update the primary key values so that
they don't clash with existing values, then import to
the destination table.

Or use a multifield primary key with a site index.

Or use replication.

Or use random autonumber if you have only 2 or 3 sites.

(replication uses guid's instead of random longs for
more replication sites, but you will regret it if you
try to use guid autonumbers yourself: they are poorly
supported in queries and code)

(david)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:B_******************@newsread2.news.atl.earth link.net...

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be
completed for each inspection and the forms would be sent to a central
location to be entered into the database. 50 to 250 inspections would be
done at a site and 50 to 250 forms would be entered into the database for
each inspection. There are approximately 70,000 records in the database
currently. The number of inspections has quickly grown to where it will
soon be impossible to enter all the forms into one central database. The
plan now is to enter the inspections into a dozen satellite databases
consisting of TblOwner and TblInspection and then after each inspection
send the database file to the central database location and append the
records in TblOwner and TblInspection into the same named tables in the
central database. The sattelite database location would then be sent an
empty satellite database to repeat the process. OwnerID and InspectionID
would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite
database arrives, the OwnerID in TblOwner will start at 1 and end at 250.
Correspondingly, the foreign key, OwnerID in TblInspection, will contain
values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
will be appended to TblOwner in the central database. The Owner records
will have OwnerID values of 70001 to 70250. OwnerID in TblInspection
needs to be updated to the corresponding OwnerID values (70001 to 70250)
before the Inspection records can be appended. The values OwnerID need to
be updated to will continue to increase as each sattelite database
arrives. I need suggestions on how to determine what values to update
OwnerID in TblInspection in the sattelite database to considering that
OwnerID in the TblOwner in the sattelite database may not be contiguous
due to the nature that it's an autonumber field.

Thanks,

Steve

[PC Datasheet]

Steve,

Thank you for responding! Your startegy seems like it would be good if
starting from scratch but im this case the first batch of owner records will
be appended to the central database and their OwnerIDs will become 70001 to
70250. The foreign key in the inspection records must then take on these
same values.

Steve
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:gi********************************@4ax.com...

My favorite strategy is to use a composite key in the central database
that
includes a batch ID and the ID value generated in the remote database.
You
generally want the batch ID anyway, so it's not an extra field, though it
is a
larger PK index.

On Mon, 12 Sep 2005 14:09:37 GMT, "PC Datasheet" <no****@nospam.spam>
wrote:

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be completed
for each inspection and the forms would be sent to a central location to
be
entered into the database. 50 to 250 inspections would be done at a site
and
50 to 250 forms would be entered into the database for each inspection.
There are approximately 70,000 records in the database currently. The
number
of inspections has quickly grown to where it will soon be impossible to
enter all the forms into one central database. The plan now is to enter
the
inspections into a dozen satellite databases consisting of TblOwner and
TblInspection and then after each inspection send the database file to the
central database location and append the records in TblOwner and
TblInspection into the same named tables in the central database. The
sattelite database location would then be sent an empty satellite database
to repeat the process. OwnerID and InspectionID would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite
database
arrives, the OwnerID in TblOwner will start at 1 and end at 250.
Correspondingly, the foreign key, OwnerID in TblInspection, will contain
values of 1 to 250. When TblOwner is appended, all the fields but OwnerID
will be appended to TblOwner in the central database. The Owner records
will
have OwnerID values of 70001 to 70250. OwnerID in TblInspection needs to
be
updated to the corresponding OwnerID values (70001 to 70250) before the
Inspection records can be appended. The values OwnerID need to be updated
to
will continue to increase as each sattelite database arrives. I need
suggestions on how to determine what values to update OwnerID in
TblInspection in the sattelite database to considering that OwnerID in the
TblOwner in the sattelite database may not be contiguous due to the nature
that it's an autonumber field.

Thanks,

Steve

[david epsom dot com dot au]

Make a copy of the source tables. You can use the
source database, destination database, or third database.

Make sure the tables have cascading update defined
on the relationships. (Change if necessary)
Create an update query for the primary table, which
updates the primary key to [original value] + [offset]
Get the offset in the query by using DMAX() on the
destination table. The cascading update will fix
the dependant tables.

After you have updated the staging tables,
append to the destination tables

Then delete the values from the staging tables,
or discard the database.

(david)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:7B******************@newsread2.news.atl.earth link.net...

David,

Thanks for responding. Could you give more details about your staging
tables concept.

Thanks!

Steve
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:43***********************@lon-reader.news.telstra.net...

Create a set of staging tables with cascading updates
from the primary key field. Import the records to the
staging tables, update the primary key values so that
they don't clash with existing values, then import to
the destination table.

Or use a multifield primary key with a site index.

Or use replication.

Or use random autonumber if you have only 2 or 3 sites.

(replication uses guid's instead of random longs for
more replication sites, but you will regret it if you
try to use guid autonumbers yourself: they are poorly
supported in queries and code)

(david)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:B_******************@newsread2.news.atl.earth link.net...

Looking for suggestions ----

A database was designed for a national automobile inspection program. In
it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be
completed for each inspection and the forms would be sent to a central
location to be entered into the database. 50 to 250 inspections would be
done at a site and 50 to 250 forms would be entered into the database
for each inspection. There are approximately 70,000 records in the
database currently. The number of inspections has quickly grown to where
it will soon be impossible to enter all the forms into one central
database. The plan now is to enter the inspections into a dozen
satellite databases consisting of TblOwner and TblInspection and then
after each inspection send the database file to the central database
location and append the records in TblOwner and TblInspection into the
same named tables in the central database. The sattelite database
location would then be sent an empty satellite database to repeat the
process. OwnerID and InspectionID would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite
database arrives, the OwnerID in TblOwner will start at 1 and end at
250. Correspondingly, the foreign key, OwnerID in TblInspection, will
contain values of 1 to 250. When TblOwner is appended, all the fields
but OwnerID will be appended to TblOwner in the central database. The
Owner records will have OwnerID values of 70001 to 70250. OwnerID in
TblInspection needs to be updated to the corresponding OwnerID values
(70001 to 70250) before the Inspection records can be appended. The
values OwnerID need to be updated to will continue to increase as each
sattelite database arrives. I need suggestions on how to determine what
values to update OwnerID in TblInspection in the sattelite database to
considering that OwnerID in the TblOwner in the sattelite database may
not be contiguous due to the nature that it's an autonumber field.

Thanks,

Steve

[PC Datasheet]

Thanks, David, appreciate the help!

Steve

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:43***********************@lon-reader.news.telstra.net...

Make a copy of the source tables. You can use the
source database, destination database, or third database.

Make sure the tables have cascading update defined
on the relationships. (Change if necessary)
Create an update query for the primary table, which
updates the primary key to [original value] + [offset]
Get the offset in the query by using DMAX() on the
destination table. The cascading update will fix
the dependant tables.

After you have updated the staging tables,
append to the destination tables

Then delete the values from the staging tables,
or discard the database.

(david)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:7B******************@newsread2.news.atl.earth link.net...

David,

Thanks for responding. Could you give more details about your staging
tables concept.

Thanks!

Steve
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:43***********************@lon-reader.news.telstra.net...

Create a set of staging tables with cascading updates
from the primary key field. Import the records to the
staging tables, update the primary key values so that
they don't clash with existing values, then import to
the destination table.

Or use a multifield primary key with a site index.

Or use replication.

Or use random autonumber if you have only 2 or 3 sites.

(replication uses guid's instead of random longs for
more replication sites, but you will regret it if you
try to use guid autonumbers yourself: they are poorly
supported in queries and code)

(david)

"PC Datasheet" <no****@nospam.spam> wrote in message
news:B_******************@newsread2.news.atl.earth link.net...
Looking for suggestions ----

A database was designed for a national automobile inspection program.
In it's simplest form, the database has two tables:
TblOwner
OwnerID
<Year/Make/Model owned by owner, Owner name/address, etc)

TblInspection
InspectionID
OwnerID
<~50 inspection results>

Originally, inspections would be done at a site, a form would be
completed for each inspection and the forms would be sent to a central
location to be entered into the database. 50 to 250 inspections would
be done at a site and 50 to 250 forms would be entered into the
database for each inspection. There are approximately 70,000 records in
the database currently. The number of inspections has quickly grown to
where it will soon be impossible to enter all the forms into one
central database. The plan now is to enter the inspections into a dozen
satellite databases consisting of TblOwner and TblInspection and then
after each inspection send the database file to the central database
location and append the records in TblOwner and TblInspection into the
same named tables in the central database. The sattelite database
location would then be sent an empty satellite database to repeat the
process. OwnerID and InspectionID would start over at 1.

The problem is in the appending process. With 70,000 records, the last
OwnerID is 70,000 at this point in time. When the first sattellite
database arrives, the OwnerID in TblOwner will start at 1 and end at
250. Correspondingly, the foreign key, OwnerID in TblInspection, will
contain values of 1 to 250. When TblOwner is appended, all the fields
but OwnerID will be appended to TblOwner in the central database. The
Owner records will have OwnerID values of 70001 to 70250. OwnerID in
TblInspection needs to be updated to the corresponding OwnerID values
(70001 to 70250) before the Inspection records can be appended. The
values OwnerID need to be updated to will continue to increase as each
sattelite database arrives. I need suggestions on how to determine what
values to update OwnerID in TblInspection in the sattelite database to
considering that OwnerID in the TblOwner in the sattelite database may
not be contiguous due to the nature that it's an autonumber field.

Thanks,

Steve