Problem with my 'FROM' clause 1

[Slippenos]

I have 2 tables .. they have a one to one relationship.
(mock field names and tables)

[u]Table1[/u]                  [u]Table2[/u]
SpecialId <-----------> SpecialId
Field1                  Field3
Field2                  Field4

I am trying to create a query like so:

SELECT SpecialId, Field1, Field2, Field3 FROM Table1, Table2 WHERE ...

The problem is obvious:

The specified field 'Field1' could refer to more than one table listed in the FROM clause of your SQL statement.

Is there a workaround?
Any thoughts?

[red]Tools | Internet Options | Clear History[/red]

 

[PHV]

SELECT A.SpecialId, A.Field1, A.Field2, B.Field3
FROM Table1 A INNER JOIN Table2 B ON A.SpecialId = B.SpecialId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Slippenos]

OK, I tried:

SELECT A.DataRequestNo, A.CsStatus, B.NinaStatus, B.signature 
FROM tblDataRequests A INNER JOIN tblNinaInfo B ON A.DataRequestNo = B.DataRequestNo 
WHERE ...

On:

[u]tblDataRequests[/u]                   [u]tblNinaInfo[/u]
DataRequestNo <-----------------> DataRequestNo
CsStatus                          NinaStatus
                                  signature

And I get the same error. Did I do it right?
Thanks for the reply.


-Mike

[red]Tools | Internet Options | Clear History[/red]

 

[PHV]

What is the REAL error message ?
Why not posting the FROM clause too ?
I guess you forgot to qualify the column names in the WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Slippenos]

I forgot to include the new column names in my WHERE clause. My new SQL statement looks like this:

SELECT A.DataRequestNo, A.CsStatus, B.NinaStatus, B.signature 
FROM tblDataRequests A INNER JOIN tblNinaInfo B 
ON A.DataRequestNo = B.DataRequestNo 
WHERE
A.CsStatus   = '" & statusdrf & "'
AND  B.NinaStatus = '" & statusnina & "'
ORDER BY A.DataRequestNo

Thanks PHV!

[red]Tools | Internet Options | Clear History[/red]