If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help Please
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!! -- Milton Purdy ACCESS State of Arkansas |
#2
|
|||
|
|||
Help Please
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; -- _________ Sean Bailey "golfinray" wrote: 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!! -- Milton Purdy ACCESS State of Arkansas |
#3
|
|||
|
|||
Help Please
I can't change project numbers, beetle. Wouldn't that change my project
number are I am off base there? -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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; -- _________ Sean Bailey "golfinray" wrote: 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!! -- Milton Purdy ACCESS State of Arkansas |
#4
|
|||
|
|||
Help Please
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? -- _________ Sean Bailey "golfinray" wrote: I can't change project numbers, beetle. Wouldn't that change my project number are I am off base there? -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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; -- _________ Sean Bailey "golfinray" wrote: 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!! -- Milton Purdy ACCESS State of Arkansas |
#5
|
|||
|
|||
Help Please
Thanks, Beetle. It is not a calculated field. It is multi-field. Thanks, Milt
-- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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? -- _________ Sean Bailey "golfinray" wrote: I can't change project numbers, beetle. Wouldn't that change my project number are I am off base there? -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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; -- _________ Sean Bailey "golfinray" wrote: 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!! -- Milton Purdy ACCESS State of Arkansas |
#6
|
|||
|
|||
Help Please
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. -- _________ Sean Bailey "golfinray" wrote: Thanks, Beetle. It is not a calculated field. It is multi-field. Thanks, Milt -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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? -- _________ Sean Bailey "golfinray" wrote: I can't change project numbers, beetle. Wouldn't that change my project number are I am off base there? -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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; -- _________ Sean Bailey "golfinray" wrote: 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!! -- Milton Purdy ACCESS State of Arkansas |
#7
|
|||
|
|||
Help Please
I will probably just re-desgn my tables then Beetle. That might be easier.
Thanks for the help!!! Milt -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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. -- _________ Sean Bailey "golfinray" wrote: Thanks, Beetle. It is not a calculated field. It is multi-field. Thanks, Milt -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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? -- _________ Sean Bailey "golfinray" wrote: I can't change project numbers, beetle. Wouldn't that change my project number are I am off base there? -- Milton Purdy ACCESS State of Arkansas "Beetle" wrote: 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; -- _________ Sean Bailey "golfinray" wrote: 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!! -- Milton Purdy ACCESS State of Arkansas |
Thread Tools | |
Display Modes | |
|
|