Updating records

  • Thread starter Thread starter scootermouse
  • Start date Start date
S

scootermouse

I have 2 file that have a many to one relationship. I'm trying to take the
sum of a field in the "many" file and update to a field in the "one" file. I
tried to do it programmaticaly but keep getting the message that the
recordset cannot be updated. I also tried to do it with an update query but
kept getting all kinds of messages. I'm sure it's probably a pretty simple
procedure, I'm just not real familiar with Access programming. Any help
would be appreciated.

scootermouse
 
Why?

If you already have the "detail" values in your many-side table, and you
already know which ones are related to which "parent" in your one-side
table, use a query! Calculating (i.e., deriving) a value and putting that
in the one-side table is subject to data integrity issues (i.e.
synchronization).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Do you mean an update query? I already tried that and it didn't work. I'm
not real familiar with queries. Would I be able to set the value in a select
query? or is there some other kind of query I should use?
 
Do you mean an update query? I already tried that and it didn't work. I'm
not real familiar with queries. Would I be able to set the value in a select
query? or is there some other kind of query I should use?
Click to expand...

Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
And if you want to display the sum in the form for the "one" file, this can
be accomplished with a DSum function. You would build it in the form field
using the Expression Builder, but it basically looks like this:

DSum("field from many table", "many table name", "linking field in many
table = linking field on form")

HTH
 
Thanks John and Amy,

As I was thinking about it today (before I got your messages) I came to the
same conclusion...that I don't need to update the file just recalculate the
total every time I display the form.

Thanks again,
Scootermouse
 
Back
Top