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 |
#11
|
|||
|
|||
Updating a field by adding all values from differen field
It will acctually work as I described, that is the way it is done. The books
you have should help you with how to build a form/subform. But, it wont help much with relational database design. One of the best books for that is "Relational Database Design for Mere Mortals" by Michael J. Hernandez. You do have a problem in that you already have related data using different relations, but without knowing exactly how the tables are designed and related, I can't really offer a suggestion on how to fix it. -- 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 |
#12
|
|||
|
|||
Updating a field by adding all values from differen field
Actually, I was just experimenting with a copy. The database was created by
me. I just wanted to know what I actually can do with it and learn how it has been built. Thank you for the title. I will try to get it "Klatuu" wrote: It will acctually work as I described, that is the way it is done. The books you have should help you with how to build a form/subform. But, it wont help much with relational database design. One of the best books for that is "Relational Database Design for Mere Mortals" by Michael J. Hernandez. You do have a problem in that you already have related data using different relations, but without knowing exactly how the tables are designed and related, I can't really offer a suggestion on how to fix it. -- 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 | |
|
|