Hi,
I've inherited the management of a relatively complex database (~15 related tables, union queries, &c.) that hinges on a few basic primary keys.
I have split the database, and distributed the front end client to other users.
The most important primary key is that assigned to each client. It takes the numerical form XXXX-XXX. Typically, the final three digits are 001. When the database was in its infancy, many of the first entries skipped these last three digits, and just entered XXXX-.
It is now necessary to go back and make sure all entries are complete. They must have the last three digits. I tried going into the back end, and editing them straightforwardly, however, it tells me that it is 'related' and cannot be edited. Not only is it a PK, it is also an FK on 4 other tables, and in a union query.
I tried to edit all of the involved relationships to 'cascade update related fields,' but when I did that I got the following error:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
Is there a way to edit these primary keys and have them push out to all the clients and forms etc? Maybe by using queries or appends? I have no idea.
Much obliged,
Jay
I've inherited the management of a relatively complex database (~15 related tables, union queries, &c.) that hinges on a few basic primary keys.
I have split the database, and distributed the front end client to other users.
The most important primary key is that assigned to each client. It takes the numerical form XXXX-XXX. Typically, the final three digits are 001. When the database was in its infancy, many of the first entries skipped these last three digits, and just entered XXXX-.
It is now necessary to go back and make sure all entries are complete. They must have the last three digits. I tried going into the back end, and editing them straightforwardly, however, it tells me that it is 'related' and cannot be edited. Not only is it a PK, it is also an FK on 4 other tables, and in a union query.
I tried to edit all of the involved relationships to 'cascade update related fields,' but when I did that I got the following error:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
Is there a way to edit these primary keys and have them push out to all the clients and forms etc? Maybe by using queries or appends? I have no idea.
Much obliged,
Jay
