A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help Please



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 03:15 PM posted to microsoft.public.access.forms
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old April 28th, 2010, 03:58 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old April 28th, 2010, 04:14 PM posted to microsoft.public.access.forms
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old April 28th, 2010, 05:11 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old April 28th, 2010, 06:15 PM posted to microsoft.public.access.forms
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old April 28th, 2010, 07:03 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old April 28th, 2010, 07:31 PM posted to microsoft.public.access.forms
Golfinray
external usenet poster
 
Posts: 1,597
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.