Unmatched Query Wizard

Question

I have created 2 queries ([TV collateral] and [TV applications]) and want to find rows in the first query that don't exist in the second query.  I have always used the Unmatched Query Wizard to accomplish this.  I have never had trouble using this wizard in the past.  However, today I keep getting the "multi-part identifier [field name] could not be bound" error message.

My database uses the Access 2000 file format and I'm running Access 2013.  I upgraded to 2013 recently and am wondering if that has anything to do with this.

This is the SQL view of [TV collateral]:

SELECT dbo_L_WORK_ID.work_id, dbo_L_WORK_ID.loan_number, dbo_L_WORK_ID.current_queue_id, dbo_L_WORK_ID.current_status_id, dbo_L_LOAN.application_type, dbo_L_COLL_MAIN.collateral_type

FROM (dbo_L_WORK_ID INNER JOIN dbo_L_LOAN ON dbo_L_WORK_ID.work_id = dbo_L_LOAN.work_id) INNER JOIN dbo_L_COLL_MAIN ON dbo_L_LOAN.work_id = dbo_L_COLL_MAIN.work_id

WHERE (((dbo_L_WORK_ID.current_queue_id)="AR") AND ((dbo_L_WORK_ID.current_status_id)="CP") AND ((dbo_L_LOAN.application_type)="NEW" Or (dbo_L_LOAN.application_type)="REF") AND ((dbo_L_COLL_MAIN.collateral_type)="TV"))

ORDER BY dbo_L_WORK_ID.loan_number DESC;

Next I created [TV applications] using [TV collateral] along with another table in the query definition:

SELECT [TV collateral].work_id, [TV collateral].loan_number, dbo_L_FORM.form_nbr

FROM [TV collateral] INNER JOIN dbo_L_FORM ON [TV collateral].work_id = dbo_L_FORM.work_id

WHERE (((dbo_L_FORM.form_nbr)="152" Or (dbo_L_FORM.form_nbr)="253" Or (dbo_L_FORM.form_nbr)="355" Or (dbo_L_FORM.form_nbr)="452"))

ORDER BY [TV collateral].loan_number DESC;

Then I wanted to use these 2 queries in the Unmatched Query Wizard.  But I get an ODBC--call failed error with each field listed as "could not be bound".  For example, [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "MS1.collateral_type" could not be bound.  (#41040)  Each field in my query is listed in this same manner. 

Any help would be greatly appreciated.

Answer 1

But there are some records in query 1 that are not in query 2.  And those are the ones I'm trying to find.  Individually, the queries run just fine.  I'm trying to determine what is in query 1 that doesn't exist in query 2.  The work_id field is the common field in both queries.

Regarding the suggestion to try without any criteria, that's why I built 2 separate queries.  The individual queries have criteria.  My specific need is to find all vehicle loans that don't have a title application.  My first query finds all vehicle loans.  My second query uses the result of query 1 (all vehicle loans) and finds related records that have a title application.  There are some in query 1 that are not in query 2.  So, I want my unmatched query to then find all vehicle loans in query 1 that don't have a corresponding title application in query 2.

I continue to appreciate the help.

Answer 2

I just relooked at the queries and you will not get any record that do not match because the  work_id  in query one is pulled for that in query 2.

Answer 3

Don't know!  Try an INNER JOIN without any criteria as a test.

Answer 4

Yes, I get results for both queries when I run them alone. 

SELECT [TV collateral].loan_number

FROM [TV collateral] LEFT JOIN [TV applications] ON [TV collateral].[work_id] = [TV applications].[work_id]

WHERE ((([TV applications].work_id) Is Null));

Answer 5

Do you get any results when you run queries [TV collateral] and [TV applications] alone?

Post the SQL of your 'unmatched query.'