Solved using joins instead of calculated fields: error in nested INNER JOINS

[Etxezarreta]

Hello,
I have been trying to get rid of all the calculated fields of my tables, in order to run "table creating"queries:
Now I can't link one of my tables to the other ones (the calculated field extracted one part of the zip code that became the link) so no way to fully use the Access query builder. I will use the "left" function to extract the two digits of the zip, and will use an inner join query to obtain the name of the "departement" (french equivalent of a county).
Problem: I need to deal with the two other INNER JOIN I had before, and that worked well, here is the code:
I tried to remove "("and ")", to move them etc.. to change the order, but since I dont really understand the code, I need some help, if possible.
Thanks a lot in advance.

str_SQL = "SELECT t_ListeContacts.[Nom_contact], t_ListeContacts.[Prenon_contact]," & _
"t_ListeContacts.[Nom_structure], t_ListeContacts.[Fonction], t_StatutJuridiqueSoignants.StatutJuridiqueSoignant, " & _
"t_IntitulesProfession.Profession, t_Departements.[Departement], t_ListeContacts.[Ville] FROM t_StatutJuridiqueSoignants " & _
"INNER JOIN (t_IntitulesProfession INNER JOIN (t_ListeContacts INNER JOIN (t_Departements ON t_IntitulesProfession.ID_Profession = t_ListeContacts.FK_Profession) " & _
"ON t_Departements.CodeDepartement = Left(t_ListeContacts.CP,2) ON t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant = t_ListeContacts.FK_Statut_juridique"

Etxe.

 

[plog]

First, you are trying to build your SQL behind a wall you can't see around. When yiu need to construct a query, do it in the proper environment---a query object, not a VBA module. This way you can run the query and see if it is valid and get better error messages when it isn't. Once it works there, copy and paste it into your VBA

Second,

ON t_Departements.CodeDepartement = Left(t_ListeContacts.CP,2)

That's bad for 2 reasons. Discrete data should be stored discretely. That means if the first 2 characters of the CP,2 field represent something, then they should be in there own field not crammed into 1 field with other data. Also, CP,2 is a poor field name, you should not use commas ( or any non-alphanumeric characters) in field names. Bwhen you do it requires brackets around the field name in code. [CP,2]

 

[plog]

Third, I'm counting more left parentheses than right parentheses. You really should build this thing in a query object.

 

[Etxezarreta]

Hello plog,
You are right, I used Access to create a query, it it is pretty easy. I have sorted it out.
Thanks a lot.

"SELECT t_ListeContacts.Nom_contact, t_ListeContacts.Prenon_contact, t_ListeContacts.Nom_structure, t_ListeContacts.Fonction, t_StatutJuridiqueSoignants.StatutJuridiqueSoignant, t_IntitulesProfession.Profession, t_Departements.CodeDepartement, t_ListeContacts.Ville FROM (t_StatutJuridiqueSoignants INNER JOIN (t_IntitulesProfession INNER JOIN t_ListeContacts ON t_IntitulesProfession.ID_Profession = t_ListeContacts.FK_Profession) ON t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant = t_ListeContacts.FK_Statut_juridique) INNER JOIN t_Departements ON Left(t_ListeContacts.[CP],2)= t_Departements.CodeDepartement"

 

[isladogs]

That's bad for 2 reasons. Discrete data should be stored discretely. That means if the first 2 characters of the CP,2 field represent something, then they should be in there own field not crammed into 1 field with other data. Also, CP,2 is a poor field name, you should not use commas ( or any non-alphanumeric characters) in field names. Bwhen you do it requires brackets around the field name in code. [CP,2]

The field name in the query is CP...not CP,2

 

[Etxezarreta]

It is CP yes, not CP,2