ambiguous error

  • Thread starter Thread starter Jimbo
  • Start date Start date
J

Jimbo

I have a query with multiple tables with a one to one relationship except
one table, it is a one to many. When I run the query I get the ambiguous
error. If I understand the error, it is telling me to create one query with
two tables, one being with my one to many table and put all the other tables
in another query and join the two queries. My question is how do I join two
queries to make one?
 
The "ambiguous" error normally only occurs when you write your own sql
statements. If you do it through the QBE interface, Access should prevent
this.
The error is saying that you have specified a field name that occurs in more
than one table, and itdoesn't know which to use
You might have table1.Amount and table2.amount, but in your query you have
just specified "amount". To prevent this error occuring always qualify the
field with the table name.
 
I have found that when mixing inner and outer joins (or outer joins that
match on more than one field), I will get this error. I am able to work
around it by writing the SQL for the query manually. The query can usually
display in 'design' mode, but futher alterations in design mode my breakt
the query again, and you'll have to got back to SQL mode to fix up the
joins.

The above can help you do things with just one query.

If you want to use your approach of joining queries, just create an save
your first query(s). Then you can create a new query, and add any required
queries or tables to the new query. A query can reference tables and/or
queries - it treats the queries it's querying just as if they were tables.
The "Show Table" dialog used to add tables to a query has a few tabs. You
can add tables from the "Tables" tab, and/or switch to the "Queries" tab and
add existing queries to your new query.
 
Back
Top