Hi all,

I'm stuck with a problem in Excel and was wondering if you could offer
some advice.

I am programatically pulling data from an Access database and posting
the results in a Pivot table. The query does have an aggregate
function (sum) and therefore also a "group by" section. The database
tables are refreshed daily and the query is run by a user clicking a
button in Excel 2000.

When the Pivot table is displayed, there are sometimes duplicate
headings and therefore an additional row or column which has values
for some data and not for others. Here's an example with a duplicate
heading for the 27% A_Pct_Move.

Sum C A_Pct_Move
B_Pct_Move (30.0%) (28.5%) (27.0%) (27.0%)
(10.0%) 2,741,538 2,304,765 1,884,721
(9.5%) 3,044,220 2,621,340 2,213,502
(9.0%) 3,281,841 2,874,701 2,480,935

Ideally, I would want the table to appear as

Sum C A_Pct_Move
B_Pct_Move (30.0%) (28.5%) (27.0%)
(10.0%) 2,741,538 2,304,765 1,884,721
(9.5%) 3,044,220 2,621,340 2,213,502
(9.0%) 3,281,841 2,874,701 2,480,935


The datatypes in excel are the same and in the database they are of
the same precision. This behavior is not consistant so it is fine
after some daily runs and has duplicates on other days.

Here's some code that produced the table:

query = "SELECT B_Pct_Move, A_Pct_Move, Sum(C) FROM table GROUP BY
B_Pct_Move, A_Pct_Move ORDER BY B_Pct_Move, A_Pct_Move"

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = "ODBC;" & strODBCName & ";"
.CommandType = xlCmdSql
.CommandText = query
.CreatePivotTable TableDestination:=Range("D23"),
TableName:="QueryPivotTable"
End With
With ActiveSheet.PivotTables("QueryPivotTable")
.ColumnGrand = False
.RowGrand = False
.SmallGrid = False
.PivotCache.RefreshOnFileOpen = True
.AddFields RowFields:="B_Pct_Move", ColumnFields:="A_Pct_Move"
.PivotFields("Expr1002").Orientation = xlDataField
.PivotFields("Mkt_Move").NumberFormat = "0.0%;[Red](0.0%)"
.PivotFields("Vol_Move").NumberFormat = "0.0%;[Red](0.0%)"
.PivotFields("Sum of Expr1002").Caption = "Sum C"
.PivotFields("P&L").NumberFormat = "#,##0;[Red](#,##0)"
End With

Thanks in advance for your help.

Regards,

Arun