Self Refferential Table

[JAL3]

I would rather remain here and continue but the hospital pager beckons me.

I shall return.

[JAL3]

Is this how you would want to display your data for a specific Church?

I am humbled by the efforts put forth for my benefit. Thank you.

The image you put up heads in the right direction but I would note the following:


1. There is no place for the date of a disestablishment of the denomination. It ceases to exist at that point.

2. The example shown, MEC, is an interesting case. It gave rise to several child denominations including the PMC and the MECS while it continued to thrive. In 1939 the MEC was reunited with the PMC and MECS to form METH. At that point, MEC and MECS were disestablished and ceased to exist. Most, but not all of the PMC was part of the merger that became the METH but the PMC continues to exist today.

3.The selected church, MEC, has no place to enter notes.

4. The parent and child denomination do have places to enter notes. I would dispense with that and have such entry possible only when that parent or child was the active record.

5. There are spaces for 2 parents. That is quite possible but the actual number could be only one or it could be more than 2.

6. it may be that in your example, the parent or child listings populate initially with a single entry and that each entry filled I generates another space for a new entry. I don't know.

7. having made the spreadsheet and seeing your form make me think that the idea I posited earlier might be the correct approach, thus:


A. as tblDenominations is populated, entries are automatically generated for tblParentDenom and tbleChildDenom.

B. the IDs/keys for tbDenominations, tbeParentDnom and tblChildDenom are the same.

C. the entries for the parent and child tables would by default be orphans unless they are linked at some later point.

D. when selecting parents or children for the main table, a subroutine would generate an entry into the appropriate junction table.

E. the parent and child tables exist only to establish relationships. Since the keys are the same, they can be used in queries to get relevant data from the main table.

[mike60smart]

I think you need to give us an example of how the example I uploaded should be displayed.

You know your data but we are only guessing.

[mike60smart]

Hi

Not following your logic for statements A-E

I have shown the relationships between the Main Form (Denominations) and the related Parent/Child Denominations.

Your statement "A"
"A. as tblDenominations is populated, entries are automatically generated for tblParentDenom and tbleChildDenom."

In Access when you add a New Parent RECORD you would only be able to enter records for the Parent/Child denominations if you know the Values ?

Your statement "B"
As you can see the ID's in green are all the same for all related records.

Your statement "C"
If the relationships are set up correctly then you would not be able to add Orphaned records.

Your statement "D"
"D. when selecting parents or children for the main table,..."
You cannot select a Parent and a Child for the Main table.

Your statement "E"
You would use queries to obtain correct data.