self-join possible?

[Synergy.ron@gmail.com]

I have spent the last few months trying to get this to work:


I HAVE A MEMBERSHIP DATABASE WHICH HAS A MEMBERS TABLE. WHICH HAS mEMBER_PK and sponser_fk (long integer)

Each member has a sponser (another member so there is a self-join relatioship. I can get this to work in datasheet view BUT IT WILL NOT WORK ON FORM VIEW. WHAT AM I DOING WRONG??????

I will post the app tomorrow.......6/11/2022 gotta jet....

[orange]

Please show us your table design and a short blurb about your application.

Mock up: Usage of self-join -- Based on All sponsors are Members, but NOT ALL members are sponsors

tblMember

memid	memname	sponsorID
1	John	8
2	Jim	3
3	Barb	2
4	Les	7
5	Ken	2
6	Ivan	1
7	Igor	2
8	Crystal	4
9	Amber	3

SQL for MemberHasSponsorQ

SELECT m.memname AS member, s.memname AS sponsor
FROM tblMember AS s LEFT JOIN tblmember AS m ON s.memid = m.sponsorID
WHERE (((m.sponsorid) Is Not Null))
ORDER BY s.memname;

member	sponsor
Amber	Barb
Jim	Barb
John	Crystal
Les	Igor
Igor	Jim
Ken	Jim
Barb	Jim
Ivan	John
Crystal	Les

MembersWhoAreNotSponsorsQ

SELECT tblMember.memname
FROM tblMember
WHERE (((tblMember.memid) Not In (SELECT distinct tblMember.sponsorID
FROM tblMember)));

Result is

memname
Ken
Ivan
Amber

Hope this is helpful.

[Synergy.ron@gmail.com]

What i am after is a form that allows me to input from a combobox the members and their pk, then upon selection, the Memberpk would be inserted into member![SponserFK] completing the relatioship......

[davegri]

In your download, I see that you are building around the nice working functional DB that I emailed you in February 2021. The membership form allows sponsor assignments via a simple combobox. That database has been thoroughly mishandled and spoiled with unadvisable changes that have rendered it completely unusable.
If you'd like a fresh start, here's pristine copy of it again. I suggest you make it a MASTER copy to save and not modify. You can return to the pristine version to make working copies whenever you might require additional fresh starts.

LodgeMembership-davegri-v03.zip

[Synergy.ron@gmail.com]

ThanksDave, goog igea to keep an un altered copy of your solution. It does seem to have the problem of no sending thepk to the fk. Something for me to learn from. Thanks for your help.