Prev: Is there anything wrong with the syntax
Next: Query from day of week
From: mat_splat on 13 May 2010 05:42
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] [email]
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

From: Ken Snell on 13 May 2010 07:03
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;

--

Ken Snell
http://www.accessmvp.com/KDSnell/




"mat_splat" <mat_splat(a)discussions.microsoft.com> wrote in message
news:799E7C25-C275-4EC5-ACCD-F0872E53D17B(a)microsoft.com...
> 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] [email]
> 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
>


 | 
Pages: 1
Prev: Is there anything wrong with the syntax
Next: Query from day of week