VB 6.0 - How to get from one table row records to another table column records

Asked By madhavi on 26-Jan-12 11:00 PM


I need show one table row records another table column records through vb6 program for crystal reports..

Actually i got one table Name : UDV6

select distinct UDv6TxInc,UDv6NTInc,UDv6DvEqu,UDv6TxMal,UDv6TxFgn,UDv6Net,UDv6UIC,udv6fcd

another table : 
select sum(UDv4TxInc) as TaxIncome, sum(UDv4NTInc) as NonTxIncome, sum(UDv4DvEqu) as DivEqualize,
sum(UDv4TxMal) as TaxMalaysia, sum(UDv4TxFgn) as TaxForeign, sum(UDv4NetA) as NetPayout,udv4fcd,
sum(UDv4HldU) as OSUnit
from udv4 

I created another table : UWR0001

select UWRDecCd,UWRDistributed,UWRAllocated,UWRFcd from uwr0001

i use this 2 statements and save the data in to 3rd table(UWR0001)
My problem is:

3rd table ( UWRDecCd)  1st field save the data based on the above 2 select statements..
for ex:

3rd Table:

 UWRDecCd                       UWRDistributed                            UWRAllocated                                UWRFcd

1 45789.000 - UDv6TxInc 34456.00 - UDv4TxInc             0001
2 23567.00 - UDv6NTInc  23078.00 - UDv4NTInc      0002

Here UWRDECCd - 1 means  UDv6TxInc
 and UDv4TxInc

UWRDECCd - 2 means  UDv6NTInc and UDv4NTInc

The 1st select statement data need to  save in the 3rd table 2nd column based on the UWRDecCd
The 2nd Select Statement data need to save in the 3rd table 3rd cloumn based on the UWRDecCd

these all for crystal reports in vb6.
i need to do through vb6 program...

please any one suggest me.its very urgent..

Thanks in advance..



D Company replied to madhavi on 26-Jan-12 11:33 PM
Hello Friend,

Here is one sample , which retrives the data from different table. here you have to just change the name of table and coulmn, might be fe condion marked as red.

 Dim connect_string As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "<strong class="highlight">Data</strong> Source=""" & TextBox4.Text & """;" & _
      "Persist Security Info=False"
      Dim conn_test As New OleDb.OleDbConnection(connect_string)

      Dim cmd4 As New OleDbCommand("<strong class="highlight">SELECT</strong> * <strong class="highlight">FROM</strong> Stud ORDER BY Name", conn_test)

      Dim studAdap As New OleDbDataAdapter
      studAdap = New OleDbDataAdapter
      studAdap.SelectCommand = cmd4

      Dim studTab As DataTable
      studTab = New DataTable()

      '' MaskedTextBox1.DataBindings.Add("text", studTab, "name")
      TextBox1.DataBindings.Add("Text", studTab, "name")
      TextBox2.DataBindings.Add("Text", studTab, "lname")
      TextBox3.DataBindings.Add("Text", studTab, "score")
      lblPhotoFile.DataBindings.Add("Text", studTab, "foto")
      studMgr = DirectCast(Me.BindingContext(studTab), CurrencyManager)
      Call ShowPhoto()
  Dim cmd5 As New OleDbCommand("<strong class="highlight">SELECT</strong> * <strong class="highlight">FROM</strong> scho WHERE scho.name =  '" & TextBox1.Text & "'", conn_test)

      Dim schoAdapa As New OleDbDataAdapter '*****
      schoAdapa = New OleDbDataAdapter
      schoAdapa.SelectCommand = cmd5

      Dim schoTaba As DataTable  '******
      schoTaba = New DataTable()

      TextBox5.DataBindings.Add("Text", schoTaba, "school")
      TextBox6.DataBindings.Add("Text", schoTaba, "city")
      Label4.DataBindings.Add("Text", studTab, "foto")

      Call ShowFhoto()

this is example is for student data fetching from three different tables

Hope this will help you!!
Venkat K replied to madhavi on 27-Jan-12 12:49 AM
It seems you are trying to transpose the data [rows as columns]
You can use the UNION to get the data into columns
Once you get the distinct value into a table just transponse data like this:


This is how you can transpose the data you can also use PIVOT and UNPIVOT concept if you are using sql 2005 and above. Check this link for examples on how it works: