sums in reports

[Guest]

is there an easy way to create a report that just displays table sums? do i
need to create a seperate table that contains the totals of all my other
tables?

 

[Rick Brandt]

is there an easy way to create a report that just displays table sums? do i
need to create a seperate table that contains the totals of all my other
tables?

At its simplest you could just use a bunch of DSum() expressions in some unbound
TextBoxes in an unbound report. Depending on the number of tables this might
not be a very efficient thing to do.

You definitely DO NOT want to create a separate table for this.

 

[Marshall Barton]

is there an easy way to create a report that just displays table sums? do i
need to create a seperate table that contains the totals of all my other
tables?

Not clear what you mean by "table sums" or what it means to
have totals of "all" your tables.

The core of just about any report is organizing its data in
a query. So, I think you question really boils down to
creating a query that includes the required totals, but
without a description of your tables and their rlationships
I couldn't venture to guess what that might be.

 

[Guest]

Sorry, I'll try to be more descriptive:

Tables consist of accounts, fields are "account #", "account type", and
"value of account". There are several different account type fields, and I
want to create reports that contain the sum of account values by account type
(just the sum, I don't want to list every record -there are thousands).
It's frustrating because I think this should be very easy. I've tried (as
Rick suggested) to use DSum expressions in unbound textboxes, but i keep
getting #Error.

 

[Marshall Barton]

Ohhhh boy. You're saying that you have multiple tables with
the same kind of data and you want to total a field across
some/all the tables???

If that's your situation, then, NO, there is nothing easy
about it. You have a tble structure that violates the basic
rules of database normalization and the standard database
capabilities will not be much help to you.

If I've understood you correctly, about all I can suggest is
that you put together an ugly query that attempts to provide
a normalized dataset to perform your other operations on:

Query FixData:
SELECT table1.fielda, table1.fieldb, table1.fieldc
FROM table1
UNION ALL
SELECT table2.fielda, table2.fieldb, table2.fieldc
FROM table2
UNION ALL
SELECT table3.fielda, table3.fieldb, table3.fieldc
FROM table3
UNION ALL
. . .

Then you can use a common Totals type query to calculate
counts and sums:

SELECT AccountType, Sum(AccountValue) As TypeTotal
FROM FixData
GROUP BY AccountType