Help Please

[golfinray]

We have had school districts consolidate so now Altheimer and Dollarway
School Districts are now joined and are just Dollarway School District. Our
ID number for Altheimer was 3501. Our ID for Dollarway is 3205. So Altheimer
construction projects were labeled 0607-3501-001, 0911-3501-016, etc.
Dollarway projects are numbered 0607-3205-012 or 0708-3205-008. So now, when
I pull up Dollarway School District on my construction tracker form or
construction approval forms, I need Altheimer's projects to appear also. We
don't want to change project numbers. Project numbers are my PK. Help please!
Thanks!!

 

[Beetle]

Don't you have a foreign key in the table of construction
projects relating back to the table of schools? Can't you just
update the FK field?

Update tblProjects Set SchoolID = 3205 Where SchoolID = 3501;

 

[golfinray]

I can't change project numbers, beetle. Wouldn't that change my project
number are I am off base there?

 

[Beetle]

I guess that depends on how your project number is derived.

Is it a calculated field that combines the fk field (SchoolID) and
data from other fields?

Is it a multi-field PK?

 

[golfinray]

Thanks, Beetle. It is not a calculated field. It is multi-field. Thanks, Milt

 

[Beetle]

Well, that's a bit problematic. To solve this issue, and to prevent
it from happening again in the future (if more school districts merge),
your best option may be to add a new field to your projects table
that will become the new fk field, yet leaving your existing Compound
Key intact.

You would then use an update query to place the values from the
existing fk field into the new fk field, followed by another update
query that would modify any incorrect values in the new fk field
(like the example in my previous post).

You could then re-establish the relationship based on the new fk
field. You would of course need to modify any associated queries,
forms and reports to reflect the new relationship and likely create
some code to insert the value from the new fk field into the old
fk field for new records (the old fk field would now only serve the
purpose of creating part of the project number).

This will be a bit of work, and will result in redundant data
in your table, but if you have a lot of existing data and you
must keep the existing project numbers, it may be your only option.

Maybe someone else will jump in with another idea, but based on
my understanding of your issue, I can't think of a better way right now.

This is a good example of why not to use Key fields as part of
any real world user data.

 

[golfinray]

I will probably just re-desgn my tables then Beetle. That might be easier.
Thanks for the help!!! Milt