Not to Mess Up by Replacing Relationship with Different Primery Ke

Could you recommend me any book I can use to buil up my knowlegde on more
complex topics like that one. I have learnt from Access 2003 for Dummies and
Microsoft Office Access 2003 Bible. I started using Access 2003 last year and
I am still learning.

"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

Hi Anna

Can you please post the SQL for the query (copy it from the Sql view
window).

It should look something like this:

UPDATE [Cat Details] INNER JOIN [Queries & Graffiti]
ON [Cat Details].[OldPK] = [Queries & Graffiti].[OldFK]
SET [Queries & Graffiti].[Customer ID] = [CAT Details].[CustomerID]

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"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

Somewhere in the query is tblCatDetails!BusinessNumber(message)
I can't tell if the prompt literally includes the word "message" in
parentheses or if you are conveying something else, but in any case you are
telling the query to use a value it can't resolve.
As Graham suggested, please post the SQL.

"A.P." wrote in message
...
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