I am unable to gather data - must be lack of INNER JOIN

Question

I have two tables, the tab_prefixo has number of prefixo and gerente responsible (important data), and in the other (table tab_valores), also presents the prefixo, plus nome, valor and data.

I want that with command prefixo.movefirst, he takes the first prefixo table tab_prefixo and look, in the table tab_valores, the lines that have the same valor prefixo and copy that data to a new table, the table tab_correio, that will feature prefixo, nome, valor, data and gerente, and forward mail to such prefix. After, with command prefixo.movenext, he passes to the second prefixo table tab_prefixo and, again, search for the data containing the same prefixo on the table tab_valores and copy the required data into the table tab_correio, and send mail to such a prefix.. and so on.

I am managing to make a query only, with the commands below:

Dim db As Database
Set db = CurrentDb
Dim Linha As Integer

Set prefixo = db.OpenRecordset("select * from Tab_prefixo order by prefixo")
Set valores = db.OpenRecordset("select * from tab_valores")
Set correio = db.OpenRecordset("tab_correio")

prefixo.MoveFirst
Do While Not prefixo.EOF

   If agencias!CODPREF = divida!agencia Then

   correio.AddNew
   correio!prefixo = divida!prefixo
   correio!nome = divida!nome
   correio!valor = divida!valor
   correio!Data = divida!Data
   correio!GERENTE = agencias!GERENTE
   correio.Update

   End If

   agencias.MoveNext
Loop

   'CurrentDb.Execute "DELETE * FROM tab_correio"

End Sub

I actually know that my command is incomplete.. something is missing, and there must be something out of place, but I stopped here.

If anyone can help me, I really appreciate it, because I’m having a hard time moving on.. All of a sudden there’s one more to go. inner join, I’m trying everything, but it’s not walking..

Answer 1

Taking into account that the prefix is unique enters the tables. And that the tables are created with the fields you reported, basically this would already solve your problem.

insert into tab_correio
select Tprx.prefixo, Tval.nome, Tval.valor, Tval.data,Tprx.gerente from tab_prefixo  Tprx
inner join tab_valores Tval
on (Tprx.prefixo = Tval.prefixo)