I am trying to create an email table. I want the person to pick a record (using drop down list - I need to combine first and last name, which are separate fields) and it automatically show first name, last name and use the email address of that person. I am using two tables with a join (Employer and CCEmployerHold). The Employer picks his own company and inputs the subject. His email is then used as the FROM: field. All the rest of the data is being chosen from the CandidateCCHold table (First Name, Last Name and Email Address) of the person being emailed to.
Here is my SQL query:
SELECT
Employer.EmailADDR,
Employer.CompName AS subject_custom,
Employer.CompName AS from_custom,
CCEmployerHold.FirstName,
CCEmployerHold.SurName,
CCEmployerHold.EmailAddr AS EmailAddr1
FROM Employer
INNER JOIN CCEmployerHold ON Employer.EmployerID = CCEmployerHold.holdby
I am pretty sure my join is wrong. How do I approach this?
Mike