Report Grouping Calculations

[Grease]

Hi,
Scenario:
A community with several subdivisions. The number of subdivisions can grow.
Each subdivision contains lots. Width and length of each lot is known.
Some lots are occupied, some are not.
I want to create a report that will show the total number of lots that are
occupied,
and the total square footage that is occupied versus those that are not for
each subdivision.
The tables looks like this:

lotInfoTbl:
lotID (char 4) (key)
subdivisionID (long int)
lotLength (long int)
lotWidth (long int)
occupied (yes/no)

subdivisionsTbl:
subdivisionID (autoNumber) (key)
subdivisionName (char 25)

Any help generating this report is much appreciated.
Thanks.
Cheers,
John

 

[Fons Ponsioen]

Open a new query in design view.
Add the two tables.
Link the tables by subdivisionID
Select the following fields to show:
From lotInfoTbl:
LotSize:lotLength * lotWidth
occupied
From subdivisionsTbl:
subdivisionName

Now open a new report in the design view and set the
source to the above created query.
Select Sorting and Grouping, group by subdivisionName, and
select a Group Footer.
In the page header place 5 labels (column Headers)
1. SubDivision - 2. Occupied # - 3. Occupied Size - 4. Not
Occupied # - 5. Not Occupied Size.

In the group footer place 5 textboxes (below the column
headers)
Put the source for each as follows:
1. subdivisionName
2. =IIF([occupied] = 0,0,1)
3. =IIF([occupied] = 0,0,[LotSize])
4. =IIF([occupied] = -1,0,1)
5. =IIF([occupied] = -1,0,[LotSize])

Set the properties for the textboxes 2,3,4 and 5 to
running sum over group.
This should do what you want.
Hope this helps.
Fons