Inner Join?

Hey guys,

Im using a string sql code to get the average of each client i have. THe code is below:

VB Code:

If cbClient.Text <> "<ALL>" Then
                strSql = "SELECT name, value as average FROM (" & vbCrLf
                strSql = strSql & "Select '" & cbClient.Text & "' as [Name], AVG([" & cbClient.Text & "]" & ".Value) as [Value] from "
                strSql = strSql & "[" & cbClient.Text & "]" & " where [" & cbClient.Text & "].Date Between #" & mindate & "# And #" & maxdate & "# " & vbCrLf
                strSql = strSql & ");"
            Else
                strSql = "SELECT name, value as average FROM (" & vbCrLf
                Dim FILENAME As String
                For i = 0 To schemaTable.Rows.Count - 1
                    FILENAME = schemaTable.Rows(i).Item("TABLE_NAME")
                    If (Not FILENAME.StartsWith("~TMP")) And Not (FILENAME.StartsWith("Msys")) And Not (FILENAME.StartsWith("fx_")) And Not (FILENAME.StartsWith("pr_")) And Not (FILENAME.StartsWith("cd_")) Then
                        If IsFirstSubquery Then
                            IsFirstSubquery = False
                        Else
                            strSql = strSql & "union " & vbCrLf
                        End If
                        strSql = strSql & "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Value] from "
                        strSql = strSql & "[" & FILENAME & "]" & " where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & vbCrLf
                    End If
                Next i
                strSql = strSql & ");"
            End If
            cmd = New OleDbCommand(strSql, con)
            dr = cmd.ExecuteReader
            'The control datagridview does not have the items property, but it has the datasource property
            'then we can use it to use that we need to assign the results of the query to an 
            'arraylist, for instance, then assing this arraylist to the datasource property of
            'the datagrid
            Dim QueryResult As New ArrayList
            While dr.Read
                Dim dummy As New Average
                dummy.Name = dr("name")
                If Not IsDBNull(dr("average")) Then
                    dummy.Average = dr("average")
                Else
                    dummy.Average = "0"
                End If
                QueryResult.Add(dummy)
            End While
            dgvAverage.DataSource = QueryResult
            'set the "average" column display order as the second column
            Me.dgvAverage.Columns("average").DisplayIndex = 1
            'average being the index of the column 
            Me.dgvAverage.Columns("average").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            'set the "name" column display order as the first column
            Me.dgvAverage.Columns("name").DisplayIndex = 0
            'name being the index of the column
            Me.dgvAverage.Columns("name").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            dr.Close()
            cmd.Dispose().
 Private Class Average
        Private _name As String
        Private _average As String
        Public Property Name() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property
        Public Property Average() As String
            Get
                Return _average
            End Get
            Set(ByVal value As String)
                _average = Math.Round(Double.Parse(value)).ToString
                If _average.Length < 16 Then
                    Dim strTemp As String = String.Empty
                    Dim chr As Char() = _average.ToCharArray
                    For i As Integer = chr.Length - 1 To 0 Step -1
                        If (chr.Length - i) Mod 3 = 0 And i <> 0 Then
                            strTemp = "," & chr(i) & strTemp
                        Else
                            strTemp = chr(i) & strTemp
                        End If
                    Next
                    _average = strTemp
                End If
            End Set
        End Property
 
    End Class

My result table looks like this:
name average
aa 193,275
ar 67,180
at 43,716
au 65,468

I also have another table that is called cd_clients that look like this:
name cy tax fee retro
aa USD No 1.50% 0.00%
ar USD No 1.50% 0.00%
at USD No 1.50% 0.00%
au USD No 1.50% 0.00%


To get in my final query all of this data together should i add a inner join to my string sql?
What i want is this:
name average cy tax fee retro
aa 193,275 USD No 1.50% 0.00%
ar 67,180 USD No 1.50% 0.00%
at 43,716 USD No 1.50% 0.00%
au 65,46 USD No 1.50% 0.00%

thx in advance for the help

ok maybe i should redo the question to see if it makes it easier:
With the strSql i get a output like this

SELECT name, value as average FROM(
Select 'aa' as [name], AVG([aa].Value) as [Value] from [aa] where [aa].Date Between #01/01/2006# And #03/31/2006#
);

An this returns me the name and the average

How can i, in the same datagrid also add the info from the cd_clients table? Should i ask this info in the same StrSQL or do another and the ask for it here:

Code:

---

dgvAverage.DataSource = QueryResult

---

please notice that the info of cd_clients table is static and does not depend on dates as in the strsql. Please notice too that the id would be the column name. That is what i think i would use to bind the 2 tables together

EDIT: I guess i should use something like WHERE cd_clients.name= [aa].name
But actually im confused on how to do this in my StrSQL and also how to asign this new stuff in my dr.read

how bout this

Code:

---

SELECT aa.aa AS [name], AVG(aa.Value) AS [Value],cd_clients.cy, cd_clients.tax, cd_clients.fee, cd_clients.retro FROM aa INNER JOIN cd_clients ON aa.aa = cd_clients.aa WHERE aa.Date BETWEEN #01/01/2006# AND #03/31/2006#

---

The inner join will only work if there are always values in both tables, otherwise you would want to use an outer join

@ bmahler- thx for the reply. Just one question. Should i change anything in the 1st select? And how can i set my dr.read to make the output in the datagrid too

SELECT name, value as average FROM(
Select 'aa' as [name], AVG([aa].Value) as [Value] from [aa] where [aa].Date Between #01/01/2006# And #03/31/2006#
);

EDIT: The sql output is looking like this:

SELECT name, value as average FROM(
Select 'aa' as [name], AVG([aa].Value) as [Value], cd_clients.currency, cd_clients.vat, cd_clients.mngmt_fee, cd_clients.retrocession from [aa] OUTER JOIN cd_clients on [aa].Name = cd_clients.[aa] where [aa].Date Between #01/01/2006# And #03/31/2006#
union
Select 'ar' as [name], AVG([ar].Value) as [Value], cd_clients.currency, cd_clients.vat, cd_clients.mngmt_fee, cd_clients.retrocession from [ar] OUTER JOIN cd_clients on [ar].Name = cd_clients.[ar] where [ar].Date Between #01/01/2006# And #03/31/2006#
);

And the VBCODE is this

VB Code:

strSql = "SELECT name, value as average FROM (" & vbCrLf
                Dim FILENAME As String
                For i = 0 To schemaTable.Rows.Count - 1
                    FILENAME = schemaTable.Rows(i).Item("TABLE_NAME")
                    If (Not FILENAME.StartsWith("~TMP")) And Not (FILENAME.StartsWith("Msys")) And Not (FILENAME.StartsWith("fx_")) And Not (FILENAME.StartsWith("pr_")) And Not (FILENAME.StartsWith("cd_")) Then
                        If IsFirstSubquery Then
                            IsFirstSubquery = False
                        Else
                            strSql = strSql & "union " & vbCrLf
                        End If
                        strSql = strSql & "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Value], cd_clients.currency, cd_clients.vat, cd_clients.mngmt_fee, cd_clients.retrocession from "
                        strSql = strSql & "[" & FILENAME & "]" & " OUTER JOIN cd_clients on [" & FILENAME & "].Name = cd_clients.[" & FILENAME & "] where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & vbCrLf
                    End If
                Next i
                strSql = strSql & ");"

Is it correct?

And here it is where i ask for the result in the DataGrid

VB Code:

cmd = New OleDbCommand(strSql, con)
            dr = cmd.ExecuteReader
            Dim QueryResult As New ArrayList
            While dr.Read
                Dim dummy As New Average
                dummy.Name = dr("name")
                If Not IsDBNull(dr("average")) Then
                    dummy.Average = dr("average")
                Else
                    dummy.Average = "0"
                End If
                QueryResult.Add(dummy)
            End While
            dgvAverage.DataSource = QueryResult
'____________________________________________________
    Private Class Average
        Private _name As String
        Private _average As String
        Public Property Name() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property
        Public Property Average() As String
            Get
                Return _average
            End Get
            Set(ByVal value As String)
                _average = Math.Round(Double.Parse(value)).ToString
                If _average.Length < 16 Then
                    Dim strTemp As String = String.Empty
                    Dim chr As Char() = _average.ToCharArray
                    For i As Integer = chr.Length - 1 To 0 Step -1
                        If (chr.Length - i) Mod 3 = 0 And i <> 0 Then
                            strTemp = "," & chr(i) & strTemp
                        Else
                            strTemp = chr(i) & strTemp
                        End If
                    Next
                    _average = strTemp
                End If
            End Set
        End Property
 
    End Class

You should be specifying a left or right outer join I would presume that you want to use a left outer join in this case, which will return all records in table aa and only those records in table cd_clients
like so

VB Code:

strSql &= "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Value], cd_clients.currency, cd_clients.vat, cd_clients.mngmt_fee, cd_clients.retrocession FROM "
strSql &= "[" & FILENAME & "]" & " LEFT OUTER JOIN cd_clients on [" & FILENAME & "].Name = cd_clients.[" & FILENAME & "] where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & System.Environment.Newline

Also VbCrlf is vb6 you should use system.environment.newline which is the .net version

I got this error message:

Code:

---

You tried to execute a query that does not include the specified expression 'Currency' as part of an aggregate function.

---

here:
dr = cmd.ExecuteReader

can you post the entire sql statement that is being passed when you are getting the error.

yes sure
The whole sql is bigger then my screen. I couldnt manage to print screen it all.

By the way, the LEFT OUTER JOIN was what i was looking for

It looks like the error might be in your initial select where you select name and value. You will need to add the other fields that you are selecting
ie

VB Code:

strSql = "SELECT name, value as average, Curr, Vat, Fee, Retro FROM (" & System.Environment.Newline
                Dim FILENAME As String
                For i = 0 To schemaTable.Rows.Count - 1
                    FILENAME = schemaTable.Rows(i).Item("TABLE_NAME")
                    If (Not FILENAME.StartsWith("~TMP")) And Not (FILENAME.StartsWith("Msys")) And Not (FILENAME.StartsWith("fx_")) And Not (FILENAME.StartsWith("pr_")) And Not (FILENAME.StartsWith("cd_")) Then
                        If IsFirstSubquery Then
                            IsFirstSubquery = False
                        Else
                            strSql = strSql & "UNION ALL " & System.Environment.Newline
                        End If
                        strSql = strSql & "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Value], cd_clients.currency AS [Curr], cd_clients.vat AS [Vat], cd_clients.mngmt_fee AS [Fee], cd_clients.retrocession AS [Retro] from "
                        strSql = strSql & "[" & FILENAME & "]" & " LEFT OUTER JOIN cd_clients on [" & FILENAME & "].Name = cd_clients.[" & FILENAME & "] where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & system.Environment.Newline
                    End If
                Next i
                strSql = strSql & ");"

Also you should use UNION ALL as opposed to UNION
this is a quote from a post about sql rules to live by

Quote:

---

Always use UNION ALL
UNION without ALL does a DISTINCT automatically - you lose common rows. Slower and dangerous for obvious reasons.

---

@bmahler - Many thanks for the reply. I tried the code you posted and now i get a oledDbException was unhandlesd: Syntax error in FROM clause.

in the same
dr = cmd.ExecuteReader

EDIT: Thanks for the Union All tip :afrog: :thumb:

hmmmm. LodeDbException?? do you mean oledbexception?

place your query section in a try catch loop and catch the exception to get a more specific error message
ie

VB Code:

Try
            cmd = New OleDbCommand(strSql, con)
            dr = cmd.ExecuteReader
            Dim QueryResult As New ArrayList
            While dr.Read
                Dim dummy As New Average
                dummy.Name = dr("name")
                If Not IsDBNull(dr("average")) Then
                    dummy.Average = dr("average")
                Else
                    dummy.Average = "0"
                End If
                QueryResult.Add(dummy)
            End While
            dgvAverage.DataSource = QueryResult
        Catch ex as OleDBException
            MessageBox.Show(ex.Message)
        End Try

Yes, sorry for the misstyping. It was a oledbexception. I ran your code and got in the message box this:Syntax error in From clause
And i think i'll have to do this after the StrSql works right?

VB Code:

Dim QueryResult As New ArrayList
            While dr.Read
                Dim dummy As New Average
                dummy.Curr = dr("Currency ")
                dummy.Vat = dr("VAT")
                dummy.Fee = dr("Fee")
                dummy.Retro = dr("Retro")
                dummy.Name = dr("name")
                If Not IsDBNull(dr("average")) Then
                    dummy.Average = dr("average")
                Else
                    dummy.Average = "0"
                End If
                QueryResult.Add(dummy)
            End While
            dgvAverage.DataSource = QueryResult

VB Code:

Private Class Average
        Private _name As String
        Private _average As String
        Private _Curr As String
        Private _Vat As String
        Private _Fee As String
        Private _Retro As String
        Public Property Retro() As String
            Get
                Return _Retro
            End Get
            Set(ByVal value As String)
                _Retro = value
            End Set
        End Property
        Public Property Fee() As String
            Get
                Return _Fee
            End Get
            Set(ByVal value As String)
                _Fee = value
            End Set
        End Property
        Public Property Vat() As String
            Get
                Return _Vat
            End Get
            Set(ByVal value As String)
                _Vat = value
            End Set
        End Property
        Public Property Curr() As String
            Get
                Return _Curr
            End Get
            Set(ByVal value As String)
                _Curr = value
            End Set
        End Property
 
        Public Property Name() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property
        Public Property Average() As String
            Get
                Return _average
            End Get
            Set(ByVal value As String)
                _average = Math.Round(Double.Parse(value)).ToString
                If _average.Length < 16 Then
                    Dim strTemp As String = String.Empty
                    Dim chr As Char() = _average.ToCharArray
                    For i As Integer = chr.Length - 1 To 0 Step -1
                        If (chr.Length - i) Mod 3 = 0 And i <> 0 Then
                            strTemp = "," & chr(i) & strTemp
                        Else
                            strTemp = chr(i) & strTemp
                        End If
                    Next
                    _average = strTemp
                End If
            End Set
        End Property
 
    End Class

Try removing the initial SELECT
ie

VB Code:

Dim FILENAME As String
                For i = 0 To schemaTable.Rows.Count - 1
                    FILENAME = schemaTable.Rows(i).Item("TABLE_NAME")
                    If (Not FILENAME.StartsWith("~TMP")) And Not (FILENAME.StartsWith("Msys")) And Not (FILENAME.StartsWith("fx_")) And Not (FILENAME.StartsWith("pr_")) And Not (FILENAME.StartsWith("cd_")) Then
                        If IsFirstSubquery Then
                            IsFirstSubquery = False
                        Else
                            strSql = strSql & "UNION ALL " & System.Environment.Newline
                        End If
                        strSql = strSql & "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Average], cd_clients.currency AS [Curr], cd_clients.vat AS [Vat], cd_clients.mngmt_fee AS [Fee], cd_clients.retrocession AS [Retro] from "
                        strSql = strSql & "[" & FILENAME & "]" & " LEFT OUTER JOIN cd_clients on [" & FILENAME & "].Name = cd_clients.[" & FILENAME & "] where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & system.Environment.Newline
                    End If
                Next i

I was playing around a bit and found that i got errors trying to run a query similar to yours, but it seemed to work fine without the initial select

ok........i tried it

Now i get an error in union query.
This code i was using was working:

VB Code:

strSql = "SELECT name, value as average FROM (" & vbCrLf
                Dim FILENAME As String
                For i = 0 To schemaTable.Rows.Count - 1
                    FILENAME = schemaTable.Rows(i).Item("TABLE_NAME")
                    If (Not FILENAME.StartsWith("~TMP")) And Not (FILENAME.StartsWith("Msys")) And Not (FILENAME.StartsWith("fx_")) And Not (FILENAME.StartsWith("pr_")) And Not (FILENAME.StartsWith("cd_")) Then
                        If IsFirstSubquery Then
                            IsFirstSubquery = False
                        Else
                            strSql = strSql & "union " & vbCrLf
                        End If
                        strSql = strSql & "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Value] from "
                        strSql = strSql & "[" & FILENAME & "]" & " where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & vbCrLf
                    End If
                Next i
                strSql = strSql & ");"
            End If

But would return me only the name and the average

let me ask you this... how come you have so many tables with the same fields and different names?

Actually its a big sotry. Let me tell you.
I have a folder where each client has a .txt file with his name. And each txt file has the Date and Value. So what i do is a dynamic import (in Access) of all of those .txt tables to create my database. And now i have this many tables (each one is named after each client name). And i am working with that...........

ah i see. It would make sense to perhaps consolidate all those tables into one table, It will save you this type of headache in the end. If that were the case, you would not need the unions at all and would greatly improve performance.

As far as the issue you are having, I have tried quite a few ways to create a query similar to yours and have yet to get one to work with the initial select statement, however when I just use a union all it runs just fine.

if you consolidated all the tables, then you could retrieve all the info you are looking for in one select statement

I am going to pop open access and try this a couple more times but I would have to say that this issue seems to be in your database design and fixing that will definitely improve your chances of not running into more issues like this one.

on a side note, the field names you are using, name,date and currency are all reserved SQL keywords and should not be used as field names. They will still work, but can also cause you more of a headache than they are worth

@bmahler - Many thx for the reply. I actually tought i had some problems in my database and relationship design. I'll try to put it all together to see if it makes my life easeir. I just have one question. Should i create a new table for that or should i do a StrSql that unites all those tables together and then use this StrSql as an array, and then work on top of it?

because this is the access code im using to import the tables

VB Code:

Function tableexists(tbl As String) As Boolean
On Error GoTo nofile
tableexists = CurrentDb.TableDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt
 
exithere:
Exit Function
 
nofile:
tableexists = False
Resume exithere
End Function
 
Sub DeleteImportTxtFiles()
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"
 
FileName = Dir(Path & "*.*")
 
Do While (Len(FileName) > 0)
    nFiles = nFiles + 1
    ' If array is full...
    If nFiles = UBound(Files) Then
    ' Make room for 10 more files
    ReDim Preserve Files(1 To nFiles + 10)
    End If
    Files(nFiles) = FileName
    'If no more files exist exit loop
    If FileName = "" Then Exit Do
    'Make FileName without .txt extension
    FileName = Left(FileName, InStr(1, FileName, ".") - 1)
    'Delete all tables
    If tableexists(FileName) Then DoCmd.DeleteObject acTable, FileName Else
    'Check if table exists and if not create a new one
    If tableexists(FileName) Then Else DoCmd.TransferText acImportDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName & ".txt", True, ""
    ' Get next file
    FileName = Dir
 Loop
    
    ReDim Preserve Files(1 To nFiles)
 
End Sub

Should i consolidate them all inside this code in a way that my import would be only one table. Or should i make a query of the imported tables?

I would create a new table That based on the fields, something like
People
--id AutoNumber
--personName -text
--pDate -Datetime Short Date
cd_clients
--id autonumber
--Peopleid -number Long Integer Foreign key to people
--Money - currency
--vat -text?
--mngmt_fee -currency
--retrocession -text?

This is just a guess, because I am not exactly sure what the fileds are and such

actually it is something like this that i have i hand
http://vbforums.com/showthread.php?t=442370

I would change your import procedure to just read the files and insert that information into the appropriate table (meaning one table only)

ps sent you a pm

That was actually my first idea. But i quite didnt manage to do that........do you have any code that might help?

ps: I just replied :D

I sent you back a modified db file to show you a simpler structure.

What you would then want to do is read your text files and create an entry in the cd_client table with the name and values for that client. Once this record is created, get the primary key of that client (id) and insert that id withe the values and dates into the client_data table.

the clientData Table field cd_clientID is foreign keyed to the cd_client (id) field.

Hope this all helps you

Great...........thx again for the help.........I'm still waiting for the email to arrive ^^
:D

that is great....thx again
oO
How did you do that? I have no clue...did u use a module?

Edit: I have one concern on that method too. I had the module runnuing because the .txt files should be changed everyday. SO the module was everyday deleting all the data there and importing the new ones. Is there anyway to do that with your method?

sure, if the cd_clients table will stay the same, you can just clear the client_data table and when importing the new file, just be sure to get the id from the cd_clients table and then insert the new values into the client_data table

Edit: never even saw the modules, I just assumed you were writing this in vb.net (because this is in the vb.net forum...) but it looks like you are using vba. is this correct?

no no.......I was using vba to manage the database, but the program is in vb.net. The oonly thing i was doing in vba was importing the database.

I just have no clue how to import the data the way you did. Was it by vb.net?

I was just using a copy and paste to do it just to show how the table will look, but you can definitely do it programatically

oh ok.................that is awesome stuff
I had never seen it before...........So basically you copied the values from the old table to the new client_data?
And in the cd_clients how did u manage to do that this that shows the data from the client_data. Is it only with the relashionship?

Anyways that was great...............im impressed

I went to database relationships and created a relationship between the 2 tables joining them on the cd_clients (id) column and the client_data(cd_clientsID) column

If you go to database relationships in the menus on top, you will see the relationship

oh ok.........I saw that. I thought it came from there...........Im still slightly confused on how did u manage the client_data one. If i go to my .txt file and try copy and paste it doesnt work. Also if i try to use the "get external data" tool i can only get data one by one................im a bit confused heheheh..........sorry for my noobness :(

you will need to write a routine to read the lines in the text file and insert them one by one into that table once you have the key.

Can you post one of the text files?

Quote:

---

Originally Posted by bmahler

you will need to write a routine to read the lines in the text file and insert them one by one into that table once you have the key.

Can you post one of the text files?

---

Oh ok. Should the id be in the .txt file as well?
I'll send you one txt file by email

EDIT:I just realized something, which was one of the main reasons i was doing the importing the other way. I wanted to store in the access database also all the .txt files that were once created but that are not being created anymore. And if i do the complete delete of the client_data table and redo it again everyday, if a .txt file intentionally stops to be created i would not have it in the access database anyomore. The way i was importing it previously it would still store the old txt ones
I have to think on a good way out of this

Another idea is that you can store date and filename of files imported in another table when you import them
an example
table
filesImported
--id autnumber
--filename -text
--dateimported -datetime

when you import the file, just check to see if that filename exists in this table, if so, just update the dateimported date, if it is a new file then add it to the table. This would keep an accurate record of the last date and time that all your files were imported

oh, I see. DO this and still import all them to the same table. But the problem is that this table would have to be 3 dimensional, once it has clients in one dimension, dates in another an values in another. Unless i use only the biggest range available of dates and then make the column name as the client name. And then set the formula to be based on the colum name. But still i would also have to make some crazy query to return me the correct date...

I am not really following what you are saying here, What I posted was just an example of a third table that would store the time of import of a file, and give you a reference to see when the last time a file was imported. I am not really following what you are saying about three dimensions.

umm.............let me see how i can explain it:
in the client_data table i have :
id
cd_clientID
valDate
val

id have to change the cd_clientID to something like the name of the client in a way that when making the vba to import it can identify which .txt files are in the folder and which ones are not in way that it can keep the old ones and update the new ones. But then i thouhgt that would make more sense to put the name of the client istead of the val column. And then use one date set for all...........

hmmmm I think you are not following the concept of the database structure that i sent. You should only be storing the name in the cd_client table and in this data table you should be referencing the id of that client and not the name itself. I would also recommend just using vb.net to to the import, it would be a fairly simple class to write to read through that directory and import all the files and store the data to the database. Might save you some headache.