1

Similar to this question I have this error I'm not able to fix.

Here is the situation :

We have an application front-end under Access 2003 with linked tables from SQL Server 2008 R2.

We have a table Vehicule(immat, secteur) and a table Vehicule_suivi_km(immat, date, km). Our vehicles are stored in the [Vehicule] table with [immat] as primary key and their kilometer records stored in the [Vehicule_suivi_km] table with [immat]+[date] as primary key.

Now I have to create a summary of the activity of the year. I made it as following :

Query creation [MO km max 2016] :

SELECT Vehicule.immat, vsk2016.date, vsk2016.km
FROM Vehicule INNER JOIN Vehicule_suivi_km AS vsk2016 ON Vehicule.immat=vsk2016.immat
WHERE (((vsk2016.date)=(SELECT Max(date) FROM Vehicule_suivi_km WHERE (Vehicule_suivi_km.date<#01/01/17# And Vehicule_suivi_km.immat=vsk2016.immat))))
ORDER BY Vehicule.immat;

This code is giving me a line by vehicle with its most recent kilometer record before the #01/01/2017#.

I do it again for 2017 [MO km max 2017] : 

SELECT Vehicule.immat, vsk2017.date, vsk2017.km
FROM Vehicule INNER JOIN Vehicule_suivi_km AS vsk2017 ON Vehicule.immat=vsk2017.immat
WHERE (((vsk2017.date)=(SELECT Max(date) FROM Vehicule_suivi_km WHERE (Vehicule_suivi_km.date<#01/01/18# And Vehicule_suivi_km.immat=vsk2017.immat))))
ORDER BY Vehicule.immat;

Now I want to create a query which join theses 2 tables and substract the kilometer of 2016 from the kilometer of 2017 [MO km this year] :

SELECT [MO km max 2017].immat, [MO km max 2017].km-Nz([MO km max 2016].km,0) AS km
FROM [MO km max 2017] LEFT JOIN [MO km max 2016] ON [MO km max 2017].immat = [MO km max 2016].immat;

I'm using a left join in case of we bought a vehicle after the 01/01/2017. So I'm using the Nz to transform the null values in 0 (in SQL Server it might by the COALESCE function but I'm using Access for querying)

I get the following error by executing this query (The multi-part identifier could not be bound [#4104]) :

access_sql-server_4104

If I use INNER JOIN instead of LEFT JOIN I don't get this error anymore but I would only have the records for vehicule bought before the 01/01/2017.

Is it a primary key problem ? I can't get what I'm doing wrong.

EDIT 1

The error message is saying

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server] The multi-part identifier "vsk2016.immat" could not be bound. 37000 4104 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server] The multi-part identifier "vsk2016.date" could not be bound. (#4104)

EDIT 2

By adding a GROUP BY close in [MO km max 2016] and [MO km max 2017] it solved my problem :

SELECT Vehicule.immat, vsk2017.[date], vsk2017.km
FROM Vehicule INNER JOIN Vehicule_suivi_km AS vsk2017 ON Vehicule.immat=vsk2017.immat
WHERE (((vsk2017.[date])=(SELECT Max([date]) FROM Vehicule_suivi_km WHERE (Vehicule_suivi_km.[date]<#01/01/18# And Vehicule_suivi_km.immat=vsk2017.immat))))
GROUP BY Vehicule.immat, vsk2017.[date], vsk2017.km
ORDER BY Vehicule.immat;

But I still wondering why I got this error message. Does the engine wasn't sure that "immat" was unique in the result of my query so I had to add the GROUP BY close ?

M. Ozn
  • 1,018
  • 1
  • 19
  • 42
  • The error shown in the error snapshot doesn't look like in English. Will you be able to translate the entire error message into English? And are you sure that with INNER JOIN you are changing only the JOIN and nothing else? – Rajesh Bhat Dec 27 '17 at 12:06
  • Sure, the error message is saying "[Microsoft][ODBC Driver 11 for SQL Server][SQL Server] The multi-part identifier "vsk2016.immat" could not be bound. 37000 4104 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server] The multi-part identifier "vsk2016.date" could not be bound. (#4104)" It seems like the two columns [immat] and [date] (which are the primary key of vsk2016, the alias of [Vehicule_suivi_km]) are in error. And yes I just replaced LEFT by INNER for the JOIN. – M. Ozn Dec 27 '17 at 12:39
  • Interesting!! If it works for INNER JOIN then it should work for other JOINS too. date and immat fields are present in table Vehicule_suivi_km? As date is a reserved keyword, use it cautiously and surround with [ ]. – Rajesh Bhat Dec 27 '17 at 12:51
  • Yes the fields are present the table. I surrounded the vsk2016.[date] but it changed nothing. BUT ! I added a GROUP BY close in the both tables [MO km max 2016] and [MO km max 2017] and now it's working great. I added details on the main question but I still wondering why I had this error message ! – M. Ozn Dec 27 '17 at 12:59

1 Answers1

1

I had the same issue, ie linking queries with left joins, but I found that if I set each of the queries I was linking to Unique Values it was OK.

jpp
  • 159,742
  • 34
  • 281
  • 339
Michael
  • 11
  • 1