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  

Updating a field by adding all values from differen field



 
 
Thread Tools Display Modes
  #11  
Old July 17th, 2009, 09:41 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old July 18th, 2009, 12:27 AM posted to microsoft.public.access.tablesdbdesign
A.P.[_2_]
external usenet poster
 
Posts: 15
Default 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

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 01:37 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.