Inner Join with Multiple fields

E

Ernst Guckel

Hello,

When i execute this it does not match up any contacts with any companies
without data in address1. It should just use address1 as criteria for
finding out which 'ABC, Inc' to use... am i missing something? All contacts
have a companyname, some companyies exist more than once but with diferent
addresses... some companies have multiple contacts but with diferent
locations... i am trying to get the right contact to the right company AND
location...

Thanks.

INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DISTINCT B.CompanyID, A.LastName, A.FirstName, A.Email, A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
FROM CardScanData AS A INNER JOIN tblCompanies AS B ON
(A.Company=B.CompanyBusinessName AND A.Address1=B.CompanyAddress1);
 
E

Ernst Guckel

Still same result. If I run an unmatched query of Companies and Contacts the
21 missing from the append are those without addresses...

KARL DEWEY said:
Use a left join.
--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
Hello,

When i execute this it does not match up any contacts with any companies
without data in address1. It should just use address1 as criteria for
finding out which 'ABC, Inc' to use... am i missing something? All contacts
have a companyname, some companyies exist more than once but with diferent
addresses... some companies have multiple contacts but with diferent
locations... i am trying to get the right contact to the right company AND
location...

Thanks.

INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DISTINCT B.CompanyID, A.LastName, A.FirstName, A.Email, A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
FROM CardScanData AS A INNER JOIN tblCompanies AS B ON
(A.Company=B.CompanyBusinessName AND A.Address1=B.CompanyAddress1);
Click to expand...
Click to expand...
 
K

KARL DEWEY

Let us back up. If you are trying to append records why are you looking for
a match?

Restate what you are trying to do with this query.

--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
Still same result. If I run an unmatched query of Companies and Contacts the
21 missing from the append are those without addresses...

KARL DEWEY said:
Use a left join.
--
KARL DEWEY
Build a little - Test a little


Ernst Guckel said:
Hello,

When i execute this it does not match up any contacts with any companies
without data in address1. It should just use address1 as criteria for
finding out which 'ABC, Inc' to use... am i missing something? All contacts
have a companyname, some companyies exist more than once but with diferent
addresses... some companies have multiple contacts but with diferent
locations... i am trying to get the right contact to the right company AND
location...

Thanks.

INSERT INTO tblContacts ( ContactCompany, ContactLastName, ContactFirstName,
[ContactE-mail], ContactJobTitle, ContactBusinessPhone, ContactMobilePhone,
ContactFaxNumber, Notes )
SELECT DISTINCT B.CompanyID, A.LastName, A.FirstName, A.Email, A.Title,
A.MainPhone, A.PhoneMobile, A.Fax, A.Notes
FROM CardScanData AS A INNER JOIN tblCompanies AS B ON
(A.Company=B.CompanyBusinessName AND A.Address1=B.CompanyAddress1);
Click to expand...
Click to expand...
Click to expand...
 
Top