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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Not to Mess Up by Replacing Relationship with Different Primery Ke



 
 
Thread Tools Display Modes
  #1  
Old July 15th, 2009, 08:52 PM posted to microsoft.public.access.tablesdbdesign
A.P.[_2_]
external usenet poster
 
Posts: 15
Default Not to Mess Up by Replacing Relationship with Different Primery Ke

Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many between
them. A field type is number so you have to enter it manually. I messed up as
I added a number outside the numeric sequence and followed it up. So instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna
  #2  
Old July 15th, 2009, 11:26 PM posted to microsoft.public.access.tablesdbdesign
bhicks11 via AccessMonster.com
external usenet poster
 
Posts: 529
Default Not to Mess Up by Replacing Relationship with Different Primery Ke

Here's one way -

Create your autonum in the master table (don't change your relationship yet).
Add a numeric field to the child table, run an update query that adds the
value from the master autonum field to the new numeric field in the child
table. Because the tables are still correctly related with the old numeric
field you will have the correct number added to the new numeric field. Then
you can change your relationship to the master.autonum and child.newnumeric
fields.

Bonnie
http://www.dataplus-svc.com

A.P. wrote:
Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many between
them. A field type is number so you have to enter it manually. I messed up as
I added a number outside the numeric sequence and followed it up. So instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna


--
Message posted via http://www.accessmonster.com

  #3  
Old July 15th, 2009, 11:26 PM posted to microsoft.public.access.tablesdbdesign
bhicks11 via AccessMonster.com
external usenet poster
 
Posts: 529
Default Not to Mess Up by Replacing Relationship with Different Primery Ke

Don't forget to back up first!

A.P. wrote:
Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many between
them. A field type is number so you have to enter it manually. I messed up as
I added a number outside the numeric sequence and followed it up. So instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1

  #4  
Old July 15th, 2009, 11:35 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default Not to Mess Up by Replacing Relationship with Different Primery Ke

Hi Anna

First, if you want your customer id values in order with no gaps, then an
autonumber is NOT the way to go. Autonumbers are not really intended for
human consumption. They might as well be (and in fact can be) completely
random. Even with incremental autonumbers, you can get gaps when a record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database file in
case you mess up. Also, ensure that the Name Autocorrect option is turned
of.

2. Open your table in design view and add a new field with the same name as
the old primary key field, and with data type Autonumber. Rename the old
field to "OldPK" but do not change the PK yet. Save the table. When you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as the old
foreign key field in the relationship, and with data type numeric. Set its
Size property to Long and delete the "0" from the DefaultValue property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join on the
OldPK and OldFK fields). Change the query to an update query and add the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the relationships
window and delete the relationships between the main table and each related
table.

7. Open the main table in design view and change the PK to the new field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before, except of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I messed up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna



  #5  
Old July 16th, 2009, 12:21 AM posted to microsoft.public.access.tablesdbdesign
A.P.[_2_]
external usenet poster
 
Posts: 15
Default Not to Mess Up by Replacing Relationship with Different Primer

Hi Again

Can you explain me how I can update it ? What should I write down in a query?

Thank you

A.P.

"bhicks11 via AccessMonster.com" wrote:

Here's one way -

Create your autonum in the master table (don't change your relationship yet).
Add a numeric field to the child table, run an update query that adds the
value from the master autonum field to the new numeric field in the child
table. Because the tables are still correctly related with the old numeric
field you will have the correct number added to the new numeric field. Then
you can change your relationship to the master.autonum and child.newnumeric
fields.

Bonnie
http://www.dataplus-svc.com

A.P. wrote:
Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many between
them. A field type is number so you have to enter it manually. I messed up as
I added a number outside the numeric sequence and followed it up. So instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna


--
Message posted via http://www.accessmonster.com


  #6  
Old July 16th, 2009, 12:58 AM posted to microsoft.public.access.tablesdbdesign
A.P.[_2_]
external usenet poster
 
Posts: 15
Default Not to Mess Up by Replacing Relationship with Different Primer

Hi Graham

But after doing it, can I still update my table by adding new customers?

I will check this out tomorrow

If I have any problems, I knock at your doors.

Thanks

Anna
"Graham Mandeno" wrote:

Hi Anna

First, if you want your customer id values in order with no gaps, then an
autonumber is NOT the way to go. Autonumbers are not really intended for
human consumption. They might as well be (and in fact can be) completely
random. Even with incremental autonumbers, you can get gaps when a record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database file in
case you mess up. Also, ensure that the Name Autocorrect option is turned
of.

2. Open your table in design view and add a new field with the same name as
the old primary key field, and with data type Autonumber. Rename the old
field to "OldPK" but do not change the PK yet. Save the table. When you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as the old
foreign key field in the relationship, and with data type numeric. Set its
Size property to Long and delete the "0" from the DefaultValue property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join on the
OldPK and OldFK fields). Change the query to an update query and add the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the relationships
window and delete the relationships between the main table and each related
table.

7. Open the main table in design view and change the PK to the new field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before, except of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I messed up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna




  #7  
Old July 16th, 2009, 01:01 AM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default Not to Mess Up by Replacing Relationship with Different Primer

Hi Anna

Sure, you can add new customers - you just don't get to choose the customer
id for the new records because Access will do that for you.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Graham

But after doing it, can I still update my table by adding new customers?

I will check this out tomorrow

If I have any problems, I knock at your doors.

Thanks

Anna
"Graham Mandeno" wrote:

Hi Anna

First, if you want your customer id values in order with no gaps, then an
autonumber is NOT the way to go. Autonumbers are not really intended for
human consumption. They might as well be (and in fact can be) completely
random. Even with incremental autonumbers, you can get gaps when a
record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database file
in
case you mess up. Also, ensure that the Name Autocorrect option is
turned
of.

2. Open your table in design view and add a new field with the same name
as
the old primary key field, and with data type Autonumber. Rename the old
field to "OldPK" but do not change the PK yet. Save the table. When you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as the
old
foreign key field in the relationship, and with data type numeric. Set
its
Size property to Long and delete the "0" from the DefaultValue property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join on
the
OldPK and OldFK fields). Change the query to an update query and add the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID].
Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the
relationships
window and delete the relationships between the main table and each
related
table.

7. Open the main table in design view and change the PK to the new field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship
between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before, except
of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer id.
it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I messed
up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field
and
changing relationship I will destroy the balance between tables and
make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna






  #8  
Old July 17th, 2009, 01:46 PM posted to microsoft.public.access.tablesdbdesign
A.P.[_2_]
external usenet poster
 
Posts: 15
Default Not to Mess Up by Replacing Relationship with Different Primer

Hi Graham

This is what I entered
Parent table CAT Detailis
Child Table Queries & Graffiti

I want to update a new field Customer ID in the child table. I opened a
query, selected a query type. I chose as following
Field: Customer ID
Table: Queries & Graffiti
Update to: [tblCAT Detailis].[Customer ID]

Then there is a window popping out ' enter parameter value'. What did I do
wrong?

Anna

"Graham Mandeno" wrote:

Hi Anna

Sure, you can add new customers - you just don't get to choose the customer
id for the new records because Access will do that for you.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Graham

But after doing it, can I still update my table by adding new customers?

I will check this out tomorrow

If I have any problems, I knock at your doors.

Thanks

Anna
"Graham Mandeno" wrote:

Hi Anna

First, if you want your customer id values in order with no gaps, then an
autonumber is NOT the way to go. Autonumbers are not really intended for
human consumption. They might as well be (and in fact can be) completely
random. Even with incremental autonumbers, you can get gaps when a
record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database file
in
case you mess up. Also, ensure that the Name Autocorrect option is
turned
of.

2. Open your table in design view and add a new field with the same name
as
the old primary key field, and with data type Autonumber. Rename the old
field to "OldPK" but do not change the PK yet. Save the table. When you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as the
old
foreign key field in the relationship, and with data type numeric. Set
its
Size property to Long and delete the "0" from the DefaultValue property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join on
the
OldPK and OldFK fields). Change the query to an update query and add the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID].
Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the
relationships
window and delete the relationships between the main table and each
related
table.

7. Open the main table in design view and change the PK to the new field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship
between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before, except
of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer id.
it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I messed
up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field
and
changing relationship I will destroy the balance between tables and
make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna






  #9  
Old July 17th, 2009, 06:49 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_4_]
external usenet poster
 
Posts: 558
Default Not to Mess Up by Replacing Relationship with Different Primer

Try using an exclamation point (aka bang) instead of the dot:
[tblCAT Detailis]![Customer ID]

What exactly is the text of the parameter prompt?

"A.P." wrote in message
...
Hi Graham

This is what I entered
Parent table CAT Detailis
Child Table Queries & Graffiti

I want to update a new field Customer ID in the child table. I opened a
query, selected a query type. I chose as following
Field: Customer ID
Table: Queries & Graffiti
Update to: [tblCAT Detailis].[Customer ID]

Then there is a window popping out ' enter parameter value'. What did I do
wrong?

Anna

"Graham Mandeno" wrote:

Hi Anna

Sure, you can add new customers - you just don't get to choose the
customer
id for the new records because Access will do that for you.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Graham

But after doing it, can I still update my table by adding new
customers?

I will check this out tomorrow

If I have any problems, I knock at your doors.

Thanks

Anna
"Graham Mandeno" wrote:

Hi Anna

First, if you want your customer id values in order with no gaps, then
an
autonumber is NOT the way to go. Autonumbers are not really intended
for
human consumption. They might as well be (and in fact can be)
completely
random. Even with incremental autonumbers, you can get gaps when a
record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an
existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database
file
in
case you mess up. Also, ensure that the Name Autocorrect option is
turned
of.

2. Open your table in design view and add a new field with the same
name
as
the old primary key field, and with data type Autonumber. Rename the
old
field to "OldPK" but do not change the PK yet. Save the table. When
you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as
the
old
foreign key field in the relationship, and with data type numeric.
Set
its
Size property to Long and delete the "0" from the DefaultValue
property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join
on
the
OldPK and OldFK fields). Change the query to an update query and add
the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID].
Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the
relationships
window and delete the relationships between the main table and each
related
table.

7. Open the main table in design view and change the PK to the new
field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship
between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before,
except
of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer
id.
it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I
messed
up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates
new
numbers for me. However, I am a bit afraid that by adding a new
field
and
changing relationship I will destroy the balance between tables and
make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna








  #10  
Old July 17th, 2009, 07:10 PM posted to microsoft.public.access.tablesdbdesign
A.P.[_2_]
external usenet poster
 
Posts: 15
Default Not to Mess Up by Replacing Relationship with Different Primer

In the windown I have as following:

Enter Parameter Value(name of message box)
tblCAT Detailis!Business Number( message)
Gap to fill in


"BruceM" wrote:

Try using an exclamation point (aka bang) instead of the dot:
[tblCAT Detailis]![Customer ID]

What exactly is the text of the parameter prompt?

"A.P." wrote in message
...
Hi Graham

This is what I entered
Parent table CAT Detailis
Child Table Queries & Graffiti

I want to update a new field Customer ID in the child table. I opened a
query, selected a query type. I chose as following
Field: Customer ID
Table: Queries & Graffiti
Update to: [tblCAT Detailis].[Customer ID]

Then there is a window popping out ' enter parameter value'. What did I do
wrong?

Anna

"Graham Mandeno" wrote:

Hi Anna

Sure, you can add new customers - you just don't get to choose the
customer
id for the new records because Access will do that for you.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Graham

But after doing it, can I still update my table by adding new
customers?

I will check this out tomorrow

If I have any problems, I knock at your doors.

Thanks

Anna
"Graham Mandeno" wrote:

Hi Anna

First, if you want your customer id values in order with no gaps, then
an
autonumber is NOT the way to go. Autonumbers are not really intended
for
human consumption. They might as well be (and in fact can be)
completely
random. Even with incremental autonumbers, you can get gaps when a
record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an
existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database
file
in
case you mess up. Also, ensure that the Name Autocorrect option is
turned
of.

2. Open your table in design view and add a new field with the same
name
as
the old primary key field, and with data type Autonumber. Rename the
old
field to "OldPK" but do not change the PK yet. Save the table. When
you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as
the
old
foreign key field in the relationship, and with data type numeric.
Set
its
Size property to Long and delete the "0" from the DefaultValue
property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join
on
the
OldPK and OldFK fields). Change the query to an update query and add
the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID].
Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the
relationships
window and delete the relationships between the main table and each
related
table.

7. Open the main table in design view and change the PK to the new
field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship
between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before,
except
of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer
id.
it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I
messed
up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates
new
numbers for me. However, I am a bit afraid that by adding a new
field
and
changing relationship I will destroy the balance between tables and
make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna









 




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 12:47 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.