I am pulling data from 3 tables via a query. The query works however I am getting too many results. The biggest issue is each business might have more than one contact person. When there is more than one contact person I get a record for each contact. I only need the first contact.
SELECT DISTINCTROW Left(tblBusinessLicenseContactItems.fldContactPers on,InStr(tblBusinessLicenseContactItems.fldContact Person, " ")-1) AS FirstName, Right(tblBusinessLicenseContactItems.fldContactPer son,Len(tblBusinessLicenseContactItems.fldContactP erson)-InStr(tblBusinessLicenseContactItems.fldContactPer son," ")) AS LastName, tblBusinessLicenseContactItems.fldEmailAddress, tblBusinessLicense.fldBusinessName, tblBusinessLicense.fldBusinessPhone, tblBusinessLicense.fldBusinessAddress, tblBusinessLicense.fldBusinessCity, tblBusinessLicense.fldBusinessZip, tblBusinessLicense.fldBusinessState, 'US' AS Country, tblBusinessLicense.fldBusinessDescription
FROM (tblBusinessLicense INNER JOIN tblBusinessLicenseItems ON tblBusinessLicense.fldBusinessID = tblBusinessLicenseItems.fldBusinessID) INNER JOIN tblBusinessLicenseContactItems ON tblBusinessLicense.fldBusinessID = tblBusinessLicenseContactItems.fldBusinessID
WHERE (tblBusinessLicenseItems.fldBusinessLicenseNumber> =Format(Now(),"yyyy") & "0000")
ORDER BY tblBusinessLicense.fldBusinessDescription, tblBusinessLicense.fldBusinessName;
I have to use distinctrow as not all of the businesses have contact person information entered correctly. Some only have 1 name while other have no information at all. I will be having a chat with our data entry person about this. If I use distinct and there is only 1 name I get an error (invalid procedure call). If there is no contact person the the record is not returned.
So I have 3 issues:
1) only return 1 record for each business regards of the number contacts.
2) don't error if the contact name is a single name only like Jeff or Barb.
3) return records that don't have a contact person.
What am I doing wrong?