Solved Combine INSERT INTO and no match on one field and one criteria (1 Viewer)

[Etxezarreta]

Hello,
I need to populate the table "PROJETS_t_AffectationProjetA_Contacts_temp" from the table "Contacts_t_ListeContacts" , in two steps:
- firstly, I use the values of listboxes from the form "PROJETS_f_AffectationContacts _Projets" to select only the rows that respect those criteria
- then I need to select rows on other two "unmatch" criteria: the field "ID_contacts" from the table "Contacts_t_ListeContacts" must not be present in the field "FK_ID_contact" from the table "PROJETS_f_AffectationContacts _Projet" and the field "FK_ID_projet" value in the table "PROJETS_f_AffectationContacts _Projet" must be different from the value of the combobox "combo_ListeProjetsAaffecterAuxContacts" in the form "PROJETS_f_AffectationContacts _Projets".
I tried to combine the INSERT INTO, the unmatched query in one signle string, but I haven't managed so far.
You will find the code in the module form_PROJETS_f_AffectationContacts_Projets.
You will find below the problematic part.

str_NomDuProjet = Me.combo_ListeProjetsAaffecterAuxContacts.Column(1)


str_SQL_finale = "" 'DBG str_SQl

str_SQL_finale = "INSERT INTO PROJETS_t_AffectationProjetA_Contacts_temp(ID_contact)" & _
" SELECT Contacts_t_ListeContacts.ID_contact" & _
" FROM Contacts_t_ListeContacts LEFT JOIN PROJETS_t_AffectationProjetA_Contacts" & _
" ON Contacts_t_ListeContacts.ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact" & _
" WHERE Contacts_t_ListeContacts.Departement In (" & str_CritereDepartement & ")" & _
" AND Contacts_t_ListeContacts.Ville In (" & str_CritereVille & ")" & _
" AND Contacts_t_IntitulesProfession.ID_Profession IN (" & str_CritereProfession & ") " & _
" AND Contacts_t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant IN (" & str_CritereStatutJuridique & ")" & _
" AND Contacts_t_Specialite.ID_Specialite IN (" & str_CritereSpecialite & ")" & _
" AND PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null" & _
" AND PROJETS_t_AffectationProjetA_Contacts <> " & str_Projet & ""

'Here: I would like to add one thing to this string: keep only the Contacts_t_ListeContacts.ID_contact values that are both not contained in the table PROJETS_t_AffectationProjetA_Contacts and for which the
'FK_ID_projet value is different from the variable int_Projet

Debug.Print str_SQL_finale
'On vide la table intermediaire
str_Sql = "DELETE FROM PROJETS_t_AffectationProjetA_Contacts_temp"
objConnection.Execute (str_Sql)



Many thanks in advance!
Etxe.

 

[Galaxiom]

Your question is a bit hard to read but I suspect you are looking for what is sometimes called an UPSERT query.

An UPDATE query with an OUTER JOIN from the source table to the target table will update records that already exist in the target table and insert those that don't (based on the join fields).

HTH

 

[Etxezarreta]

I will split the sql string in two queries, and will repost, it will be easier to understand

 

[Etxezarreta]

Hello,
I split the query in two, you will find it in the sub "Sub LancerRequetePourTrouverContactsAquiAffecterProjet".
This part works just fine:

str_SQL_finale = ""
str_SQL_finale = "INSERT INTO PROJETS_t_AffectationProjetA_Contacts_temp(FK_ID_contact)" & _
"SELECT Contacts_t_ListeContacts.ID_Contact" & _
" FROM Contacts_t_ListeContacts" & _
" WHERE " & strAllCriteria & ""

Debug.Print str_SQL_finale
'On vide la table intermediaire
str_SQL = "DELETE FROM PROJETS_t_AffectationProjetA_Contacts_temp"
objConnection.Execute (str_SQL)

But the following one shows me an error message: "one parameter has no value"

'Sailkatua izan den xedean direnak kentzen dituk tablatik:
str_SQL_finale = "DELETE *" & _
" FROM PROJETS_t_AffectationProjetA_Contacts_temp WHERE PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact IN" & _
" (SELECT PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact" & _
" FROM PROJETS_t_AffectationProjetA_Contacts_temp INNER JOIN PROJETS_t_AffectationProjetA_Contacts ON PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)" & _
" AND NZ(PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet,'') = " & str_Projet & ""


It works without the last row though: so I tried to modify the type of data, with and without the parenthesis after and before the AND.
I ran out of solutions. You will find the file enclosed.
Many thanks in advance.
Etxe.

 

[Etxezarreta]

Hello again,

I changed the way I handle the query: I use a simple unmatched query on the following fields: FK_ID_contact and FK_ID_projet
I created a query-object, called "PROJETS_r_Selection" (the only query of the file) and it works..
But when I put this code into VBA, it still doesn't take into consideration the value of FK_ID_projet:
it might be a problem of type of value, as I populate the field PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet with the value of a combobox using the word AS:
" SELECT Contacts_t_ListeContacts.ID_Contact, " & int_Projet & " AS FK_ID_projet" & _
I put as an integer: I tried to play with the type of data (string variant) and the comas, but I haven't managed so far.

You will find the code in the sub called "LancerRequetePourTrouverContactsAquiAffecterProjet", and a transcription of it below:


Part 1: works fine:

str_SQL_finale = ""
str_SQL_finale = "INSERT INTO PROJETS_t_AffectationProjetA_Contacts_temp(FK_ID_contact,FK_ID_projet)" & _
" SELECT Contacts_t_ListeContacts.ID_Contact, " & int_Projet & " AS FK_ID_projet" & _
" FROM Contacts_t_ListeContacts" & _
" WHERE " & strAllCriteria & ""


Debug.Print str_SQL_finale
'On vide la table intermediaire
str_Sql = "DELETE FROM PROJETS_t_AffectationProjetA_Contacts_temp"
objConnection.Execute (str_Sql)
'Puis on la complete avec la requete:
objConnection.Execute (str_SQL_finale)
Me.Refresh
'------------------------------------------------------------------------------------------------------------------------------------------------------------------
'in the code below


str_SQL_finale2 = "SELECT PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact,PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet" & _
" FROM PROJETS_t_AffectationProjetA_Contacts_temp LEFT JOIN PROJETS_t_AffectationProjetA_Contacts ON" & _
" (PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet = PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet)" & _
" AND (PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)" & _
" WHERE PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null"

Debug.Print str_SQL_finale2
'Puis on la complete avec la requete:
objConnection.Execute (str_SQL_finale2)
Me.Refresh

 

[Etxezarreta]

Hello,
I found the error: it is not possible to use "execute" with a SELECT query: here is the code that works:

    str_SQL_finale = ""
    str_SQL_finale = "INSERT INTO PROJETS_t_AffectationProjetA_Contacts_temp(FK_ID_contact,FK_ID_projet)" & _
                    " SELECT Contacts_t_ListeContacts.ID_Contact, " & int_Projet & " AS FK_ID_projet" & _
                    " FROM Contacts_t_ListeContacts" & _
                    " WHERE " & strAllCriteria & ""


Debug.Print str_SQL_finale
'On vide la table intermediaire
    str_Sql = "DELETE FROM PROJETS_t_AffectationProjetA_Contacts_temp"
    objConnection.Execute (str_Sql)
'Puis on la complete avec la requete:
    objConnection.Execute (str_SQL_finale)
Me.Refresh
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Sailkatua izan den xedean direnak kentzen dituk tablatik:

str_SQL_finale2 = "SELECT PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact,PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet" & _
                " FROM PROJETS_t_AffectationProjetA_Contacts_temp LEFT JOIN PROJETS_t_AffectationProjetA_Contacts ON" & _
                " (PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet = PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet)" & _
                " AND (PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)" & _
                " WHERE PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null"


With oBjRcrdst_RecordSetSource
  .Source = str_SQL_finale2
  .ActiveConnection = objConnection
  'doesnt work with 'CurrentProject.connection
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
    .Open
End With

Set Me.PROJETS_sf_f_AffectationContacts_Projets.Form.Recordset = oBjRcrdst_RecordSetSource

  Me.Refresh

 

[Galaxiom]

Since you are simply passing a string to the SQL you don't actually need anything more than the line continuation.

str_SQL_finale2 = "SELECT  _
               PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact,  _
               PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet  _
               FROM PROJETS_t_AffectationProjetA_Contacts_temp  _
               LEFT JOIN PROJETS_t_AffectationProjetA_Contacts  _
               ON ( PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet = PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet) _
               AND ( PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)"  _
               WHERE PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null"

Concatenation is usually for passing values into the string and that can be complex than just converting your original SQL as have done below just to demonstrate the principles of layout when concatenating SQL. It separates and organises the critical elements where they are most easily verified.

The key to maintaining readability with any SQL is layout though there are more characters in this structure than really needed.

str_SQL_finale2 = "SELECT " _
                & " PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact" _
                & " , PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet" _
                & " FROM PROJETS_t_AffectationProjetA_Contacts_temp" _
                & " LEFT JOIN PROJETS_t_AffectationProjetA_Contacts" _
                & " ON ( PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet = PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet)" _
                & " AND ( PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact = PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)"  _
                & " WHERE PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact Is Null"

The concatenators form a border-like column that is much less distracting than having them scattered at the end of the lines. The comma is at the beginning of the line where it is extremely easy to see. This leaves just the close double quote and line continuation mark very clear and identical on every line. Any departure from the regularity at both ends of each line is very easy to see

I put one select field value per line when writing raw SQL but I don't concatenate query strings in VBA as much where there is more overhead in extra lines. Obsession with layout regularity can lead to obfuscation sometimes too. Note that, continuation only works to 22 lines.

 

[Etxezarreta]

Well, thank you very much for your advice, I do appreciate.
Have a good day.
Etxe.