FROM and JOIN Problems

[mat_splat]

Probably basics to you guys but still having loads of trouble and would
appriciate any help.

I have a table "tblNames" consisting of [ID] [firstname] [surname]
I have another table "Master" [ID] [Name] [Email] [Notes] - there is a form
built from this table "Issues"


Field Master.Name combines tblNames Firstname & Surname fields This displays
in the master table as the full name.
I now want to complete the Master.Email field using the Master.Name field.
I have managed to do this in the "Issues" form by using Master.Name as the
control source for the Issues.Email combo-box and it works.
However this does not complete the Master.Email field in the "Master" table...

SO I was told to try

SELECT FirstName & " " & SurName As FullName, Email FROM Master JOIN
tblNames ON Master.Name = tblNames.ID

Returns: Syntax Error in FROM Clause... poss change to "....FROM tblNames
JOIN....? " No Good!

ok... so have got rid of FROM error by using

SELECT tblContact.ContactID, [firstname] & " " & [Surname] AS Assigned FROM
tblContact INNER JOIN tblContact ON tblFault.Assigned = tblContact.ContactID;

(NOTE: Have changed the Table Names issues - tblFault and tblNames -
tblContact)

NOW I get Syntax Error in JOIN Operation... really dont get this Please
Please Please Help

 

[Ken Snell]

You're using the same tblContacts table twice in your join, which I don't
think you want to do. From the ON clause, it appears that you want the
tblFault table to be used too:

SELECT tblContact.ContactID, [firstname] & " " & [Surname] AS Assigned FROM
tblContact INNER JOIN tblFault ON tblFault.Assigned = tblContact.ContactID;