Doubt in SQL, need a help here
Hello Fellows
I have this SQL, which is fetching already inserted values at an old date, for records with a new date. SQl is working fine, but I have some difficulty doing the following:
If I mistakenly re-run the query, the records become duplicates, I want to avoid this with something like "WHERE NOT EXISTS", but I'm not sure how to do that part. Can you give me a hand?
Greetings to all.
SQL:
Thanks in advanced
I have this SQL, which is fetching already inserted values at an old date, for records with a new date. SQl is working fine, but I have some difficulty doing the following:
If I mistakenly re-run the query, the records become duplicates, I want to avoid this with something like "WHERE NOT EXISTS", but I'm not sure how to do that part. Can you give me a hand?
Greetings to all.
SQL:
Private Sub Command39_Click()
' remind that [DATA] must be like this -> #" & Format(DataFormatada, "mm/dd/yyyy") & "#
Dim DataFormatadaOld As Date
Dim DataFormatadaNew As Date
DataFormatadaOld = Format(Forms!frmFScomposicao!ComboFSDATA, "short date")
DataFormatadaNew = Format(Forms!frmFScomposicao!TransferData, "short date")
Dim db As Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO tblFSListaTecnicos (AtribDataTecnico, AtribEquipaTecnico, TecnicoLista, FuncaoTecnicoLista) " _
& "SELECT #" & Format(DataFormatadaOld, "mm/dd/yyyy") & "#, '" & Forms!frmFScomposicao!ComboFSEQUIPA & "', " _
& "TecnicoLista, FuncaoTecnicoLista " _
& "FROM tblFSListaTecnicos " _
& "WHERE ((tblFSListaTecnicos.AtribDataTecnico = #" & Format(DataFormatadaNew, "mm/dd/yyyy") & "#) And (tblFSListaTecnicos.AtribEquipaTecnico = '" & Forms!frmFScomposicao!TransferEquipa & "'))"
db.Execute strSQL, dbFailOnError
Me.Refresh
MsgBox db.RecordsAffected & " Tecnicos Adicionados"
End Sub
Thanks in advanced
Problem resolver
Suppose AtribDataTecnico is the relevant Key column in the table, then you can put
into your query.
... where #" & Format(DataFormatadaOld, "mm/dd/yyyy") & "# not in (select AtribDataTecnico from tblFSListaTecnicos)"
into your query.
Self-taught
Imagine this
in day AAAA
I have this
Julian day AAAA
Mary day AAAA
i run query and in day BBBB
I want those people in day AAAA
So the result is:
Julian day BBBB
Mary day BBBB
But if i re-run query mistakenly in day BBBB
I get the result
Julian day BBBB
Mary day BBBB
Julian day BBBB
Mary day BBBB
in day AAAA
I have this
Julian day AAAA
Mary day AAAA
i run query and in day BBBB
I want those people in day AAAA
So the result is:
Julian day BBBB
Mary day BBBB
But if i re-run query mistakenly in day BBBB
I get the result
Julian day BBBB
Mary day BBBB
Julian day BBBB
Mary day BBBB
need more info;
<I want those people in day AAAA> how do you determine day AAAA ? is this the max date for these people?
<I want those people in day AAAA> how do you determine day AAAA ? is this the max date for these people?
Self-taught
No! That info i get from a combo in form.
There are 4 combos criteria....
DataFormatadaOld = Format(Forms!frmFScomposic
DataFormatadaNew = Format(Forms!frmFScomposic
There are 4 combos criteria....
DataFormatadaOld = Format(Forms!frmFScomposic
DataFormatadaNew = Format(Forms!frmFScomposic
Self-taught
sorry is the other way around DataFormatadaOld is the new date and DataFormatadaNew date from where i want to pick persons
Self-taught
But since I've been doing this for a couple of hours, I've chosen to do things differently. I will delete the query and I will do things with a listbox in which I will loop and compare with the inserted records, if they do not exist, he adds. I'm finishing programming now and it seems to be working fine. Anyway, I would like to know if you can do this with the query.
Self-taught
But I can better describe what I mean. Let's say that on X-Day I have Mary, Alex and Jon on team A. I'm going for day Y and I want those same people, but linked to a different date "Y" and a team that may be different or not.
<elaborate this more - if they do not exist> what are your criterias to find if the records does not exists?
which field contains the name of people?
which field contains the name of people?
Self-taught
those records are all in one table, the criteria for "if they do not exists" is a combined factor. If Julia combined with Date X And Team A dont exists, then insert, else, dont insert because all three combinations already exists.
what is the name of the field that contains Julia?
what is the name of the field that contains Team A?
what is the name of the field that contains Date X?
what is the name of the field that contains Team A?
what is the name of the field that contains Date X?
here is a sample query using NOT Exists based on three fields
it will be best if you can upload a copy of the db
SELECT tblFSListaTecnicos.TecnicoLista, tblFSListaTecnicos.AtribEquipaTecnico, tblFSListaTecnicos.AtribDataTecnico
FROM tblFSListaTecnicos
WHERE NOT Exists(Select T.TecnicoLista FROM tblFSListaTecnicos As T Where T.TecnicoLista = tblFSListaTecnicos.TecnicoLista And T.AtribEquipaTecnico =tblFSListaTecnicos.AtribEquipaTecnico And T.AtribDataTecnico = tblFSListaTecnicos.AtribDataTecnico)
it will be best if you can upload a copy of the db
Self-taught
Hi again
Rey,
I used your technique, but now the query did not fetch any records.![2017-03-07.png]()
The two combos from above are the Date and Team which must appear in the copied records. The two bottom combos are the criteria where the query should fetch people and teams from old date and team already inserted that need to be copied.
Heres the code:
Rey,
I used your technique, but now the query did not fetch any records.
The two combos from above are the Date and Team which must appear in the copied records. The two bottom combos are the criteria where the query should fetch people and teams from old date and team already inserted that need to be copied.
Heres the code:
Dim DataFormatadaOld As Date
Dim DataFormatadaNew As Date
DataFormatadaOld = Format(Forms!frmFScomposicao!ComboFSDATA, "short date")
DataFormatadaNew = Format(Forms!frmFScomposicao!TransferData, "short date")
Dim db As Database
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO tblFSListaTecnicos (AtribDataTecnico, AtribEquipaTecnico, TecnicoLista, FuncaoTecnicoLista) " _
& "SELECT #" & Format(DataFormatadaOld, "mm/dd/yyyy") & "#, '" & Forms!frmFScomposicao!ComboFSEQUIPA & "', tblFSListaTecnicos.TecnicoLista, tblFSListaTecnicos.FuncaoTecnicoLista" _
& " FROM tblFSListaTecnicos " _
& " WHERE not Exists(Select T.* FROM tblFSListaTecnicos As T Where T.TecnicoLista = tblFSListaTecnicos.TecnicoLista " _
& "And T.AtribEquipaTecnico = '" & Forms!frmFScomposicao!TransferEquipa & "' " _
& "And T.AtribDataTecnico = #" & Format(DataFormatadaNew, "mm/dd/yyyy") & "#)"
db.Execute strSQL, dbFailOnError
Me.Refresh
MsgBox db.RecordsAffected & " Tecnicos Adicionados"
Open form frmFScomposicao, then choose the two upper combo for Date 3-10-2017 and EQ3. You should see some records... next navigate to ... 4-10-2017. No records there. Then on the lower combos choose the first date 3-10-2017 and EQ3 that's the records i want for 4-10-2017.... any questions, im around.
Database1.accdb
Database1.accdb
Self-taught
I hope I have not forgotten to delete some references that generate a mistake, but I think everything is working in the basics.
way back in my post, I asked about the criteria you need to know what record that don't exists..
the solution - queries I created was based on the criteria you provided.
the solution - queries I created was based on the criteria you provided.
Self-taught
I'm not saying that your solution is not right. This is a case of misunderstanding ... no one is to blame. The functionality I'm looking for is simply to copy the records (eg names of people and skills) entered on 03/10/2017 to a later date, for example 10/10/2017. Anyway, I've been able to solve the problem, thanks also to your help. Thank you. Greetings.