Solved

Combine data from 2 tables

Posted on 2004-08-19
7
174 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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