Access is refusing to run an query with linked table?

Question

Hey all

i have 3 tables each as follow

cash_credit

Bank_Name-------in_date-------Com_Id---Amount
America Bank    15/05/2010      1       200
HSBC            17/05/2010      3       500

Cheque_credit
Bank_Name-----Cheque_Number-----in_date-------Com_Id---Amount
America Bank   74835435-5435    15/05/2010      2       600
HSBC           41415454-2851    17/05/2010      5       100

Companies
com_id----Com_Name
1         Ebay
2         Google
3         Facebook
4         Amazon

Companies table is a linked table when i tried to create an query as follow

SELECT cash_credit.Amount, Companies.Com_Name, cheque_credit.Amount
FROM cheque_credit INNER JOIN (cash_credit INNER JOIN Companies ON cash_credit.com_id = Companies.com_id) ON cheque_credit.com_id = Companies.com_id;

I get an error saying that my inner Join is incorrectly, this query was created using Access 2007 query design the error is

Type mismatch in expression

then i thought it might be the inner join so i tried Left Join and i get an error that this method is not used

JOIN expression is not supported

I am confused on where is the problem that is causing all this

Answer 1

Is the data type of column com_Id same/consistent across all the 3 tables?
If not, correct the data type and make it consistent for that column.

That should fix the issue for you.

Answer 2

Regarding your first error:

Type mismatch in expression

This means that the types involved in some expression are not compatible with each other. Probably you are joining on two columns that have different types. Check the types of the following columns to ensure that they all have the same type:

Companies.com_id
cash_credit.com_id
cheque_credit.com_id

Regarding the different type of joins, as you seem to be uncertain when they should be used:

The difference between a LEFT JOIN and an INNER JOIN is if you have a row in the left table that doesn't match any row in the right table:

• LEFT JOIN: returns the row with NULLs for the values in the right table
• INNER JOIN: does not return the row

Answer 3

I think you may want:

SELECT cash_credit.Amount, Companies.Com_Name, cheque_credit.Amount
FROM ( cheque_credit
INNER JOIN Companies 
ON cheque_credit.com_id = Companies.com_id)
INNER JOIN  cash_credit
ON cash_credit.com_id = Companies.com_id;