Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126

    Self Join problem...?

    I have a Membership app. When joining, a member might t be sponsered by an existing member (Like employees and supervisors)




    In my frmMembers There are 2 comboboxes. Sponsers: Selects a Member to display their record, and Sponser (List of Sponsers...(mostly members)Even putting the sponsers query into data mode won't allow me to add SponserID (MemberID)...........I've been working on this problem for a week with almost no progress, Help?!

    Attachment 47215[ One possible situation is that Access does not allow a combo box ob a self-join????
    Last edited by Synergy.ron@gmail.com; 02-01-2022 at 01:44 PM. Reason: thoughts...

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    If I understand you correctly your FIRST combo box is just a combo to select a member to navigate the form to that record?

    If so there should be no need for a self join in regard to the immediate issue of building your data entry form...
    1. Your form would simply be based off your members table
    2. Your 1st combobox would be unbound with a row source of something like 'SELECT IDMember, LastName & ", " & FirstName FROM tbl_members'
    3. Add code to the After Update event of the 1st combobox to navigate your form. I like to use the code provided by David-W-Fenton in this post https://stackoverflow.com/questions/...obox-in-access
    4. Your 2nd combobox would have a control source of [IDSponser] and rowsource that queries the members table. something like 'SELECT IDMember, LastName & ", " & FirstName FROM tbl_members WHERE IDMember <> Forms!frmMembers!IDMember'

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    In spite of image posted, queries are not linking the self-join on correct fields.

    What is purpose of SponsorFK field? IDSponsor is FK for sponsor.

    SQL for Sponsors query:

    SELECT Sponsors.IDSponsor, [Sponsors].[LastName] & ", " & [Sponsors].[FirstName] AS LF
    FROM tbl_members AS Sponsors INNER JOIN tbl_members ON Sponsors.IDMember = tbl_members.IDSponsor
    ORDER BY Sponsors.LastName;

    Then QrySponsors uses a field idemberFK which doesn't exist:

    SELECT Members.IDMember AS MemberID, Members.LastName AS MemberLastName, Members.fIRSTnAME, Sponsors.LastName AS SponsorLast, Sponsors.fIRSTnAME AS SponsorFirst, Sponsors.IDMember AS ISponsorID
    FROM tbl_members AS Sponsors RIGHT JOIN tbl_members AS Members ON Sponsors.IDMember = Members.IDSponsor;

    Okay, qry_Sponsors has correct link. The others are junk, failed attempts?

    Apparently, self-joins are not editable datasets and should only be used for report output.



    Note: I have corrected spelling of Sponser to Sponsor in tables and queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure why you need a separate table for sponsers - they are both identical - even to having a sponserID field.

    On your form for members, you just need the members table, not your query. And the control for IDSponser would be a combo with a rowsource of ID and membername from sponsers (or members if you decide you don't actually need to maintain two tables)

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    OP stated in another thread that tblSponsor is not used (there are no records in it). Why they don't just delete it is a mystery. And saving failed queries is another annoyance.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    not sure why you need a separate table for sponsers - they are both identical - even to having a sponserID field.

    On your form for members, you just need the members table, not your query. And the control for IDSponser would be a combo with a rowsource of ID and membername from sponsers (or members if you decide you don't actually need to maintain two tables)
    I assumed this was simply an aliased copy of the members table.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No, the db actually has a table tblSponser, which is apparently not in use.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    No, the db actually has a table tblSponser, which is apparently not in use.
    Ah okay. I didn't see the attachment. Looking at the sql of the query in question though [Sponsers] is an aliased tbl_members

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    In the attached the frmMembers has been modified to allow you to select a Sponser.

    You need to remove the Lookup fields from tables as these are a non no in Access.

    You also need to look at your table LodgeInfo.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  10. #10
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Quote Originally Posted by Ajax View Post
    not sure why you need a separate table for sponsers - they are both identical - even to having a sponserID field.

    On your form for members, you just need the members table, not your query. And the control for IDSponser would be a combo with a rowsource of ID and membername from sponsers (or members if you decide you don't actually need to maintain two tables)

    Wha I am after is a sponser list of members combobox.....possible?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure what that means.

    Source of names that can be selected as a sponsor is tbl_Members.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Right....SponserFK is an old, test field that I do not need...

  13. #13
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    On the file you sent, it seems likee it should work, but the IDSponser does not update from the combo box. What don't I see? thanks...

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    In the attached file I displayed the IDSponser Control to show that it updates when you select using the Combobox
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Yes, the IDSponser fk field will populate, BUT if i add sponsers![lastname] to my form it shoes nothing. Is it an Access limitation that when in form mode you can not show related fields (i.e. [Lastname]) from Sponsers? It shows ok in data format.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left join problem
    By therzakid in forum Access
    Replies: 24
    Last Post: 03-22-2014, 03:51 PM
  2. problem in self join query
    By royalhishighness in forum Queries
    Replies: 2
    Last Post: 12-30-2013, 02:27 PM
  3. Left join problem
    By e.badin in forum Queries
    Replies: 5
    Last Post: 01-17-2011, 08:03 AM
  4. Problem with Join
    By sujitshukla in forum Queries
    Replies: 1
    Last Post: 08-26-2010, 07:25 AM
  5. Many to many self-join problem
    By dbdbdo in forum Database Design
    Replies: 1
    Last Post: 07-18-2010, 09:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums