adding problem

[Aussie John]

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, 2007 by Guest

[Vaughan]

How are "rooms" related to "type"?

[Aussie John]

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

[Vaughan]

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.

[Aussie John]

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.