Query Error => Join expression not supported

[Hossein]

My dear friends
I am creating a report in access. source of this report is a query but i have an error during creating my query. When i add Bank table to my query, i faced by Join expression not supported error but I couldn't find reason of error.


Would you help me please!

[June7]

Joins should be on primary/foreign key fields. None of the links include a primary key field.

Can a supplier have only one bank?

[isladogs]

You are using outer joins but the direction isn't consistent.
If you need that arrangement try removing the Bank table and save your query.
Then create a new query based on the saved query and add the Bank table to that.

[Hossein]

Joins should be on primary/foreign key fields. None of the links include a primary key field.

Can a supplier have only one bank?

I set PK on Supplier code in table Supplier , but error dose not fix.
Also in supplier table, suppliers code is unique. this means that, once supplier in supplier table has several bank account.

[Hossein]

You are using outer joins but the direction isn't consistent.
If you need that arrangement try removing the Bank table and save your query.
Then create a new query based on the saved query and add the Bank table to that.

I used your method and it's Working.
Big thanks.

[isladogs]

Not quite correct.
You have what is called a Cartesian join AKA no join query.
This will give one record for every combination of records from each table.
So if PO Supplier has 200 records and Bank has 40, your query will have 200x40=8000 records

Reinstate the join on the Supplier_Code field

[Hossein]

Not quite correct.
You have what is called a Cartesian join AKA no join query.
This will give one record for every combination of records from each table.
So if PO Supplier has 200 records and Bank has 40, your query will have 200x40=8000 records

Reinstate the join on the Supplier_Code field

Dear ridders52
I make relationship as you say and my query give one record.






Note: I mentioned one supplier can several bank account in bank table
Many thanks for your help

[isladogs]

Not sure if you're saying that is the desired outcome.
If not, bear in mind that ....

Previously you had one outer join to the Bank table.
Now you have two inner joins.
Try joining on account number only (if those really are unique) and try an outer join

[Hossein]

Not sure if you're saying that is the desired outcome.
If not, bear in mind that ....

Previously you had one outer join to the Bank table.
Now you have two inner joins.
Try joining on account number only (if those really are unique) and try an outer join

I removed supplier_code join and by one relationship (Account number join) it is working.




Is this join outer join ? if I am wrong to make outer join, could you do that for me ? (I can upload Access file for you)

[isladogs]

Hi Hossein
No its an inner join which means the query output only contains records that are in both tables.
That may well be what you want - only you can tell by looking at the results.
However its easy enough for you to test.

I've created a quick guide to the different join types as a zipped word doc (attached)

Hope that helps

[Hossein]

Hi Hossein
No its an inner join which means the query output only contains records that are in both tables.
That may well be what you want - only you can tell by looking at the results.
However its easy enough for you to test.

I've created a quick guide to the different join types as a zipped word doc (attached)

Hope that helps

Thank you very much for taking the time to help me.