sum

  • Thread starter Thread starter Luis
  • Start date Start date
L

Luis

Please, anybody know how can I do the sum of hours stored
into different tables and store the result into other
table?

Tanks a lot for your attention.

Luis
 
There are a lot of ways that you could do this. Possibly
the easiest would be to create an append query and use an
expression to calculate the result that you want to
append.
 
Since you can always recalculate sums in queries, forms or
reports, in many situations it's unnecassary to store the
totals in a table.

You can pull the totals a couple of different ways,
depending on how your database is set up.

For my example, let's assume the two tables below and I
want to calculate the total amount of money my
organization brought in during the month:

tblDonations (dDate, dDonor, dAmount) and

tblFundRaisers (frDate, frAmount)

Look up the Help file on UnionQueries. They let you join
the results of two (or more) other queries. The two big
things to remember are that 1) all the queries being
combined need to have the same fields with the same names
in the same order and 2) all Union Queries have to be
written in SQL (you can't use the QueryDesigner).

Create a standard SELECT query named qryDonations,
calculating your Donation totals:

SELECT Sum([dAmount]) AS Donations, NULL AS FundRaisers
FROM tblDonations;

Create a standard SELECT query named qryFundRaisers,
calculating your FundRaising totals:

SELECT NULL AS Donations, Sum([frAmount]) AS FundRaisers
FROM tblFundRaisers;

You can build both of these in the QueryDesigner, too, and
include any filtering criteria - like the date/month - you
need. Then open a new query in Design View and click on
View > SQL to open the SQL window. Write your Union query:

SELECT tblDonations.Donations, tblDonations.FundRaisers
FROM tblDonations

UNION SELECT tblFundRaisers.Donations,
tblFundRaisers.FindRaisers
FROM tblFundRaisers;

This will give you two records so you'll probably need to
run another query to total the Union query but this should
give you the number you need.

If you do need to store the totals in another table, check
the Help files on Append Queries and Update Queries.

Hope this helps!

Howard Brody
 
Back
Top