circle relation

[johanoverbeeke]

Hi there!

1.
I have a table Persons and a table Relations (relation e.g. "parent of", "partner of", "client of",...).
From Persons goes a relation One-To-Many to Relations and from Relation goes a Many-To-One back to Persons.
As soon as you create these relations, Acces creates on the diagram a copy of Persons with the name Persons-1, so there is no circle in the diagram.
If I want a form where on the left you see Person X and on the right the Person(s) related to Person X, I cannot "see" the table Persons-1, so I cannot add the records/fields of Persons-1???
How to make this form?

2.
If you have the record "Parent of" in Relations, should I add also another record "Child of", or should I capture this in the programming, bc with the "Parent of"-relation, all the information is already in the tables...
What is "good" DB-design in this?


Thx!

Johan.

 

[dhookom]

1) There is no second copy of the persons table. Base your subform on the right on your relations table. You will probably want a combo box with the Person table as the Row Source of the combo box.

2) You should only need to define the relationship with a single record. If you wanted to see:
Duane Father of Jacob
and
Jacob Son of Duane
You could do this with a union query. It might also help to have a table of RelationshipVsRelationship with records like:
Mother Female Son Male
Mother Female Daughter Female
Uncle Male Nephew Male
Uncle Male Niece Female
--etc--
This would define the relationship both ways depending on gender.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

In your relationship window, you probably have Persons shown twice. One is a copy, delete it. Access tacks a numeric onto copies of tables.
As suggested by Mr. DHookom, you should have only one table and then in a query you can do a self-join. This is a classic example of a self-join that is in basic Access books. Also, it has been answered before in these forums. Just do a search.