asked on

Combine data from 2 tables

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 (?,?,?,?)"
rst.MoveNext
Next
rs.MoveNext
Wend

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

RonaldBiemans

use dataset.merge

look here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassmergetopic.asp

natloz

SQL Statement to get results would be...

insert into tblOtherTable(agent_id, trans_id, TransDate)
select agent_id, trans_id, type_id, tblAgents.date as TransDate from tblTransactions
inner join tblAgents on tblAgents.agent_id = tblTransactions.agent_id
where tblAgents.date = ??

Assuming tblOtherTable is created in database

natloz

Forgot type_id in insert...

insert into tblOtherTable(agent_id, trans_id, type_id, TransDate)
select agent_id, trans_id, type_id, tblAgents.date as TransDate from tblTransactions
inner join tblAgents on tblAgents.agent_id = tblTransactions.agent_id
where tblAgents.date = ??

ASKER CERTIFIED SOLUTION

DotNetLover_Baan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

moralju

ASKER

He is the issue I have a Scheduling component from DBI
http://www.dbi-tech.com/SolSched7ProductPage.htm

I was using the component under vb6 and had no problems. but now that I have moved on to vb.net. I am having trouble populating the schedules.
in vb6 I simple sorted the first agent in the transaction table and added all the trans per that agent per line in the control
then moved on to the next agent.

I can't seem to find something similar to rs.EOF
I really need to solve this issue. so far I'm not loving vb.net

DotNetLover_Baan

Using RecordSet DAO is old VB style... modern VB.Net uses ADO.Net :))

moralju

ASKER

I wrote this to for access today.. and I am able to load over 400 schedules in less than 3 seconds.
I need to translate this to vb.net.. I am faily new to vb.net so I'm having trouble.

Private Sub Command1_Click()
Dim rs As Recordset
Dim strID As String
Dim strLname As String
Dim StrFname As String
Dim rcdcnt As Integer
Dim nIndex
Dim nbar

Set rs = CurrentDb.OpenRecordset("select * from tblAgents order by l_name")

Me.ctSchedule0.DateStart = 38215

ctSchedule0.AddColumn "Agent ID", 50
ctSchedule0.AddColumn "Last", 100
ctSchedule0.AddColumn "First", 100
Me.ctSchedule0.TimeType = 3

While Not rs.EOF
Set rst = CurrentDb.OpenRecordset("select * from tblTransactions where agent_id = " & rs!agent_id)

strID = rs!agent_id
strLname = rs!l_name
StrFname = rs!f_name
nIndex = Me.ctSchedule0.AddItem(strID + ";" + strLname + ";" + StrFname)

If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
End If

rcdcnt = rst.RecordCount

For i = 1 To rcdcnt
nbar = Me.ctSchedule0.AddTimeBar(nIndex, rst!TimeStart, rst!TimeEnd, rst!Date, rst!Date)
rst.MoveNext
Next i
rs.MoveNext
Wend

End Sub