Page 1 of 1

[Solved] Can a table be related to itself?

PostPosted: Sun Sep 15, 2019 7:49 am
by 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

Re: Can a table be relted to itself?

PostPosted: Sun Sep 15, 2019 8:41 am
by Villeroy
Code: Select all   Expand viewCollapse view
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.

Re: Can a table be related to itself?

PostPosted: Mon Sep 16, 2019 5:09 am
by gkick
Thanks Villeroy, if I use this as data source for the table control, will that then allow additions to the Identities form ?

Re: Can a table be related to itself?

PostPosted: Mon Sep 16, 2019 1:39 pm
by Villeroy
I don't know. May be you need a one-table form and a subform with related records of the same table.