rjmccafferty
IS-IT--Management
I am trying to design a query in which I have a list of Names in a table (first and last names combined, e.g. John Smith) indicating the recipients of a book, and I want to limit the results of the query to those of individuals with the same last name who work for the same company.
That is, is Company A had books sent to John Smith and Harold Adams, I want to see which contacts at the same company have the last name of Smith or Adams.
I first linked the two tables by CompanyID and set the union to All records in the first table above with only those matching records in the second table above. Thus I am trying to see all matches for those people listed as receiving a book, but have no interest in those who did not receive a book.
The resulting SQL statement is:
SELECT PPM_History.PPM_Recipient, [contact].[fname] & " " & [contact].[lname] AS name, Contact.LName
FROM PPM_History LEFT JOIN Contact ON PPM_History.CustomerID = Contact.CustomerID
WHERE (((PPM_History.PPM_Recipient) Like "*[contact].[lname]*"
);
When I run the query, I get no hits when, in fact, I know there are many matches. If I remove the WHERE statement I get a row for every individual in each related company.
Ultimately, I am trying to find book recipients whose names may not be exactly correct in the table and am just trying to see all last name matches so I can visually check first names to see if they are accurate.
What am I doing wrong in my query?
That is, is Company A had books sent to John Smith and Harold Adams, I want to see which contacts at the same company have the last name of Smith or Adams.
I first linked the two tables by CompanyID and set the union to All records in the first table above with only those matching records in the second table above. Thus I am trying to see all matches for those people listed as receiving a book, but have no interest in those who did not receive a book.
The resulting SQL statement is:
SELECT PPM_History.PPM_Recipient, [contact].[fname] & " " & [contact].[lname] AS name, Contact.LName
FROM PPM_History LEFT JOIN Contact ON PPM_History.CustomerID = Contact.CustomerID
WHERE (((PPM_History.PPM_Recipient) Like "*[contact].[lname]*"
);When I run the query, I get no hits when, in fact, I know there are many matches. If I remove the WHERE statement I get a row for every individual in each related company.
Ultimately, I am trying to find book recipients whose names may not be exactly correct in the table and am just trying to see all last name matches so I can visually check first names to see if they are accurate.
What am I doing wrong in my query?
