Self join confusion

[Synergy.ron@gmail.com]

I have a members app. each member can have a sponser (doesnot have to have a sponser.) I am trying to ultimately setup a combobox to select the sponser. But, if I relate MembersFK to Sponsers PK in the underlying query, (qrySponsers) fails. ONLY if I do not have a relationship do I get results.



What am I doing wrong? thanks!


Attachment 46525 Attachment 46526

[Micron]

You probably need an outer join. Which table comes first determines if the join is LEFT or RIGHT, so just double click on the join line in query design and pick one of the 2nd or 3rd options. When you read the explanation above the selection, you should understand why it probably doesn't work - not that you explained what "fails" means.
I'm guessing you want all members regardless if they have sponsors. Your equal join means they have to be both (be in both tables).

Watch out for the consistency of spelling members.

[Synergy.ron@gmail.com]

Right. I want all members in my sponsers dropbox. It really is ok if they are not in the Sponsers table. I have tried all the relatioship combinations to no avail. have you gotten it to work? Admittedly my unrstanding comes from watching utube videos, but the creator did not address this situaion thanks

[Micron]

Didn't download your db as I figured it would be that simple. Will have to give it a look I guess. Looks like you might have an IIF expression. Did you try your joins without the calculated field?

[Micron]

I open your query and see

MemberID	MemberLastName	fIRSTnAME	SponserLast	SponserFirst	ISponserID
1	Wazneac	William
2	washington	george
3	Wazneac	Woodrow
5	wazneac	Steven
9	Flintstone	fred
10	Jetson	george
11	Rubble	Barney
13	zorro	Mark
16	zorro2	Mark2
18	gordono	ronaldo
19	wilson	woodrow

So what am I to make of that? Again, no idea of what "fails" means. Your posted db uses an outer join but your post not only doesn't, you've said it doesn't matter-

I have tried all the relatioship combinations to no avail.

[Micron]

OK, you have no data in sponsers (sp) table, so not sure what you expect to see.

Edit - had to remove some of this post because I'm stumbling through due to lack of instructions and I have to step out for a bit. I see that those "tables" are queries thus are not aliased tables. Regardless, there is no data in the sponsors table so that conundrum remains.

[Micron]

Seems you are attempting to get member/sponsor data from self joining the members table. First, did you not get an error when trying to run the sponsors query? You'd have to fix the ambiguity there by specifying the table to get the field data from. Then if you use a Left join you get something. I have no idea if those results are valid.

[June7]

Members and sponsors are all people. Have one table. Eliminate tblSponsers, especially since there is no data in it.

Review http://allenbrowne.com/ser-06.html

SponserFK field needs to be changed to number type. Then self-join:

SELECT DISTINCT Sponsors.SponserFK, tbl_members.[prefix] & " " & tbl_members.[Lastname] & ", " & tbl_members.[Firstname] & " " & tbl_members.[m_Name] & " " & tbl_members.[suffix] AS FullName, tbl_members.LastName
FROM tbl_members RIGHT JOIN tbl_members AS Sponsors ON tbl_members.IDMember = Sponsors.SponserFK
ORDER BY tbl_members.LastName;



As micron noted, sponser is a misspelling of sponsor.

[Synergy.ron@gmail.com]

I do not use the sponsers table. The problem is self join on MEMBERS using qrySponsers

[Synergy.ron@gmail.com]

Seems Like it is working. I noticed that the isSponserfk field property was set to long AND was not able to be set to INTEGER LONG. Ultimately I deleted and recreated the field and baboom works as expected. ONward to creating a combo box instead of a text box..... Thanks to al (especially micron)