Combine data from 2 tables
Posted on 2004-08-19
I have 2 tables. an Agent table and a transaction table. what I want to do is combine each agent in the agent table and for each transaction in the transaction table. I want to combine this into another table so a union query or req query would not do the trick.
I can do this in Ms Access as follows:
Set rs = CurrentDb.OpenRecordset("select * from tblAgents where date = ?? )
While Not rs.EOF
Set rst = CurrentDb.OpenRecordset("select agent_id,trans_ID,type_id from tblTransactions where agent_id = rs!agent_id and date = ??)
CurrentDb.Execute "Insert into tblOtherTable (agent_id,trans_id,type_id,date)" & _
" Values (?,?,?,?)"
I typed this out really quick so I might have missed some items but you should get the picture
I really would like to solve this today so I will offer 500 pnts