How to add sum from Query to Table

  • Thread starter Thread starter Nathan Lars
  • Start date Start date
N

Nathan Lars

Proposed Total: [Proposed Amount 1]+[Proposed Amount 2]+[Proposed Amount
3]+[Proposed Amount 4]

In query design view I have the Proposed Total field sum of 4 other fields.
So, on the Form I enter figures in any of the Proposed Amount fields and they
sum in the Proposed Total field on the form. The problem I am having is the
total is not saved in the Table. How do I get the results to show on both
the form and table fields?
 
Proposed Total: [Proposed Amount 1]+[Proposed Amount 2]+[Proposed Amount
3]+[Proposed Amount 4]

In query design view I have the Proposed Total field sum of 4 other fields.
So, on the Form I enter figures in any of the Proposed Amount fields and they
sum in the Proposed Total field on the form. The problem I am having is the
total is not saved in the Table. How do I get the results to show on both
the form and table fields?
Click to expand...

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 in the control source of a Form or a Report
textbox.

Note also that if your table has four fields for proposed amount, someday
you'll need a *fifth*. Storing repeating fields in your table is not correctly
normalized! You'ld do better to have a one to many relationship, storing each
proposal in its own record in a second table, and using a Totals query to sum
up the two, or four, or seventeen proposed amounts.
 
John,
You are absolutely right 5th field. How do I fix that first? Could you
show me how to set up the one to many relationship you refered to to correct
this problem?

Your post is right on and extremely helpful, thank you.

John W. Vinson said:
Proposed Total: [Proposed Amount 1]+[Proposed Amount 2]+[Proposed Amount
3]+[Proposed Amount 4]

In query design view I have the Proposed Total field sum of 4 other fields.
So, on the Form I enter figures in any of the Proposed Amount fields and they
sum in the Proposed Total field on the form. The problem I am having is the
total is not saved in the Table. How do I get the results to show on both
the form and table fields?
Click to expand...

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 in the control source of a Form or a Report
textbox.

Note also that if your table has four fields for proposed amount, someday
you'll need a *fifth*. Storing repeating fields in your table is not correctly
normalized! You'ld do better to have a one to many relationship, storing each
proposal in its own record in a second table, and using a Totals query to sum
up the two, or four, or seventeen proposed amounts.
Click to expand...
 
I have 3 fields for each proposal: Vendor, Proposed Amount, and Proposal
Date. As you can probably guess I have the same problem with the other two:
Vendor 1, Vendor 2...

Once the invoices come in there are 4 fields in the table for these: Invoice
Number, Invoice Amount, Invoice Date and Invoice Paid Date. Same problem
here.

Can I have 1 table showing a record for each vendor's proposal and invoice
information? Or do I need separate tables?

John W. Vinson said:
Proposed Total: [Proposed Amount 1]+[Proposed Amount 2]+[Proposed Amount
3]+[Proposed Amount 4]

In query design view I have the Proposed Total field sum of 4 other fields.
So, on the Form I enter figures in any of the Proposed Amount fields and they
sum in the Proposed Total field on the form. The problem I am having is the
total is not saved in the Table. How do I get the results to show on both
the form and table fields?
Click to expand...

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 in the control source of a Form or a Report
textbox.

Note also that if your table has four fields for proposed amount, someday
you'll need a *fifth*. Storing repeating fields in your table is not correctly
normalized! You'ld do better to have a one to many relationship, storing each
proposal in its own record in a second table, and using a Totals query to sum
up the two, or four, or seventeen proposed amounts.
Click to expand...
 
John,
You are absolutely right 5th field. How do I fix that first? Could you
show me how to set up the one to many relationship you refered to to correct
this problem?
Click to expand...

Just what do you mean by "fix that first"? What's the current structure of
your table? What type of Entity (real-life person, thing, or event) does it
represent?

You will want *two tables* - one for your current entity, with a Primary Key
(an account number, an Autonumber, whatever's appropriate), and a second table
for Proposals (whatever they are!); this table would have a field of the same
datatype as the first table's primary key (Long Integer if that's an
autonumber) to provide a link.

See the tutorials 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
 
Sorry for the confusion. What I meant by "fix that first" is the problem I
created by storing repeating fields in my table.

I use it to keep track of projects from start to finish. Could you clarify
what you mean by the current structure of my table?

When the DB was created, it was not set up with a primary key. I understand
this should have been done at the start, but can I fix it now?

Thanks for the links.
 
Sorry for the confusion. What I meant by "fix that first" is the problem I
created by storing repeating fields in my table.

I use it to keep track of projects from start to finish. Could you clarify
what you mean by the current structure of my table?

When the DB was created, it was not set up with a primary key. I understand
this should have been done at the start, but can I fix it now?
Click to expand...

You will need to set up a *SET* of normalized tables (not just one table; if
you only need one table, you may be just as well off using Excel).

I cannot tell you - from my current vantage point - how to set up those
tables, since I do not know the current structure of your tables, nor do I
know the real-life problem your tables are intended to model. You say "keep
track of projects from start to finish" - but you do not say, and I do not
know, what constitutes a project for you, nor what is the "start" and what is
the "finish" and what are the steps along the way!

It may be more than is appropriate for this free volunteer newsgroup, but if
you'll post some of the background perhaps I or another volunteer could
suggest some appropriate designs. All I can say right now is that if one
Project has multiple Proposed Amounts, the way to handle it would be something
like the following two tables:

Projects
ProjectID <Primary Key>
ProjectName
<other information about the project as a thing in itself>

Proposals
ProposalID <Primary Key>
ProjectID <link to Projects>
ProposalNo <1, 2, 3, ..., if the numbering is relevant>
Amount
<other information about this specific proposal>

You would enter as many *records*, not fields, into proposals as you had
proposalamounts.
 
Okay, I see what you are saying. I think I can noodle it through and refer
to the links you provided for more help.

Thank you for your extra time in explaining this for me.

Merry Christmas.
 
Back
Top