Linked table "not found" on a left join

  • Thread starter Thread starter Shing
  • Start date Start date
S

Shing

I am trying to join two linked tables ("Job_Transactions" and
"Purchase_Ledger" from a database called Sage 100, it's an English accounting
package) by creating a left join. When I run the query, I receive the error
"Could not execute query; could not find linked table. Table not found (#0)."
If I change the join to an inner join, the query runs.

The SQL is below:

SELECT JOB_TRANSACTIONS.JOB_NUMBER, JOB_COSTING_FILE.JOB_NAME,
JOB_TRANSACTIONS.REFERENCE, JOB_TRANSACTIONS.SECOND_REFERENCE,
JOB_COST_HEADINGS.COST_HEADING, JOB_TRANSACTIONS.TRANSACTION_DATE,
JOB_TRANSACTIONS.TOTAL_VALUE_AT_COST, PURCHASE_LEDGER.ANALYSIS_1
FROM ((JOB_TRANSACTIONS LEFT JOIN PURCHASE_LEDGER ON JOB_TRANSACTIONS.
ACCT_EMP_ITEM_NO = PURCHASE_LEDGER.ACCOUNT_NUMBER) INNER JOIN
JOB_COSTING_FILE ON JOB_TRANSACTIONS.JOB_NUMBER = JOB_COSTING_FILE.JOB_NUMBER)
INNER JOIN JOB_COST_HEADINGS ON JOB_TRANSACTIONS.TRAN_COST_HEADING =
JOB_COST_HEADINGS.COST_CODE;
 
I've tried that and it doesn't work. I've used linked tables before (from
other databases) and have never had this problem before.

John said:
The only suggestion I can find is to delete and re-create the linked
tables.
I am trying to join two linked tables ("Job_Transactions" and
"Purchase_Ledger" from a database called Sage 100, it's an English accounting
Click to expand...
[quoted text clipped - 13 lines]
INNER JOIN JOB_COST_HEADINGS ON JOB_TRANSACTIONS.TRAN_COST_HEADING =
JOB_COST_HEADINGS.COST_CODE;
Click to expand...
Click to expand...
 
Does it make any difference if you put the left join in a separate
query? If not, try asking in microsoft.public.access.queries.


I've tried that and it doesn't work. I've used linked tables before (from
other databases) and have never had this problem before.

John said:
The only suggestion I can find is to delete and re-create the linked
tables.
I am trying to join two linked tables ("Job_Transactions" and
"Purchase_Ledger" from a database called Sage 100, it's an English accounting
Click to expand...
[quoted text clipped - 13 lines]
INNER JOIN JOB_COST_HEADINGS ON JOB_TRANSACTIONS.TRAN_COST_HEADING =
JOB_COST_HEADINGS.COST_CODE;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top