Solved

Combine data from 2 tables

Posted on 2004-08-19
7
168 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 25

Expert Comment

by:RonaldBiemans
ID: 11844364
0
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now