Solved

Combine data from 2 tables

Posted on 2004-08-19
7
172 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check if number is currency 15 31
VB.Net - For Loop Error 5 37
Make a border less form movable 2 23
how to use laptop or pad camera in vb.net windows application 2 55
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 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

24 Experts available now in Live!

Get 1:1 Help Now