Tech Support Guy banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
199 Posts
Discussion Starter · #1 ·
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;
 

·
Registered
Joined
·
19,896 Posts
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?
 

·
Registered
Joined
·
199 Posts
Discussion Starter · #3 · (Edited)
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
 

·
Registered
Joined
·
19,896 Posts
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?
 

·
Registered
Joined
·
199 Posts
Discussion Starter · #5 ·
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.
 

·
Registered
Joined
·
19,896 Posts
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.
Good night where you are, it is 4:20pm here in the UK.
 

·
Registered
Joined
·
666 Posts
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
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
 

·
Registered
Joined
·
199 Posts
Discussion Starter · #8 ·
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.
Product Rectangle Slope Font Parallel
 

·
Registered
Joined
·
19,896 Posts
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.
 

·
Registered
Joined
·
199 Posts
Discussion Starter · #10 ·
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!
 
1 - 10 of 10 Posts
Status
Not open for further replies.
Top