Ambiguous Outer Join Problem - Can't Resolve With Separate Query - Think I'm Circular Referencing

[sdrmason]

Right, Ihave an issue with what is technically a Join Table. So if I first explain theidea. I have various vehicle lines and various currencies; a table holds datafor each. (These tables are also use for other fields in my queries) I use atable called tblProgExhange to hold the exchange rate, which essentially actsas my many to many join table. Now when I try and us the "rate" inany query which already uses anything from either of the other tables I get anambiguous outer join problem. Therefore I tried making a separate query and gotthe same error. If I play with the join types I can get it to work-ish but itrepeats every record from the main table 45 times as its not fully constrained.


Just topoint out in back end relationships the link between 2 tables and join table isjust join type 1. Also if I make a simple query it does give me the rate, pervehicle, per currency as I would expect. I think it’s a relationship problem.The fixes I am finding online are not helping as I assume I have somethingwrong.


Icompletely understand I have most likely made a fatal relationship error butwas hoping somebody could identify my problem?

Thanks In Advance.



Query I’mTrying to build:



Backend Relationship:


Separate Query I Tried To Use:

[rpeare]

IN the first query you definitely have an circular reference. If you need to reference the same table multiple times in a single query you have to keep adding instances of it to your query. MS access will add the table and give it an alias of _x where x is the iteration count.

For example in your first query you would add two instances of tblProgExchange, the second time you added it to your query MS access would alias it with tblProgExchange_1.

Same thing goes for the second query, I don't know what you're trying to do with the third query but it should run fine depending on what you're doing.

[sdrmason]

The second picture was a picture from the database relationship manager. The Third was a separate query I tried to use to resolve the problem.

I have now tried adding in tblProgExch twice into the first query and in most iterations of join type it doesn't work, when using one of the join types it works but repeats each line in the database 45 times (the same number of records that tblProgExch has).

[sdrmason]

If it helps I have took some snap shots to explain where I am at. (Which is very near the end of my tether with this thing!)

[rpeare]

Maybe you should explain what you are trying to accomplish. If you link two tables that have a one to many relationship assuming each table has a PK through the PK (primary key) - FK (foreign key) you will *always* end up with the number of records on the 'many' side of the relationship unless you apply criteria or use an aggregate query to limit the number of instances you see from the 'many' side.

[sdrmason]

Okay essentially I have lots of lines of data in my tblMain (PK is Unique_Item_ID), these store costs in their local currency. We store different currencies for different programmes (as they are frozen in time for that programme), therefore I have multiple currencies for multiple programmes. programmes stores all of my programmes and its PK is Vehicle_ID, the currency types are stored in tblCurrency and PK Currency_ID, the table tblProgExch I am using as my many to many or Join table. This takes the two PK's and adds a column called "Rate" which stores that programmes rate per currency.

When I have a query showing all my lines in tblMain I want to use the "Rate" from tblProgExch. BUT whenever I add this I either get ambiguous outer join OR 45 iterations of each row, depending on the join type.

(Sorry if this is a poor/waffling problem description)

[rpeare]

I suspect you will have to fix your relationships as well I don't use relationships, they aren't necessary and typically they screw things up for me more than they help. You can try removing all the relationships and see if you start making progress but I think the main problem here is that you are using foreign keys from two different tables to look up a single value from a third table.

This is what I would try:

First, from your existing query remove the table tblProgExchange and any calculations/fields associated with it, save this query with a new name (qryTest)

Save the query

Create a new query and add the query you just saved (qryTest) and the table tblprogexchange

your original query (qryTest) should have both your lookup fields in it, link those to tblprogexchange and see what you get.