1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Linking 2 different fields to the same table

Discussion in 'Business Applications' started by Charmian, Aug 3, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    I have a table that has a commitee member name and a nominee name. Both these are entered in a form by using a lookup to a tab1e of membernames. Now I want to print both names on a report and I need to join my table to the table that decodes the name twice. I just can't figure it out.
    SELECT CCP.ABR, CCP.MemberCatABR, CommitteeMemberNames.surname, CommitteeMemberNames.title
    FROM CCP INNER JOIN CommitteeMemberNames ON CommitteeMemberNames.ID = CCP.Nomineename AND
    (INNER JOIN CommitteeMemberNames C2 on C2.ID = CCP.ID)
    I have tried
    FROM CCP INNER JOIN CommitteeMemberNames C1 ON C1.ID = CCP.Nomineename AND
    (INNER JOIN CommitteeMemberNames C2 on C2.ID = CCP.ID)

    I need to get a Title, Name, Surname for both the Member (CCP.ID) and the Nominee (CCP.Nomineename). I used to have an actual name in Nomineename, but that was because I wanted to incorporate it quickly. Now I have time to do it properly and I'm struggling. This is the type of SQL I have BEFORE the nomineename became a lookup.
    SELECT Committees.ABR, CCP.EndDate, IIf([ccp.Nominee]=-1,[ccp].[nomineename],[Title] & " " & [Initials] & " " & [Surname]) AS MemName, [ccp].[MemberCATABR] & " " & [MembershipCategory].[Description] AS MemCat, CCP.StartDate, CCP.Chairperson, CCP.ViceChairperson, CCP.Dependency, CCP.MemberCatABR, CCP.ID, Committees.Minutes, CCP.NoVotingRights, CCP.Nominee, CCP.ExOfficio, CCP.Advisory
    FROM MembershipCategory INNER JOIN (Committees INNER JOIN (CommitteeMemberNames INNER JOIN CCP ON CommitteeMemberNames.ID = CCP.ID) ON Committees.ABR = CCP.ABR) ON MembershipCategory.MemberCatABR = CCP.MemberCatABR
    WHERE (((CCP.EndDate)>Date()-180 Or (CCP.EndDate) Is Null))
    ORDER BY Committees.ABR, CCP.Chairperson, CCP.ViceChairperson;
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Why not just use the Query Wizard and then add the other table in design view?
    Is there relationship set up between the Main & lookup tables?
     
  3. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    yes, there is a relationship set up. the table has ID which is one to many to the key of the lookuptable and nomineename (also a integer) one to many with the lookuptable. when the information is captured, the member details must be in the lookuptable. I use a dropdown to identify the membername when capturing the information in the main table, but only store the ID-key in the main table. Same with NomineeName - also uses the lookup table dropdown to find the member. On the Relationship view it shows the Main table and links the ID to lookup table ID and the Nomineename links to ID in lookup table. So it shows lookuptable and lookuptable_1 in the relationship diagram.

    How do I get more than just the code to appear. I want the following to have the title, initials & surname for the nomineename code too:
    MemName: IIf([ccp.Nomineename] Is Not Null,[Title] & " " & [Initials] & " " & [Surname] & " OR Nominee: " & [ccp].[nomineename],[Title] & " " & [Initials] & " " & [Surname])
    This causes it to show for example: Prof K Allweather OR Nominee: 236 when I want Prof K Allweather OR Nominee: Dr C Lear

    I've just tried it with running a inner join on the table created in the above step to join to only the nominees, but it doesn't give me all the records only those that have nominees. but for those that have nominees I now get Prof K Allweather OR Nominee: Dr C Lear
     
    Last edited: Aug 6, 2018
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    On the Query in Design view you can change how the data is interpretated by the SQL by right clicking on the link line.
    Can you provide some dummy data in a table or Excel sheet that I can play around with?
     
  5. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    I've coded around this by running two queries (1 for no nominee and 1 for with nominees) and then run a UNION, but will try this right clicking on the link line - I will try that in my morning. If it doesn't work, I'll create a dummy table. Good night and thank you for now.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Good night where you are, it is 4:20pm here in the UK.
     
  7. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    Hello Chariman,

    Try the following modification; however, I would suggest defining the remainder of the IIF condition set to prevent potential problems down the road..... What happens if [ccp.Nomineename] Is Null? See modifications in red. :)

    MemName: IIf([ccp.Nomineename] Is Not Null,[Title] & " " & [Initials] & " " & [Surname] & " OR Nominee: " & [ccp].[Nomineename] & "," & [Title] & " " & [Initials] & " " & [Surname], [What happens if Is Null])

    Good Luck
     
  8. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    Hi OB
    In South Africa, but it's winter and when its cold, I go home earlier. and yesterday, I will admit to been miserable, cold and decidedly crotchety ! And yes it may only have been 10 C, but I can't cope with it so cold.It came up with an error when I tried to link the second field to the initial table. the Union solution is longer (instead of 1 step, its 1 sql for members with nominees, 1 sql for members with nonominees and the union sql. The table has so many relationships to other tables in this little system, that it will be really time consuming to give a 'cleaned' list with dummy data. And since it deals with personal information, I don't want to make a mistake. But, as always, thank you for your time. upload_2018-8-7_9-54-2.png
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Charmain, is the database working OK?
    I just wondered because you have Key fields related to key fields, normally key fields are "No Duplicates" fields and therefore only allow one Record per Key.

    I know what you mean about the temperature, I have a relative in Australia who was born here but quite often finds it cold when she comes back.
    We have had a sample of it ourselves with this current hot weather as we soon acclimatised to up to 30C and now when it drops to 16C - 18C it feels quite cold.
     
  10. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    199
    Hi, the database works very well and is very effective. I think my naming is ugly in places, as there may be ID in a few placed, but not all are the SAME ID. Naughty, I know. The database is for the various committees and their meeting dates. Each committee has a grouping of member categories. There are people that 'own' the category due to government appointment or due to their positition and others that are appointed by vote. The same category may belong to different people for the different committees. I use it to produce attendance registers and also stats at year end, to see the attendances at the various meetings. But with nominees that may attend, they may not attend if the main member or 'owner' of the category is also present. Its to ensure legal constitution and quoram of the various decision making bodies within the Institution. You know, after working here a while, I totally understand God not asking Noah to form a committee to build the Ark - just definite directions and then it got done! LOL
    I know my husband arrived here and was all gung ho, laughing at us getting cold in 'swimming 'weather - it only took one of our summers and he got cold with the rest of us! Our ability to adapt is our advantage and our downfall!
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Tags:
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1213931

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice