Dear JWRose:
When your query requires there by more than one table for it to
function, it is not unusual for it to include tables that may have
like-named columns. So, when you refere to these columns in the
SELECT clause, in the WHERE clause, in an ON clause, a GROUP BY or
HAVING, or in any other way, it is essential that you specify from
which table you want to table to use this column. Otherwise, it is
ambiguous. That's understandable, isn't it?
When you're entering the SQL text of a query, and when the query will
reference multiple tables, it is a good practice to use a syntax that
references every column with the table in which you wish the reference
made, even for those columns that are only in one table. This syntax
is:
TableName.ColumnName
instead of just:
ColumnName
I make it a practice to always user aliases when I do this. Create
the aliases in the FROM clause. Instead of just using:
TableName
in the FROM clause, use:
TableName Alias
When you do this, the TableName CANNOT then be used to reference that
table. You MUST use the Alias everywhere else in the query in its
place.
My aliases are generally one or two characters long, making the query
much shorter and easier to read.
The only time you can avoid this is when you have included tables in
the query you find you don't really need. You could then remove those
tables from the query, thereby reducing the chance of having
like-named columns.
Rather than being concerned with finding and eliminating ambiguous
column references, if you will just follow the practice of naming all
columns with their table or alias name you will avoid the problem
altogether. Note again that this is not necessary in queries that
reference only a single table or query.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
