Record Numbering problem...

[Grim Reaper]

I know this should be easy (I think), but I can not figure out how to do
it.

Basically, I have 4 tables (2 tables with 2 subordinate tables) that
have an INQ_ID key that was created by using "Auto-increment".

(What I mean by 4 tables is that I have 2 "Main" tables with a
subordinate table tied to each of them.)

Now, I have to merge these tables so that I only have 2 tables (the data
is the same, the tables were created at different times, so, the
"Auto-increment" for the INQ_ID is overlapping).

This is basically my problem, I have about 20,000 records that the
INQ_ID overlaps. I need to figure out a way to change ONE of the tables
(and its subordinate table) so that the INQ_IDs are in a different range
of numbers so that when I merge the 4 tables (2 main, 2 subordinate),
the INQ_ID will not overlap.

I know I am not explaining it very well, but if there is any
clarification needed, just post the clarification question, and I will
follow it up ASAP because I really need to fix this (redundant to have 2
separate tables with the same fields in the database).

Thanks for any help,
Reaper

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rick Brandt]

"Grim Reaper" <re****@nospam.com> wrote in message
news:3f***********************@news.frii.net...

I know this should be easy (I think), but I can not figure out how to do
it.

Basically, I have 4 tables (2 tables with 2 subordinate tables) that
have an INQ_ID key that was created by using "Auto-increment".

(What I mean by 4 tables is that I have 2 "Main" tables with a
subordinate table tied to each of them.)

Now, I have to merge these tables so that I only have 2 tables (the data
is the same, the tables were created at different times, so, the
"Auto-increment" for the INQ_ID is overlapping).

This is basically my problem, I have about 20,000 records that the
INQ_ID overlaps. I need to figure out a way to change ONE of the tables
(and its subordinate table) so that the INQ_IDs are in a different range
of numbers so that when I merge the 4 tables (2 main, 2 subordinate),
the INQ_ID will not overlap.

I know I am not explaining it very well, but if there is any
clarification needed, just post the clarification question, and I will
follow it up ASAP because I really need to fix this (redundant to have 2
separate tables with the same fields in the database).

If you have cascade update turned on you should be able to change the AutoNumber
on one main table to a standard Long Integer. Then run an update query that
adds a fixed amount to all of those values so that the smallest value will now
be a bit larger than the largest AutoNumber value in the other main table. The
cascade update feature should make corresponding changes to the child table
values. Then you should be able to use append queries to add the revised
records into the unrevised tables.

Please try all of the above on a *copy* of your database.

--
*******************************
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
*******************************

[bag]

I may be misunderstanding your requirements here but...

you could create a new field on one of the tables which identifies
both unique identifers from each table by creating a new identifier
that is a concatenation of the two seperate primary fields.

record number Table one primary key + Table two primary key
New field
1 123 456
123456
2 341 445
341445
Howver when handling this field in queries or whatever you would have
to reference the relevent charaters via substrings, etc.

Apologies if this is not what you want.

Bag.

[Hugh N. Ross]

My 2 cents: I have always found it inconvenient to use autonumber for the
key on a table. I think is much better to define a primary key that is
related to the meaning of the data record. It simplifies the user specifying
what record is desired.
Hugh

"Grim Reaper" <re****@nospam.com> wrote in message
news:3f***********************@news.frii.net...

I know this should be easy (I think), but I can not figure out how to do
it.

Basically, I have 4 tables (2 tables with 2 subordinate tables) that
have an INQ_ID key that was created by using "Auto-increment".

(What I mean by 4 tables is that I have 2 "Main" tables with a
subordinate table tied to each of them.)

Now, I have to merge these tables so that I only have 2 tables (the data
is the same, the tables were created at different times, so, the
"Auto-increment" for the INQ_ID is overlapping).

This is basically my problem, I have about 20,000 records that the
INQ_ID overlaps. I need to figure out a way to change ONE of the tables
(and its subordinate table) so that the INQ_IDs are in a different range
of numbers so that when I merge the 4 tables (2 main, 2 subordinate),
the INQ_ID will not overlap.

I know I am not explaining it very well, but if there is any
clarification needed, just post the clarification question, and I will
follow it up ASAP because I really need to fix this (redundant to have 2
separate tables with the same fields in the database).

Thanks for any help,
Reaper

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Grim Reaper]

I tried to use the "cascade" option (this would have made it easier, so
that I could change the INQ_ID field in one table, and the child would
also change).

One problem, there is not necessarily data for each record. In other
words, parent table might have data, but the child might not. When I
tried to use the cascade update ("enforce referential integrity"), it
said that this was not possible because there has to be a record in the
child table with data in it corresponding to my INQ_ID field (primary
key for both tables).

Is there a way that I can "get around" this so that I can update both
tables at the same time? How? (the INQ_ID was an Auto-numbered field and
each table has the same corresponding Auto_number, except, in the child
table - there was not data for the child table).

Thanks, again,
Reaper

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rick Brandt]

"Grim Reaper" <re****@nospam.com> wrote in message
news:3f*********************@news.frii.net...

I tried to use the "cascade" option (this would have made it easier, so
that I could change the INQ_ID field in one table, and the child would
also change).

One problem, there is not necessarily data for each record. In other
words, parent table might have data, but the child might not. When I
tried to use the cascade update ("enforce referential integrity"), it
said that this was not possible because there has to be a record in the
child table with data in it corresponding to my INQ_ID field (primary
key for both tables).

Enforcing Referential Integrity with Cascade update or delete should require
that a record exist in the *parent* table. There should be no requirement that
every parent record have matches in the child table. Are you sure the error
wasn't that you have some records in the child table without matches in the
parent?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com