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
|
|||
|
|||
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 |
#2
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#3
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#4
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#5
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#6
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#7
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#8
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#9
|
|||
|
|||
Updating a field by adding all values from differen field
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 |
#10
|
|||
|
|||
Updating a field by adding all values from differen field
-- 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 |
|
Thread Tools | |
Display Modes | |
|
|