Summing in a Report based on a one-to-many Query

[NYShai]

I'll do my best to describe the problem without sounding like a bafoon.

I have 2 tables: Properties and Units. Each one property has many
units, hence a one-to-many relationship exists between these 2 tables.

I created a report based on a query that pulls from these 2 tables and
thus creates redundant data on those fields pulling from the "one"
table (for each instance in the many table). for example, running the
query produces something like this:

PropertyID PropName PropSize UnitNumber UnitSize
A10 5th Street Lofts 10,000 A 2,000
A10 5th Street Lofts 10,000 B 6,000
A10 5th Street Lofts 10,000 C 2,000
A12 Brookside Lofts 7,000 1 3,500
A12 Brookside Lofts 7,000 2 3,500

The report has a grouping header whereby it shows each instance of
PropertyID, PropetryName and PropSize once, and then each of the units
(A, B, and C) below. I am trying to sum the PropSize for the entire
report, and instead of getting 17,000 I get 44,000 because the report
sums up each instance of PropSize that occurs in the query as a result
of each row that is created because it pulls from the Units table.

What's the workaround, what am I doing wrong, etc ? The report / table
/ query is actually a bit more involved that what I described above,
but this is a good representation of the problem.

Please email, as well as post, since I don't get to log on here as
often as I would like.

ShyGuy

 

[Duane Hookom]

You can create a PropertyID sorting and grouping level with a group header
or footer section. Add a text box (txtPropSizeRS) bound to the PropSize
field in this group section and set its running sum to Over All. Then add a
text box to your report footer with a control source of:
=txtPropSizeRS

 

[John Spencer]

Just an aside, but with the sample data given couldn't you just sum the Unit
Size.

Duane's solution is more generic and will work in many more situations than
this one. Also, if UNIT Size doesn't total to property size or not all
units are included in the report (for whatever reason), then again his
solution is superior.

 

[Duane Hookom]

The Unit Size would allow simple summing but the Property Size requires
either the running sum or a separate totals query joined to the report's
record source or code or the use of subreports.