Populate a table with update query or recordset?

[Etxezarreta]

Good morning,

Before presenting the problem, here is the context: I select a range of records from one table, "table1, (step1) using a form containing for criteria to filter the records.
Then I would like to transfer those selected records to another, table2 (step2) containing an extra column: a default value ("yes") will be assigned to every record in this last column (step3) This allow me to "mass assign" one value, not going "row to row". If I need to, I will be able to change the value of this last parameter using a form (step4).
Another important information: I will have to migrate the tables to SQL server when I am done with this Acces app.
My problem is:
I have a variable that can be used to "feed" a recordset : str_SQL_finale in the code below.
For stel 2, I guess I could populate the table2 with an update query, but I have problems using it in the Access query builder, since I have a function in str_SQL_finale: Left(t_ListeContacts.[CP],2).
I found interesting ways to do it with recordsets, using a loop or not
(https://stackoverflow.com/questions...rdset-into-existing-access-table-without-loop).

My question is:
What would be a good and strong method to carry it out? Do you have any suggestion please?
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.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"

If (Len(str_CritereDepartement) <> 0) Then
str_SQL1 = str_SQL & " Where t_Departements.CodeDepartement In (" & str_CritereDepartement & ")"
str_SQL_finale = str_SQL1
End If
Debug.Print str_SQL_finale
If (Len(str_CritereProfession) <> 0) Then
str_SQL2 = str_SQL1 & " AND t_IntitulesProfession.Profession IN (" & str_CritereProfession & ")"
str_SQL_finale = str_SQL2
End If
Debug.Print str_SQL_finale

If (Len(str_CritereStatut) <> 0) Then
str_SQL3 = str_SQL2 & " AND t_StatutJuridiqueSoignants.StatutJuridiqueSoignant IN (" & str_CritereStatut & ")"
str_SQL_finale = str_SQL3
End If
Debug.Print str_SQL_finale

 

[isladogs]

In order to populate a second table, you would need an APPEND query not an UPDATE query.
However, there is no reason to copy records to a second table just to add a Boolean (yes/no) field.
Add that field to the first table and set the values all false (no).
Create a query to filter for the records you require and UPDATE those records to yes.
Use an UPDATE query for that. Don't waste time using a recordset

There is an import/export wizard in SQL Server Management Studio when you are ready to upsize your database

 

[Etxezarreta]

In order to populate a second table, you would need an APPEND query not an UPDATE query.
However, there is no reason to copy records to a second table just to add a Boolean (yes/no) field.
Add that field to the first table and set the values all false (no).
Create a query to filter for the records you require and UPDATE those records to yes.
Use an UPDATE query for that. Don't waste time using a recordset

There is an import/export wizard in SQL Server Management Studio when you are ready to upsize your database

Hello isladogs,
thanks a lot for your advice.
However, I can not add a boolean value to the first table (it is a table that identify people that might be assigned to projects, primary key "ID_contact") , as the result of table2 (used as an intermediary table, I will empty it before populating it ) will populate a table3 (people that have been assigned to a project : table with an auto ID+ 2 foreign keys: "ID_contact" and "ID_project": the value will be the name of the extra column of table2).

 

[The_Doc_Man]

You said you could not add a field to the first table. While I can accept that you believe that statement to be true, I need to understand WHY you do not think you can update the structure like that.

Repeatedly having to copy data from point A to point B is USUALLY a sign of a poorly designed structure. The more common structure is to add a "location" or "belongs to" field and instead of moving a whole record, just mark the record with a value that VIRTUALLY says where the record belongs at the moment. AND you are describing a third table containing the same data. You are working too hard. Not only that, but repeated copying of data like that is MORE likely to have issues because you are continually exposing that data to copy errors and glitches. The general rule of thumb is, the less movement, the better.

 

[Etxezarreta]

Hello Doc,

I actually can add a field to my first table but I don't want to: here is why:
- the first table (table 1) contains a list of people, those people can be assigned to one or several projects, so I need another table (table 3, same fields as in table1+one field with the name of the project, as I said one person can take part in many projects)
- to do so, I use a form (Form1): I call records from table1 using three criteria (3 lisboxes)+ I chose the name of a project (1 combo list) that gives his name to the extra field (constant value in the SQL code in VBA)-> selection of as many rows as people selected by the query, land the last field value = combo box value. Before storing these records in table 3, I want the user to be able to remove the assignment if he wants to: my idea was to show the result of this query in a subform, included in Form1, and there the user can remove the name of the project (or unselect a checkbox): only here table 3 would be populated by the rows that still have a value for the project name field .
I hope it is clearer.
Thanks for your help.
Etxe.

 

[The_Doc_Man]

Ah, now I understand, and you are right (though perhaps for the wrong reason). You DON'T want to add another field there.

What you want is a JUNCTION table. You have a table of people. DON'T copy records from table A to table B so that you can link them to a project. Instead, create a table that lists "person with ID 6 is assigned to project with ID 14." You can have dates or other things associated with this assignment, but the idea is, NEVER copy whole records. Just link them through some small mechanism like this JUNCTION table. And it is a topic you can look up using the SEARCH feature of this forum (upper right, on the menu bar). To make this work, all you need is some unique ID that selects one and only one person from the person table. You probably already have such a field, but if you don't, THAT is the one field you need to add to the people table.

This JUNCTION table allows you to assign different people to different projects at different times. It even allows for cases where the same person is forced to split time between two different projects. And if you have both start date and end date for assignments, it becomes the basis for both historical reports and schedule planning.

Appropriate JOIN queries would allow you to show the assignment by linking the person ID in the people table to the person ID in the junction table. You would have name information and, if the dates were in the junction table, the time that the person would be busy on the project. AND if you also have a unique project ID, that could ALSO be the basis of a join that shows the project that the person is on.

This involves NO movement of people records - only ASSIGNMENT records. This is an example of proper normalization, where you have people, projects, and assignments, with a separate table for each.

If you have studied this topic, forgive me, but your question and problem description suggest to me that you are not familiar with normalization. This is one of the most powerful tools you can have in your personal toolbox. If you have not studied this, may I respectfully suggest that you read some articles on normalization? To search this forum, seek "Normalization." To search the general Internet, seek "Database Normalization" because the word normalization applies to other disciplines as well, such as chemistry, math, diplomacy, and biochemistry.

 

[Etxezarreta]

Ah, now I understand, and you are right (though perhaps for the wrong reason). You DON'T want to add another field there.

What you want is a JUNCTION table. You have a table of people. DON'T copy records from table A to table B so that you can link them to a project. Instead, create a table that lists "person with ID 6 is assigned to project with ID 14." You can have dates or other things associated with this assignment, but the idea is, NEVER copy whole records. Just link them through some small mechanism like this JUNCTION table. And it is a topic you can look up using the SEARCH feature of this forum (upper right, on the menu bar). To make this work, all you need is some unique ID that selects one and only one person from the person table. You probably already have such a field, but if you don't, THAT is the one field you need to add to the people table.

This JUNCTION table allows you to assign different people to different projects at different times. It even allows for cases where the same person is forced to split time between two different projects. And if you have both start date and end date for assignments, it becomes the basis for both historical reports and schedule planning.

Appropriate JOIN queries would allow you to show the assignment by linking the person ID in the people table to the person ID in the junction table. You would have name information and, if the dates were in the junction table, the time that the person would be busy on the project. AND if you also have a unique project ID, that could ALSO be the basis of a join that shows the project that the person is on.

This involves NO movement of people records - only ASSIGNMENT records. This is an example of proper normalization, where you have people, projects, and assignments, with a separate table for each.

If you have studied this topic, forgive me, but your question and problem description suggest to me that you are not familiar with normalization. This is one of the most powerful tools you can have in your personal toolbox. If you have not studied this, may I respectfully suggest that you read some articles on normalization? To search this forum, seek "Normalization." To search the general Internet, seek "Database Normalization" because the word normalization applies to other disciplines as well, such as chemistry, math, diplomacy, and biochemistry.

Hello Doc,
This is a very detailed and useful answer, many thanks!
I think my data are correctly normalized, I use very few redundant fields, and almost only foreign keys, and then joins to display explicit information in forms.
I actually solved the problem using an simple update query, and then a no-match one.
I do not know junction joins, I will read about it.
Have a good day.
Etxe.