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:
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

Open in new window


Thanks in advanced
Armando Vilela Baiões Self-taughtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Suppose AtribDataTecnico is the relevant Key column in the table, then you can put

... where #" & Format(DataFormatadaOld, "mm/dd/yyyy") & "# not in (select AtribDataTecnico from tblFSListaTecnicos)"

Open in new window


into your query.
0
Armando Vilela Baiões Self-taughtAuthor Commented:
Thats not it.
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Rey Obrero (Capricorn1)Commented:
need more info;

<I want those people in day AAAA> how do you determine day AAAA ? is this the max date for these people?
0
Armando Vilela Baiões Self-taughtAuthor Commented:
No! That info i get from a combo in form.

There are 4 combos criteria....

DataFormatadaOld = Format(Forms!frmFScomposicao!ComboFSDATA, "short date") gets old date where i want to pick persons
DataFormatadaNew = Format(Forms!frmFScomposicao!TransferData, "short date")gets criteria for new date to person picked
0
Armando Vilela Baiões Self-taughtAuthor Commented:
sorry is the other way around DataFormatadaOld is the new date and DataFormatadaNew date from where i want to pick persons
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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.
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
<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?
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
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?
0
Armando Vilela Baiões Self-taughtAuthor Commented:
TecnicoLista [Julia]
AtribEquipaTecnico [Team A]
AtribDataTecnico [Date X]
0
Rey Obrero (Capricorn1)Commented:
here is a sample query using NOT Exists based on three  fields

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)

Open in new window


it will be best if you can upload a copy of the db
0
Armando Vilela Baiões Self-taughtAuthor Commented:
I still have not given up on the query, so I'll try your suggestion Rey.
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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:
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 in new window

0
Rey Obrero (Capricorn1)Commented:
it will be best if you can upload a copy of the db. just fake sensitive info
0
Armando Vilela Baiões Self-taughtAuthor Commented:
ok... ill do that
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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
0
Armando Vilela Baiões Self-taughtAuthor Commented:
I hope I have not forgotten to delete some references that generate a mistake, but I think everything is working in the basics.
0
Rey Obrero (Capricorn1)Commented:
copy this query and save as Q_Append
INSERT INTO tblFSListaTecnicos ( AtribDataTecnico, AtribEquipaTecnico, TecnicoLista, FuncaoTecnicoLista )
SELECT [Forms]![frmFScomposicao]![ComboFSDATA] AS AtribDataTecnico, [Forms]![frmFScomposicao]![ComboFSEQUIPA] AS AtribEquipaTecnico, T.TecnicoLista, T.FuncaoTecnicoLista
FROM tblFSListaTecnicos AS T LEFT JOIN (SELECT tblFSListaTecnicos.TecnicoLista FROM tblFSListaTecnicos WHERE (tblFSListaTecnicos.AtribDataTecnico=Forms!frmFScomposicao!ComboFSDATA) And (tblFSListaTecnicos.AtribEquipaTecnico=Forms!frmFScomposicao!ComboFSEQUIPA))  AS T2 ON T.TecnicoLista=T2.TecnicoLista
WHERE T2.TecnicoLista Is Null;

Open in new window


Note: Set the popup property of the form to NO first
open the form and make your selection, then run the query Q_Append
then check your table,

you will notice that if you don't make a new selection nothing will be added to the table.

post back your result.

here is the Query using EXISTS
INSERT INTO tblFSListaTecnicos ( AtribDataTecnico, AtribEquipaTecnico, TecnicoLista, FuncaoTecnicoLista )
SELECT [Forms]![frmFScomposicao]![ComboFSDATA] AS AtribDataTecnico, [Forms]![frmFScomposicao]![ComboFSEQUIPA] AS AtribEquipaTecnico, T.TecnicoLista, T.FuncaoTecnicoLista
FROM tblFSListaTecnicos AS T 
Where Exists (SELECT tblFSListaTecnicos.TecnicoLista FROM tblFSListaTecnicos WHERE (tblFSListaTecnicos.AtribDataTecnico=Forms!frmFScomposicao!ComboFSDATA) And (tblFSListaTecnicos.AtribEquipaTecnico=Forms!frmFScomposicao!ComboFSEQUIPA)) = False

Open in new window

0
Armando Vilela Baiões Self-taughtAuthor Commented:
Hi Rey


I used AppendQuery and it fetches the data, but duplicates and triples them and so on. The same goes for EXISTS. Maybe this is not the right way to do this. Anyway, I used a more "mechanical" method. I put a listbox that is fed by the two Combos from the bottom. This ListBox has the records I want to fetch. Then with a Loop, these records are inserted into the form. This way, it works and I get the results I want.

The code is as follows:

Dim TotalCombo As Integer
TotalCombo = TecnicosParaCopia.ListCount
For x = 0 To TotalCombo - 1
TecnicosParaCopia = TecnicosParaCopia.ItemData(x)

'''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''
Forms!frmFScomposicao.subfrmFSlistatecnicos.SetFocus
Forms!frmFScomposicao.subfrmFSlistatecnicos!TecnicoLista.SetFocus
  DoCmd.GoToRecord , "", acFirst
 contadorperson = 0
Do While Not IsNull(Forms!frmFScomposicao!subfrmFSlistatecnicos!TecnicoLista)

 If Forms!frmFScomposicao.subfrmFSlistatecnicos!TecnicoLista = TecnicosParaCopia Then
 
     MsgBox "Tecnico " & [TecnicosParaCopia] & " repetido!", vbInformation, ""
    
     Me.TecnicosParaCopia.SetFocus
     Exit Sub
     End If
   Forms!frmFScomposicao.subfrmFSlistatecnicos.SetFocus
    DoCmd.GoToRecord , "", acNext

    Loop
   Forms!frmFScomposicao.subfrmFSlistatecnicos!TecnicoLista = Me!TecnicosParaCopia.Column(0)
   Forms!frmFScomposicao.subfrmFSlistatecnicos!FuncaoTecnicoLista = Me!TecnicosParaCopia.Column(1)
   Forms!frmFScomposicao.subfrmFSlistatecnicos!AtribDataTecnico = Me!ComboFSDATA
   Forms!frmFScomposicao.subfrmFSlistatecnicos!AtribEquipaTecnico = Me!ComboFSEQUIPA
   Forms!frmFScomposicao.subfrmFSlistatecnicos.SetFocus
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next x

Open in new window

Database1.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
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.
0
Armando Vilela Baiões Self-taughtAuthor Commented:
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.
0
Armando Vilela Baiões Self-taughtAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.