Solved

Combine data from 2 tables

Posted on 2004-08-19
7
175 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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 …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 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