Reference to more than one table


J

jwrnana

When I am running queries/reports, I get the message that one of my fields;
i.e. "Unit Price", could refer to more than one table in my SQL statement.

How do I avoid this?

Is there someway to find duplicates and then eliminate them?

I am a novice at this, and seem to have created some problems when building
my tables.

Your assistance is greatly appreciated.

Joy Rose
 
Ad

Advertisements

T

Tom Ellison

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top