Adding values from 7 fields from 1 table to 7 fields of another ta

[BP]

Hello,
I have 2 tables namely "tblUpdate" and "tblRecord". These 2 tables are
joined by a primary key known as Job_No with referential integrity set.
(tblUpdate - Multiple records, tblRecord - 1)

I have a form named "frmUpdate" where the user key in Job_No, followed by
manhours into 7 different fields named A, B, C, D, E, F, G into tblUpdate.

After this form is fully keyed, the user exits the form by clicking an exit
button. On exit the values of these 7 fields has to be added to the value of
the same 7 fields of the row(reference by Job No) on "tblRecord".

How do I do this?
I figured I need to put in some code on the on_exit event of the form but my
vba knowledge is extremely basic. Please help.

Thanks! BP

 

[karl dewey]

Maybe I am reading your post wrong but it does not make sense.
This is how I read it --
"tblRecord" and "tblUpdate" are one-to-many
Use "frmUpdate" to input manhours into fields A, B, C, D, E, F, G into
tblUpdate
Then dump same data into tblRecord.

Why store the same data in two places? Use a form/subform
(tblRecord/tblUpdate) with Master/Child links set on Job_No.

 

[BP]

Its not the same data. those 7 fields from the many side are Added to the
fields on the one side. Those fields on the One side are cumulative.

ie. [tblRecord].[A] = 7

in the frmUpdate, field A is keyed in as 8. after the form closes,
[tblUpdate].[A] = 8, but [tblRecord].[A]=15

Dun't know how to put it into words, but Does this make sense?

pls advise. thanks.

 

[karl dewey]

Again I say why store the same data in two places? You are storing it
separately in individual records of tblUpdate and totalled in tblRecord.
Just use a query/form/report to display totals.

You can use event/macro to Set Value of field [tblRecord].[A] to
[tblRecord].[A] + [Forms]![frmUpdate]![A] and the same for the other fields.

--
KARL DEWEY
Build a little - Test a little

Its not the same data. those 7 fields from the many side are Added to the
fields on the one side. Those fields on the One side are cumulative.

ie. [tblRecord].[A] = 7

in the frmUpdate, field A is keyed in as 8. after the form closes,
[tblUpdate].[A] = 8, but [tblRecord].[A]=15

Dun't know how to put it into words, but Does this make sense?

pls advise. thanks.

Maybe I am reading your post wrong but it does not make sense.
This is how I read it --
"tblRecord" and "tblUpdate" are one-to-many
Use "frmUpdate" to input manhours into fields A, B, C, D, E, F, G into
tblUpdate
Then dump same data into tblRecord.

Why store the same data in two places? Use a form/subform
(tblRecord/tblUpdate) with Master/Child links set on Job_No.

 

[BP]

Because I will later need to sum the entire columns of the cumulative fields
table and do not want to make it too complicated. simplify it into simpler
steps although taking more space is fine.

Can you explain more on the macro part, how does the macro regconise which
row to set the tblRecord.A field?

Thanks.

Again I say why store the same data in two places? You are storing it
separately in individual records of tblUpdate and totalled in tblRecord.
Just use a query/form/report to display totals.

You can use event/macro to Set Value of field [tblRecord].[A] to
[tblRecord].[A] + [Forms]![frmUpdate]![A] and the same for the other fields.

--
KARL DEWEY
Build a little - Test a little

Its not the same data. those 7 fields from the many side are Added to the
fields on the one side. Those fields on the One side are cumulative.

ie. [tblRecord].[A] = 7

in the frmUpdate, field A is keyed in as 8. after the form closes,
[tblUpdate].[A] = 8, but [tblRecord].[A]=15

Dun't know how to put it into words, but Does this make sense?

pls advise. thanks.

Maybe I am reading your post wrong but it does not make sense.
This is how I read it --
"tblRecord" and "tblUpdate" are one-to-many
Use "frmUpdate" to input manhours into fields A, B, C, D, E, F, G into
tblUpdate
Then dump same data into tblRecord.

Why store the same data in two places? Use a form/subform
(tblRecord/tblUpdate) with Master/Child links set on Job_No.
--
KARL DEWEY
Build a little - Test a little


:

Hello,
I have 2 tables namely "tblUpdate" and "tblRecord". These 2 tables are
joined by a primary key known as Job_No with referential integrity set.
(tblUpdate - Multiple records, tblRecord - 1)

I have a form named "frmUpdate" where the user key in Job_No, followed by
manhours into 7 different fields named A, B, C, D, E, F, G into tblUpdate.

After this form is fully keyed, the user exits the form by clicking an exit
button. On exit the values of these 7 fields has to be added to the value of
the same 7 fields of the row(reference by Job No) on "tblRecord".

How do I do this?
I figured I need to put in some code on the on_exit event of the form but my
vba knowledge is extremely basic. Please help.

Thanks! BP

 

[karl dewey]

You would have form/subform and only one record would be in the main form.
Correction to last post ----
You can use event/macro to Set Value of field [Forms]![MainForm].[A] to
[Forms]![MainForm].[A] + [Forms]!]![MainForm]![frmUpdate]![A] and the same
for the other fields.
--
KARL DEWEY
Build a little - Test a little

Because I will later need to sum the entire columns of the cumulative fields
table and do not want to make it too complicated. simplify it into simpler
steps although taking more space is fine.

Can you explain more on the macro part, how does the macro regconise which
row to set the tblRecord.A field?

Thanks.

Again I say why store the same data in two places? You are storing it
separately in individual records of tblUpdate and totalled in tblRecord.
Just use a query/form/report to display totals.

You can use event/macro to Set Value of field [tblRecord].[A] to
[tblRecord].[A] + [Forms]![frmUpdate]![A] and the same for the other fields.

--
KARL DEWEY
Build a little - Test a little

Its not the same data. those 7 fields from the many side are Added to the
fields on the one side. Those fields on the One side are cumulative.

ie. [tblRecord].[A] = 7

in the frmUpdate, field A is keyed in as 8. after the form closes,
[tblUpdate].[A] = 8, but [tblRecord].[A]=15

Dun't know how to put it into words, but Does this make sense?

pls advise. thanks.



:

Maybe I am reading your post wrong but it does not make sense.
This is how I read it --
"tblRecord" and "tblUpdate" are one-to-many
Use "frmUpdate" to input manhours into fields A, B, C, D, E, F, G into
tblUpdate
Then dump same data into tblRecord.

Why store the same data in two places? Use a form/subform
(tblRecord/tblUpdate) with Master/Child links set on Job_No.
--
KARL DEWEY
Build a little - Test a little


:

Hello,
I have 2 tables namely "tblUpdate" and "tblRecord". These 2 tables are
joined by a primary key known as Job_No with referential integrity set.
(tblUpdate - Multiple records, tblRecord - 1)

I have a form named "frmUpdate" where the user key in Job_No, followed by
manhours into 7 different fields named A, B, C, D, E, F, G into tblUpdate.

After this form is fully keyed, the user exits the form by clicking an exit
button. On exit the values of these 7 fields has to be added to the value of
the same 7 fields of the row(reference by Job No) on "tblRecord".

How do I do this?
I figured I need to put in some code on the on_exit event of the form but my
vba knowledge is extremely basic. Please help.

Thanks! BP

 

[John W. Vinson]

Hello,
I have 2 tables namely "tblUpdate" and "tblRecord". These 2 tables are
joined by a primary key known as Job_No with referential integrity set.
(tblUpdate - Multiple records, tblRecord - 1)

I have a form named "frmUpdate" where the user key in Job_No, followed by
manhours into 7 different fields named A, B, C, D, E, F, G into tblUpdate.

After this form is fully keyed, the user exits the form by clicking an exit
button. On exit the values of these 7 fields has to be added to the value of
the same 7 fields of the row(reference by Job No) on "tblRecord".

How do I do this?
I figured I need to put in some code on the on_exit event of the form but my
vba knowledge is extremely basic. Please help.

Thanks! BP

WHOA. I think you may have some real structural problems here!

First off you apparently have repeating fields. If fields A, B, C etc. contain
the same type of data (manhours) then you're jamming a one (tblUpdate) to many
(manhours) relationship into each record.

Then if you're storing the sum of tblUpdate.A and tblRecord.A back into
tblRecord, you're storing derived data - or is that not what you mean by
"add"?

Step back a bit and describe the Entities modeled by these tables, the meaning
of these A-G fields, and a bit more about the context.

 

[BP]

tblRecord is for project creation and has a primary key known as JobNo. There
can be only 1 project at one time hence this is the one table. I have 7
fields in this table to clock the total hours for each engineer(7 engineers)
for each project.

tblUpdate is many table(common field JobNo) as it is for updating the status
of Each visit of the project as there can be many visits to the clients place
and thus the individual manhours of the engineers are clocked here in the 7
fields for 7 engineers for each visit.

I require the manhours fields in tblRecord to be the sum of all the manhours
of each all visits under the same project. I know it can be queried, and that
the fields in the tblRecord table is extra, but my knowledge in access/coding
is limited to the 1st few chapters of the dummies guide so i think i want the
value to be stored value so that it is easier for me to reference it in the
later stages for some other calculation or to just export 1 table without the
need to look at the tblUpdate. I would also need to sum the whole column of
the 7 stored totals in the tblRecord and i dunt know how to sum a column of
queries.

 

[BP]

I got it working already. Thanks.

 

[John W. Vinson]

tblRecord is for project creation and has a primary key known as JobNo. There
can be only 1 project at one time hence this is the one table. I have 7
fields in this table to clock the total hours for each engineer(7 engineers)
for each project.

So if a new project is started which needs nine engineers you'll... what?
Restructure your table, rewrite all your queries, redesign all your forms,
redesign all your reports? OUCH!!

You're using a relational database as if it were a spreadsheet. That's a very
common mistake, especially for people who come to Access from an Excel
background... but it *is* a mistake. See below.

tblUpdate is many table(common field JobNo) as it is for updating the status
of Each visit of the project as there can be many visits to the clients place
and thus the individual manhours of the engineers are clocked here in the 7
fields for 7 engineers for each visit.

And you want to DESTROY the information about each visit, and record only the
total? What if there's an error in one of the manhours values? If you record
only the sum, how will you ever figure out where the error was?

I require the manhours fields in tblRecord to be the sum of all the manhours
of each all visits under the same project. I know it can be queried, and that
the fields in the tblRecord table is extra, but my knowledge in access/coding
is limited to the 1st few chapters of the dummies guide so i think i want the
value to be stored value so that it is easier for me to reference it in the
later stages for some other calculation or to just export 1 table without the
need to look at the tblUpdate. I would also need to sum the whole column of
the 7 stored totals in the tblRecord and i dunt know how to sum a column of
queries.

You need to learn about "Normalization". Each type of Entity - real-life
person, thing, or event, such as an Engineer, a Job, a Visit, a Project -
needs *its own table*. I don't know your business, but I could imagine tables
like

Engineers
EngID <autonumber primary key>
LastName
FirstName
<other biogrphical data>

Projects
ProjectNo <Primary Key>
Description
StartDate
<other info about the project as an entity>

Visit
VisitID <Autonumber primary key>
ProjectNo <link to Projects>
EngID <link to Engineers, who worked on the project this visit>
VisitDate <Date/Time>
Purpose <text, why was Florence Ambrose there that day>
Hours <how long did she work>
<other info about this visit>

You would use Totals Queries to sum the values, grouping by project and/or by
engineer.

The Dummies book is, from what I've seen, pretty good at teaching how to *use*
an existing Access app. It's not intended, nor is it adequate, for scaling the
rather steep learning curve to actually designing and implementing a new
Access app! And in this case, it's led you down a wrong path. See some of the
resources at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials