Code:
INSERT INTO directoryFINAL ( LASTNAME, ComboName, LotNumber, segment, Member, ADDRESS, CITY, STATE, ZIP, T1, TelType, T2 )
SELECT Members.LASTNAME, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[LASTNAME] & ", " & [FIRSTNAME] & " and " & [spouse] & " " & [LASTNAME],IIf([spouselastname]<>[lastname],[lastname] & ", " & [firstname] & " and " & [spouse] & " " & [spouselastname],[lastname] & ", " & [firstname])) AS ComboName, primarylots.LotNumber, primarylots.segment, IIf(Right([comboname],13)=' and , ',Left([comboname],Len([comboname])-8),[comboname]) AS Member, Members.ADDRESS, Members.CITY, Members.STATE, Members.ZIP, TelephoneNumbers.TelephoneNumber AS T1, TelephoneNumbers.TelType, IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],"") AS T2
FROM ((Members INNER JOIN TelephoneNumbers ON Members.MemberID_PK = TelephoneNumbers.MemberID_FK) INNER JOIN primarylots ON Members.MemberID_PK = primarylots.MemberID_PK) INNER JOIN TelephoneNumbers AS TelephoneNumbers_1 ON Members.MemberID_PK = TelephoneNumbers_1.MemberID_FK
WHERE (((TelephoneNumbers.TelephoneNumber) Is Not Null) AND ((IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],""))<>[telephonenumbers].[TelephoneNumber]) AND ((Members.Inactive)=False))
ORDER BY Members.LASTNAME;
Then I tried this:
Code:
INSERT INTO directoryFINAL ( LASTNAME, ComboName, LotNumber, segment, Member, ADDRESS, CITY, STATE, ZIP, T1, TelType, T2 )
SELECT Members.LASTNAME, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[LASTNAME] & ", " & [FIRSTNAME] & " and " & [spouse] & " " & [LASTNAME],IIf([spouselastname]<>[lastname],[lastname] & ", " & [firstname] & " and " & [spouse] & " " & [spouselastname],[lastname] & ", " & [firstname])) AS ComboName, primarylots.LotNumber, primarylots.segment, IIf(Right([comboname],8)=' and , ',Left([comboname],Len([comboname])-8),[comboname]) AS Member, Members.ADDRESS, Members.CITY, Members.STATE, Members.ZIP, TelephoneNumbers.TelephoneNumber AS T1, TelephoneNumbers.TelType, IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],"") AS T2
FROM ((Members INNER JOIN TelephoneNumbers ON Members.MemberID_PK = TelephoneNumbers.MemberID_FK) INNER JOIN primarylots ON Members.MemberID_PK = primarylots.MemberID_PK) LEFT JOIN TelephoneNumbers AS TelephoneNumbers_1 ON Members.MemberID_PK = TelephoneNumbers_1.MemberID_FK
WHERE (((TelephoneNumbers.TelephoneNumber) Is Not Null) AND ((IIf(Not IsNull([telephonenumbers_1].[TelephoneNumber]),[telephonenumbers_1].[TelephoneNumber],""))<>[telephonenumbers].[TelephoneNumber]) AND ((Members.Inactive)=False))
ORDER BY Members.LASTNAME;