Inner Join?

[super_nOOb]

@ 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

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

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