Updating a field by adding all values from differen field

Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

Thanks

Anna

You question is vague. What do you mean by "rows are parallel"?
In any case, what you are doing seems a bit scary. You should not mess
around with autonumbers.

If you can describe What you want to do, perhaps we can help with the How to
do it.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

Thanks

Anna

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek

I want to add a new field with autonumber to a parent table and to buid a
relationship on it. In this way I could avoid making a mistake in Customer ID
field by entering a wrong number, out of sequence, eg. 99 instead of 89.
Currently, I have a primary key set on Customer ID that is a numeric field.
The value is entered manually and has to be in sequence. I made a mistake and
add a wrong number that was followed.

To add a new field and create a relationship, I added a autonumber field to
a parent table and numeric field to child one. Then I wanted to copy
autonumber field to that numeric field in child table, to make sure that all
rows are well matched.

But when I entered these parameters below, a window with' Enter Parameter
Value' popped out. So what did I do wrong?

This is what I entered

Parent table CAT Detailis
Child Table Queries & Graffiti

Field: Customer ID
Table: Queries & Graffiti
Update to: [tblCAT Detailis].[Customer ID]

"Klatuu" wrote:

You question is vague. What do you mean by "rows are parallel"?
In any case, what you are doing seems a bit scary. You should not mess
around with autonumbers.

If you can describe What you want to do, perhaps we can help with the How to
do it.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

Thanks

Anna

Hi
So how can I do? What kind commands should I use to update a child table
with values that make ID of parent table equal to ID of child one?

Anna

"Bernard Peek" wrote:

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek

Set your table relations to the two fields. The master field should be
autonumber, the child field should be long integer.

Use a form/subform construct. The master table (or a query on the table)
should be the record source for the form and the child table should be the
record source for the subform. Use the subform control's Link Master Fields
and Link Child Fields to link the tables on the two fields.

Now when you create a child record in the subform, it will autmatically get
the value of the parent record's autonumber field.

If you are allowing users to enter data directly into tables, you will never
have a reliable database.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi
So how can I do? What kind commands should I use to update a child table
with values that make ID of parent table equal to ID of child one?

Anna

"Bernard Peek" wrote:

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek

The problem is I was entering directly into the table. I cannot correct the
records number as I would have delet relevant records from the child table.
If I add an autonumber field and try to connect it to a customer id field in
my database, it simply won't work.

"Klatuu" wrote:

Set your table relations to the two fields. The master field should be
autonumber, the child field should be long integer.

Use a form/subform construct. The master table (or a query on the table)
should be the record source for the form and the child table should be the
record source for the subform. Use the subform control's Link Master Fields
and Link Child Fields to link the tables on the two fields.

Now when you create a child record in the subform, it will autmatically get
the value of the parent record's autonumber field.

If you are allowing users to enter data directly into tables, you will never
have a reliable database.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi
So how can I do? What kind commands should I use to update a child table
with values that make ID of parent table equal to ID of child one?

Anna

"Bernard Peek" wrote:

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek

Beside, if I changed a primary key and set a new relationship, I will lose a
control over the child table's records. Everyhing will get messed up. I am
not sure how I can use form/subform contruct. Do mean to build an actual form
and try to build relations there?

"Klatuu" wrote:

Set your table relations to the two fields. The master field should be
autonumber, the child field should be long integer.

Use a form/subform construct. The master table (or a query on the table)
should be the record source for the form and the child table should be the
record source for the subform. Use the subform control's Link Master Fields
and Link Child Fields to link the tables on the two fields.

Now when you create a child record in the subform, it will autmatically get
the value of the parent record's autonumber field.

If you are allowing users to enter data directly into tables, you will never
have a reliable database.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi
So how can I do? What kind commands should I use to update a child table
with values that make ID of parent table equal to ID of child one?

Anna

"Bernard Peek" wrote:

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek

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.

Anna

"Klatuu" wrote:

Set your table relations to the two fields. The master field should be
autonumber, the child field should be long integer.

Use a form/subform construct. The master table (or a query on the table)
should be the record source for the form and the child table should be the
record source for the subform. Use the subform control's Link Master Fields
and Link Child Fields to link the tables on the two fields.

Now when you create a child record in the subform, it will autmatically get
the value of the parent record's autonumber field.

If you are allowing users to enter data directly into tables, you will never
have a reliable database.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi
So how can I do? What kind commands should I use to update a child table
with values that make ID of parent table equal to ID of child one?

Anna

"Bernard Peek" wrote:

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek

--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

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.

Anna

"Klatuu" wrote:

Set your table relations to the two fields. The master field should be
autonumber, the child field should be long integer.

Use a form/subform construct. The master table (or a query on the table)
should be the record source for the form and the child table should be the
record source for the subform. Use the subform control's Link Master Fields
and Link Child Fields to link the tables on the two fields.

Now when you create a child record in the subform, it will autmatically get
the value of the parent record's autonumber field.

If you are allowing users to enter data directly into tables, you will never
have a reliable database.
--
Dave Hargis, Microsoft Access MVP


"A.P." wrote:

Hi
So how can I do? What kind commands should I use to update a child table
with values that make ID of parent table equal to ID of child one?

Anna

"Bernard Peek" wrote:

In message , A.P.
writes
Hi Everyone

Somebody suggested that I could use update query to add all values from one
field to another one. I want to add autonumber values to a numeric field
without making additional autonumber field in child table. I am doing it to
make sure rows are parallel to each other. Can you please tell which command
I should you?

You may have a fundamental problem here. This is not the right way to
make sure that the keys are correctly matched.

If you have not yet populated the child table then you should build a
master/subform pair for the two tables linked by the ID fields. Step
through each record in the parent table and use the form to populate the
child table. This will make sure that each child record is linked to the
correct parent record using their ID fields.

If you have already populated both tables then you need to create a
primary/foreign key relationship using natural keys instead of the
surrogate key which is the autonumber field. You can then create a query
that incorporates all of the relevant fields and update the child.ID
field to equal parent.ID, that is if you still want to make use of the
autonumber field.



--
Bernard Peek