Access DB Row Values into Columns

[malatesh kumar]

Hi I am Working Windows Appl .Net08.
My DB is Access Db.
The below image is from Access DB only.
In the image the table called Contacts having one of the column called 'ContactMedium'. In this Column having values like 'Mobile' , 'email'.
Now i want to saperate these values and create new columns in the same table.
How can make these values as a new columns ??

If i create Crosstab Query it giving error like Value type is not Aggr.

May i know the solution ??

Thanks

[medsont]

hi mala try this

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ClassDatabase.CreateColumns()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class
 
 
Public Class ClassDatabase
    ''' <summary>
    ''' give your database file name
    ''' </summary>
    Public Shared ReadOnly Property DatabasefileName() As String
        Get
            Return "E:\People.mdb"
        End Get
    End Property
    ''' <summary>
    ''' this is your connection string
    ''' </summary>
    Private Shared ReadOnly Property ConnectionString() As String
        Get
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabasefileName & ";"
        End Get
    End Property
 
 
    Public Shared Function CreateColumns() As Boolean
        Try
            Dim _datasValue As New List(Of String)
            Using _oledbConxn As New OleDb.OleDbConnection
                _oledbConxn.ConnectionString = ConnectionString
                _oledbConxn.Open()
                Dim _sql$ = "SELECT * FROM tblContacts"
                Dim Rdr As OleDb.OleDbDataReader
                Using _cmd As New OleDb.OleDbCommand
                    _cmd.Connection = _oledbConxn
                    _cmd.CommandText = _sql$
                    Rdr = _cmd.ExecuteReader
                End Using
                If Rdr.HasRows Then
                    While Rdr.Read
                        _datasValue.Add(Rdr.Item(7).ToString) 'thi is your 'ContactMedium' columnindex
                    End While
                End If
                Rdr.Close()
            End Using
 
            'check and Create Columns 
            For Each _itmstr$ In _datasValue
                If ColumnExists(_itmstr$.ToString) = False Then
                    _itmstr$ = _itmstr$.Replace(" ", "")
                    CreateTable("tblContacts", _itmstr$)
                End If
            Next
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Private Shared Function ColumnExists(ByVal _columnName$) As Boolean
        Try
            Using _oledbConxn As New OleDb.OleDbConnection
                _oledbConxn.ConnectionString = ConnectionString
                Dim _cmd As New OleDb.OleDbCommand("SELECT * FROM tblContacts", _oledbConxn)
                Dim Da As New OleDb.OleDbDataAdapter(_cmd)
                Dim Dst As New DataSet
                Da.Fill(Dst, "tblContacts")
                'getColumns
                For Each _clmn As DataColumn In Dst.Tables(0).Columns
                    If _columnName$.ToString.ToLower.Equals(_clmn.ColumnName.ToLower) = True Then
                        Return True
                    End If
                Next
                _cmd.Dispose()
                Da.Dispose()
                Dst.Dispose()
            End Using
            Return False
        Catch ex As Exception
            Return False
        End Try
    End Function
    Private Shared Function CreateTable(ByVal _tableName$, ByVal _newColumnName$) As Boolean
        Try
            If ColumnExists(_newColumnName) = True Then Return True
            Using _oledbConxn As New OleDb.OleDbConnection
                _oledbConxn.ConnectionString = ConnectionString
                _oledbConxn.Open()
                Dim _sqlCreateTable$ = "ALTER TABLE " & _tableName$ & " ADD COLUMN " & _newColumnName$ & " STRING"
                Using _cmd As New OleDb.OleDbCommand
                    _cmd.Connection = _oledbConxn
                    _cmd.CommandText = _sqlCreateTable$
                    _cmd.ExecuteNonQuery()
                End Using
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
End Class

[medsont]

this is the preview for my above codes....

[techgnome]

do you want a permanent solution? or simply a view that represents the data?

medsoft's method will work if you want a permanent solution... but it's a bit heavy handed...

SELECT C.Title, C.FirstName, C.LastName, C.Street, .... , PHONE.ContactNumber as PhoneNumber, EMAIL.ContactNumber as EmailAddress 
FROM tblContacts C
left join tblContacts PHONE on C.ID = PHONE.ID and PHONE.ContactMedium = 'Mobile'
left join tblContacts EMAIL on C.ID = EMAIL.ID and EMAIL.ContactMedium = 'email'

-tg

[malatesh kumar]

Hi Thanks for given response.
No i dont want Permanent Solution, i want simply view table thats it.
techgnome , as per ur code :
i wrote same code in my Access DB, but i am getting join Error "Syntax error in join Operation"
My Code is as below:

SELECT  C.PersonID, C.EventName, C.Surname, C.GivenName, C.InstitutionName, PhysicalAddress.ContactNumber as PA
FROM tblTEMP AS C RIGHT JOIN tblTEMP ON C.PersonID = tblTEMP.PersonID
and PhysicalAddress.ContactMedium= 'Physical Address' ;

Thanks Medsont Thanks techgnome..

[techgnome]

first. it should be a LEFT join, not a right....

secondly, where did "PhysicalAddress" come from? You didn't alias any of the tables as PhysicalAddress... it should be just tblTemp, both in the join and in the select...

-tg

[malatesh kumar]

My i know as per ur code (#4) what is PHONE indicates.....

[malatesh kumar]

B'se i am Confusing where i want to fill the Column names otherwise can u send me the Syntax of this query....

[malatesh kumar]

Hi i am getting the new columns Successfully..
The image is showing i am getting new columns.
But the problem is the number of rows are increased .
How can i delete the duplicates .

My SQL Query is as Below:

SELECT C.PersonID, C.EventName, C.Type, C.StartDate, C.EndDate, C.Venue, C.OrganizedBy, C.Surname, C.GivenName, C.Gender, C.Nationality, C.InstitutionName, C.InstitutionType, C.JobStatus, 
C.CurrentDesignation, C.Role, [Land-Telephone].ContactNumber AS LPHONE, PhysicalAddress.ContactNumber AS Address, FAX.ContactNumber AS FAX, C.City, C.State, C.Country
FROM ((tblTEMP AS C LEFT JOIN tblTEMP AS [Land-Telephone] ON C.PersonID = [Land-Telephone].[PersonID]) 
LEFT JOIN tblTEMP AS PhysicalAddress ON C.PersonID = PhysicalAddress.[PersonID]) 
LEFT JOIN tblTEMP AS FAX ON C.PersonID = FAX.[PersonID]
WHERE ((([Land-Telephone].ContactMedium)='Land-Telephone') AND ((PhysicalAddress.ContactMedium)='PhysicalAddress') AND ((FAX.ContactMedium)='FAX'));

Thanks

[malatesh kumar]

the image is

[thava]

use DISTINCT Keyword

[malatesh kumar]

HI Before Distinct Command i got some thousands of rows came but after apply the Distinct i am getting 30 Rows. the rows are reduced but actually my output rows should be 9 only. i want to remove few more duplicate records.

In my Temp Table Some persons having only Physical Address but some having both Physical Address as well as LandPhone and Fax...
So for each personID it displays 2 or more records.

May be it Join Problem... I am not understanding.

Thanks

[techgnome]

First, PHONE is an alias for the table. Because in the sample, I joined the table to itself, each one has to have a different name...

Secondly, you're getting multiple lines because each contact has multiple rows in the table...

Thirdly, DISTINCT isn't the solution in this case... but will probably suffice given the info at hand, and trying to keep it simple.

Lastly... with out knowing more about the data and the table's design... I don't know what to tell you about eliminating the duplicate rows... other than to ask, does every contact have a physical address line? Your comment about some having only a physical address would suggest that, but your screen shots say otherwise.

-tg