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

Wow that table did not look like the preview from my newsreader. Sorry
about that.

Basically, the 27% column heading for the A_Pct_Move is duplicated and
there are blanks in the table. I hope that makes sense.

Thanks in advance.

Arun

Well, it turns out that it was a precision issue related to floating
point arithmetic. As strange as it sounds, I changed the datatype in
the database to currency with fixed precision and the pivot table
displayed correctly.

Regards

Arun




(arun) wrote in message . com...
Wow that table did not look like the preview from my newsreader. Sorry
about that.

Basically, the 27% column heading for the A_Pct_Move is duplicated and
there are blanks in the table. I hope that makes sense.

Thanks in advance.

Arun