Help with Totals

[Maria]

I have a simple question, but no one seems to be able to
help me. I want to create a table with income from
various different sources. At the bottom of the column of
figures, I want to put a "Total" which will add up the
previous fields. This can be right in the table or in a
query. Either way, someone please tell me how to do it.
Thank you very much for your time.

 

[Graham Mandeno]

Hi Maria

It should certainly not be in a table, and if you want the numbers being
summed as well as the total, then it should not be in a query either.

Assuming you have a finite number of pre-defined sources, you should have an
IncomeSources table, with two fields:
SourceID (autonumber, primary key)
SourceName (text, no duplicates)

And you need an Income table with (at least) these fields:
IncomeDate (date/time)
IncomeSource (numeric - long)
IncomeAmount (currency)

Make a relationship between the two tables on SourceID<IncomeSource
(one-to-many).

Now you can make a totals query, including both tables, and grouping by
SourceName, with the following aggregate field:
TotalIncome: Sum(IncomeAmount)

If you want to list all the numbers as well as the total, then make a
report, add a grouping level on SourceName with a group header and footer,
put IncomeDate and IncomeAmount in the detail section, and SourceName in the
group header. Then in the group footer, add a textbox with controlsource:
=Sum(IncomeAmount)