Basically I have a DB of rooms. Each record has 1) name 2) area 3) generic type of room (eg large, small).

I want to be able change the area so all similar generic types change. Hence I have a self relationship by generic type. This seems to work until I try to add the areas up as the relationship refers only to the first record.

Is there some way around this? thanks

Edited August 6, 200718 yr by Guest

How are "rooms" related to "type"?

thanks

each room is assigned a type

eg

room 1-------small room

room 2-------small room

room 3-------medium room

room 4-------large room

room 5-------large room

So the Rooms table needs a TypeID field.

A Type table contains the records with 1 = small room etc where 1 = TypeID.

Match the TypeID field together to relate the twp tables.

Thanks Vaughan. The relationship is working fine, just the areas wouldnt sum up. eg if I had two typeID records representing large and small rooms the sum would be only of those two records when (in case of my earlier example) should be summing 5 instances.

It may not be the best solution but I have created a count field (for the number of rooms in each type) and multiplied it by the area to get a field I can sum.

I was hoping for an simpler solution as ultimately the database will have a lot of different fields that need summing.