alias...don't alias at all

[rocco]

Hello,
i have this statement

SELECT DSPC_PATIENT.ID, C_PROTO.ID AS [in c_proto]
FROM DSPC_PATIENT LEFT JOIN C_PROTO ON DSPC_PATIENT.ID = C_PROTO.ID IN
'C:\Documents and Settings\rocco n forgione\My
Documents\CLIENTS\GRANT\grant_2000.mdb'
WHERE (((DSPC_PATIENT.ID)>35));

Well... despite the "AS"...I still see the the original column names in
table instead of the name I decided with "AS" in the statement.
Something wrong?

 

[Andreas]

Have a look at the table in design view.
Is the "Caption" property for this field set?
If so, that is the culprit.

Regards,
Andreas

 

[Van T. Dinh]

Andreas got the correct cause of aliasing problem.

I am not sure whether this makes any different but in my SQL Strings, I use
the IN Clause directly after the Table name rather than after the ON clause
like your SQL.

 

[rocco]

Actually van, I tried your way to code the "IN" clause, here is the *new*
statement:

SELECT DSPC_PATIENT.ID, C_PROTO.ID AS C_PROTO
FROM DSPC_PATIENT IN 'C:\Documents and Settings\rocco n forgione\My
Documents\CLIENTS\GRANT\grant_2000.mdb' LEFT JOIN C_PROTO IN 'C:\Documents
and Settings\rocco n forgione\My Documents\CLIENTS\GRANT\grant_2000.mdb' ON
DSPC_PATIENT.ID=C_PROTO.ID
WHERE (((DSPC_PATIENT.ID)>35));

But it wont work.

Andreas got the correct cause of aliasing problem.

I am not sure whether this makes any different but in my SQL Strings, I use
the IN Clause directly after the Table name rather than after the ON clause
like your SQL.

--
HTH
Van T. Dinh
MVP (Access)

Hello,
i have this statement

SELECT DSPC_PATIENT.ID, C_PROTO.ID AS [in c_proto]
FROM DSPC_PATIENT LEFT JOIN C_PROTO ON DSPC_PATIENT.ID = C_PROTO.ID IN
'C:\Documents and Settings\rocco n forgione\My
Documents\CLIENTS\GRANT\grant_2000.mdb'
WHERE (((DSPC_PATIENT.ID)>35));

Well... despite the "AS"...I still see the the original column names in
table instead of the name I decided with "AS" in the statement.
Something wrong?

 

[Van T. Dinh]

Interesting!

I didn't know that both Tables were external. I assumed that the first Table
is local and second is external. The syntax I suggested works if only the
second Table is external.

It looks like when you used it after ON Clause, the IN Clause applies to
both Tables in the Join.