Hi again Debra,

I don't think i quite follow you.

My current sql statement is:

sqlStr = "Select *, Investments.Unit_Value*Purchases.No_Of_Units as " &
_
"TotalUnitValue FROM Agents Agents, Customers Customers, " & _
"Managers Managers, Investments Investments, Purchases
Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND " & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No)"

and i don't know how to add your suggestion to this sqlStr.

would you be able to elaborate please??

thank in advance...im really stressing out for this


cheers
tedy

p/s fyi, this is the sub i'm working on to create the pivot table:

Private Sub GeneratePivot(aRange As Range)

'This subroutine is used to generate the pivot table. It takes the
range
'as an argument to place the pivot table

'variable declaration
Dim activePage As MSForms.Page
Dim refComboBox As MSForms.ComboBox
Dim dataNo As Integer
Dim rsData As ADODB.Recordset
Dim sqlStr As String
Dim strTable As String
Dim strPrimaryKey As String
Dim fillRange As Range
Dim objPivotCache As PivotCache
Dim pivot As PivotTable
Dim pv As PivotField
Dim strDataField As String
Set fillRange = aRange.Cells(3, 1)

'query preparation
On Error GoTo ErrorHandler
Set rsData = New ADODB.Recordset
sqlStr = "Select *, Investments.Unit_Value*Purchases.No_Of_Units as
" & _
"TotalUnitValue FROM Agents Agents, Customers Customers,
" & _
"Managers Managers, Investments Investments, Purchases
Purchases " & _
"WHERE (Agents.Agent_No = Customers.Agent_No AND " & _
"Investments.Investment_No = Purchases.Investment_No AND
" & _
"Investments.Manager_No = Managers.Manager_No AND " & _
"Customers.Cust_No = Purchases.Customer_No)"


'Connect
rsData.Open sqlStr, con, adOpenStatic

'set the pivot cache object with external data source
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)

'assign the recordset to the pivot cache
Set objPivotCache.Recordset = rsData

'set the pivot table
Set pivot =
objPivotCache.CreatePivotTable(TableDestination:=f illRange)
pivot.SmallGrid = False

'set the pivot row field with the value from the user selection
Set pv = pivot.PivotFields(Me.lbRowFields.Value)
pv.Orientation = xlRowField
pv.Position = 1

'set the pivot column field with the value from the user selection
Set pv = pivot.PivotFields(Me.lbColumnFields.Value)
pv.Orientation = xlColumnField
pv.Position = 1


'if the user wants to display no of units, then assign it as
datafield
If Me.optNoOfUnitsNet.Value Then
strDataField = "No_Of_Units"
Set pv = pivot.PivotFields(strDataField)
pv.Orientation = xlDataField
pv.Position = 1
End If

'if the user wants to display total value, then assign it as
datafield
If Me.optTotalValue.Value Then
strDataField = "TotalUnitValue"
Set pv = pivot.PivotFields(strDataField)
pv.Orientation = xlDataField
pv.NumberFormat = "$#,##0.00"
pv.Position = 1
End If

'if the user also selects the pagefield, then set the pagefield
If Me.lbPageFields.Value < "NONE" Then
Set pv = pivot.PivotFields(Me.lbPageFields.Value)
pv.Orientation = xlPageField
pv.Position = 1
' End With
End If

'check if the recordset contains data
If rsData.EOF Then
MsgBox "No data exists!", vbExclamation, "Information"
rsData.Close
Set rsData = Nothing
Exit Sub
End If

'Closing peek
rsData.Close
Set rsData = Nothing

'close the pivot table command bar
Application.CommandBars("PivotTable").Visible = False

Exit Sub

ErrorHandler:

'On Error, exit sub
Exit Sub

End Sub