Aliasing JOINs

[Matt Volatile]

Hello again, yet another query!

How would I give a set of JOINed tables an Alias using AS?

If the all the columns I needed to reference were in the same table, I could
use

SELECT Field1, Field2
FROM table AS alias

However, I need to reference two joined tables as a single Alias.

I tried FROM table1 INNER JOIN table2 ON
[table1].[field1]=[table2].[field2] AS alias

but that returned an error.

What's the correct syntax?

 

[Gerald Stanley]

As far as I know, you can only give a column, table or query an Alias. You
can use the aliases in expressing a join e.g.
FROM table1 AS alias1 INNER JOIN table2 AS alias2 ON alias1.col1 = alias2.col1

Hope This Helps
Gerald Stanley MCSD

 

[John Spencer (MVP)]

One method that will work with Access as long as you have no special characters
or spaces in the table and field names is shown below. NOTE the SQUARE BRACKETS
AND THE PERIOD after the last bracket. If for ANY reason you would need to use
square brackets anywhere in the query in the from clause you are out of luck
with this method.

SELECT A.*
FROM [SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field2]. as A

The other way is to write the first query, save it and then use it as the basis
for the second query.
QueryA
SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field2

Then used the saved queryA

SELECT A.* FROM QueryA as A

This second method has no problems with brackets being in the saved query.