Summing problem with a complex relationship
I've got a space database and I'm trying to sum floor areas for each of five use types in each building in the database. The types are Administrative, clinical, research, etc. I want a portal with an entry for each building and a field that shows the total area in that building for each type.
Right now I'm only trying to total the Administrative spaces just to get it working. Here is how it's set up:
There are three tables. Buildings, Spaces, and Space_Class
Buildings has four fields:
- Name (unique, text)
- Area (calc) = Sum(Spaces::Net_Area) <-- WORKING
- Admin_Use_Type (global calc) = "Administrative"
- Admin_Area (calc) = Sum(Admin_Spaces::Net_Area) <-- NOT WORKING
Spaces has a record for every room. There are two instances, Spaces and Admin_Spaces. The relevant fields are:
- Building (text) - name of a building in the above table
- Use_Code (text) - a code for the use, used to look up the use type
- Net_Area (number) - area of the room
- Use_Type (calc) = Lookup(Space_Class::UseType;"")
Space_Class has the type for each use code. There are many use codes, and five possible types
- Use_Code (unique, text)
- Use_Type (text)
I attached the relationship graph which will hopefully clarify things a little. Spaces and Admin_Spaces are the same table. Space_Class is there so that Admin_Spaces can look up its Use_Type.
The reasoning here is I want Buildings::Area to calculate the total area for the building, and Buildings::Admin_Area to have the total of only the spaces with Administrative use types. So the total area is calculated from Spaces, and the Admin area is calculated from Admin_Spaces, which has the extra condition of containing only records where Use_Type is "Administrative."
Here's the problem. In the portal for Buildings I have fields for Building name, total area, and admin area. The building and total area show up correctly, but the admin area is blank for every building.
(Sorry the formatting seems wierd, it looks fine in the editor then it seems to strip the <li> tags out when it posts)