?
Solved

Combine data from 2 tables

Posted on 2004-08-19
7
Medium Priority
?
193 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:moralju
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Expert Comment

by:natloz
ID: 11845181
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
0
 
LVL 7

Expert Comment

by:natloz
ID: 11845197
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 = ??
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 1500 total points
ID: 11846979
Try this code...
Imports System.Data.OleDb

Sub GetData()    'This sub will take care of everything
   Dim OleCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Your mdb file";User Id=admin;Password=;")

   Dim SqlStr As String
   SqlStr="SELECT  A.agent_id,T.trans_ID,T.type_id FROM tblTransactions T, tblAgents A " & _
           "WHERE T.agent_id=A.agent_id AND  T.date =" & YourDate
   Dim OleDA As New OleDbDataAdapter(SqlStr,OleCon)
   Dim Ds As New DataSet
   OleCon.Open()
   OleDA.Fill(Ds)  
   OleCon.Close()

   Dim DtRow As DataRow
   For Each DtRow In Ds.Tables(0).Rows
      SqlStr2="INSERT INTO tblOtherTable (agent_id,trans_id,type_id,date) VALUES(" & _
                   DtRow.Item(0).ToString() & "," & DtRow.Item(1).ToString() & "," & _
                   DtRow.Item(2).ToString() & "," & YourDate & ")"
     Dim OleCmd As New OleDbCommand(SqlStr,OleCon)
     OleCon.Open()
     OleCmd.ExecuteNonQuery()
     OleCon.Close()
   Next
End Sub


-Baan
0
 
LVL 1

Author Comment

by:moralju
ID: 11849249
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
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 11850814
Using RecordSet DAO is old VB style... modern VB.Net uses ADO.Net  :))
0
 
LVL 1

Author Comment

by:moralju
ID: 11859953
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question