Multiple Parent & Child Record Problem

[stevefutcher]

My organisation performs a number of different collection services from a large number of premises. The most complicated situation I am trying to model involves a block of flats/ apartments / retirement home.

In this scenario we might have 16 flats in 4 blocks of 4. Blocks 1 & 2 have collection A made from containers located in the car park, blocks 3 & 4 have collection A made from containers located by the gates, collection B is made from containers located in each block and collection C is made from containers located in each property.

If I create a parent record for the whole complex, then I lose the details of which containers are for which properties, as they are all grouped together. If however, I make parent records for the half complex communal containers (for collection A), and a parent record for each block (for collection B) and I already have an individual premises record for collection C then I have a suitable premises record for each collection and for all containers.

The problem comes when I want to create a query which will display the premises ID of the property I am viewing, together with the premises ID of any parent(s) AND any children, all in the same PremID field so that I can summarise all collections and container data in a single view.

At the moment I am using two tables tblPremises (PK PremID) and tblParentChild fields (Parent_PremID, Child_PremID) to record the relationships between parent & child premises, and I am creating a temporary table, and appending the parent & child records to it as a three query process in order to discover all relevant collections.

I can't help but think that there is an easier way - either a change of structure or a better query - anybody got any suggestions.

 

[Andrzejek]

I may be off, but isn’t it a situation similar to students and classes – many to many relationship?
You may have a lot of students and a lot of classes they can take. So every student can take one or many classes, and every class can have one or many students. So you have tblClasses and tblStudents. Then you create another table where there will be a record for every student assigned to a particular class. You simplify your relations from many-to-many to many-to-one and one-to-many.

Would that work in your situation?


Have fun.

---- Andy

 

[stevefutcher]

You are right about it being a many to many relationship, but it's not quite the same as students & classes because there is only one premises table, and I want to return all the combinations of parents and children premises ids.

tblPremises
PremID PremName
1 Steve Court
2 Steve Court Block 1
3 Steve Court Block 2
4 Flat 1 Steve Court
5 Flat 2 Steve Court
6 Flat 3 Steve Court
7 Flat 4 Steve Court

tblParentChild
ParentPremID ChildPremID
1 2
1 3
1 4
1 5
1 6
1 7
2 4
2 5
3 6
3 7

tblCollection
PremID Collection
1 A
2 B
3 B
4 C
5 C
6 C
7 C

I want to only record collections that are actually made, but I would like to make sure that I can report on all relevant collections for any property