[Solved] Can a table be related to itself?

[gkick]

Hi,

Not sure if what I want to do is possible, its kind of a table looking itself up.
The scenario is depicted in the screen dump below.

Main form listing Identities like credit cards, web logins etc, one record at a time. PK is iid auto
On the right a table control based on a query taking the iid from the identity table as well as the fields from a couple of reference tables.

The browsing works all fine, but I can not figure out how to modify the form to allow for additions of new records, getting integrity msg. All the properties are set to allow additions. I understand its not the 1 to 1 or 1 to many nor many to many, its more like the table having a relationship with itself.
Anyone can think of a work around?

Thks

[Villeroy]

Code: Select all

SELECT "A".*, "B".* FROM "Table" AS "A" JOIN "Table" AS "B" ON "A"."X"="B"."Y"

compares 2 instances A and B of the same table.

[gkick]

Thanks Villeroy, if I use this as data source for the table control, will that then allow additions to the Identities form ?

[Villeroy]

I don't know. May be you need a one-table form and a subform with related records of the same table.