access 2003 pivot view

  • Thread starter SaveTheMonarchButterflies
  • Start date
S

SaveTheMonarchButterflies

I have an access 2003 query that looks at our general ledger data and
crosstabs it into rows by account number, columns by month. Everything ties
out perfectly in the query itself. When I build a pivot table view, all the
cents are dropped and dollars only are displayed and calculated, causing
rounding errors and out of balance conditions. Is there a setting I am
missing? Changing the format doesn't change anything. Again, the underlying
query returns perfectly.

I am posting this in "Queries" as there is no pivot category. Please advise
if another forum is more appropriate.
Thanks in advance!
 
D

Duane Hookom

There should be no rounding of sums in a crosstab value. Do you mind sharing
your SQL View?
 
S

SaveTheMonarchButterflies

I think this is what you asked for. If not, please let me know and I will
provide it. Again, I want to say that when I run the crosstab query, the
dollars and cents are there; It's just when I put it into pivot table view
that they disappear, and the calculations are based on the dollars only, not
the cents:

SELECT [Account] & " - " & [Unit 1] & " - " & [Unit 2] & " - " & [Unit 3] &
" - " & [Unit 4] & " - " & [Type] AS Sort, tblActualBudget.FY,
tblActualBudget.Account, [Account] & " " & [Description] AS AcctDesc,
tblActualBudget.[Unit 1], tblActualBudget.[Unit 2], tblActualBudget.[Unit 3],
tblActualBudget.[Unit 4], tblActualBudget.Type, tblActualBudget.[05 01],
tblActualBudget.[05 02], tblActualBudget.[05 03], tblActualBudget.[05 04],
tblActualBudget.[05 05], tblActualBudget.[05 06], tblActualBudget.[05 07],
tblActualBudget.[05 08], tblActualBudget.[05 09], tblActualBudget.[05 10],
tblActualBudget.[05 11], tblActualBudget.[05 12], tblActualBudget.[05 13],
tblActualBudget.YR_Total
FROM tblActualBudget
ORDER BY [Account] & " - " & [Unit 1] & " - " & [Unit 2] & " - " & [Unit 3]
& " - " & [Unit 4] & " - " & [Type];
 
S

SaveTheMonarchButterflies

I forgot to say thank you in my previous response. I am grateful for your
interest in this. Thanks!
Here's one record from that query. You can see the cents.
Sort FY Account AcctDesc Unit 1 Unit 2 Unit 3 Unit 4 Type 05 01 05 02 05
03 05 04 05 05 05 06 05 07 05 08 05 09 05 10 05 11 05 12 05 13 YR_Total
13220 - - - - - Actual 05 13220 13220 WIP -
OVERHEAD Actual 228910.32 11719.62 -6932.65 73473.99 97475.96 -60122.59 -12798.66 90627.08 -45641.51 -157775.2 108026.81 326963.17
 
D

Duane Hookom

You first stated:
"all the cents are dropped and dollars only are displayed and calculated,
causing rounding errors and out of balance conditions. Is there a setting "

Then you poste an example of your data that includes cents. What's up with
that?
 
S

SaveTheMonarchButterflies

Whats up is this:
"ties out perfectly in the query itself. When I build a pivot table view, all
the cents are dropped and dollars only are displayed and calculated,
causing rounding errors and out of balance conditions. Is there a ..."
Another way to say it is when I run the query, I see the dollars and cents.
I can copy them to Excel and add the numbers up and it ties out to the penny.
But when I run the query, then change the view to pivot table view, the pivot
table view drops the cents from all the values, and performs its calculations
 
S

SaveTheMonarchButterflies

One new thing: I just used NorthWind, and put a couple of their dollars/cents
queries into pivottable view. They do show the cents. So it's got to be
something I'm doing, but what? My data comes from a link to a SQL database,
but if that were a factor, why wouldn't I be experencing the problem at the
query level too? I'm stumped.
 
D

Duane Hookom

Are you really using a "pivot table" or is this a crosstab query as per your
first post?

Do you really have a table with field names containing names of
months/years?
 
S

SaveTheMonarchButterflies

It's a crosstab query with a new feature built on it. In access 2003 once you
run a query and are looking at the results, you can get into the "View" menu
and change the view to "PivotTable" and create a pivot table right there.
(That's what I'm doing, but you can also create a pivot chart, and also from
a form, table, or I believe other objects.)
So, in my case the underlying table has dollars and cents, the query results
are dollars and cents, and the pivot table chops the cents off the display
format and excludes them from any calculated fields you build in the pivot
table view.

However, I figured it out! The pivot table view(engine or whatever they call
it) can't seem to handle the decimal data type of the underlying table. When
I change the data type for a field to either single or currency (the only two
I tried), then the pivot table view works the way I would expect it to.
Have you used pivot table or pivot chart views much? Over the last month I
have posted several inquiries around the net asking to compare notes with
anyone who is evaluating it, and haven't had a reply yet.
 
D

Duane Hookom

I use crosstabs quite a bit. When I need to use pivot tables, I open Excel
and link to my Access/SQL Server tables/queries.
 
S

SaveTheMonarchButterflies

I'm testing the Access PT view because of an article in a journal we
subscribe to, which claims there are several advantages over Excel's PT. So
far I like the fact that you can set up the data in a more pivot friendly
fashion by creating a query, then running the PT right on the query results.
(You can also run it on tables and bound forms.) It saves the last setup you
made, which turns out to be a good thing unless you don't like the last thing
you did. There is no undo. The article also claims there is more extensive
drill down into the data, which I haven't tinkered with yet.
The article also referred to a bug which is fixed in Office XP Service Pack
1. Since I have SP1 I didn't look up the bug.

If you decide to give it a try let me know what you think!
Thanks for letting me think at you about this. The rounding error isn't
completely fixed by changing the field size in the data type, but is greatly
diminished. I hope I can make a discovery that takes it the rest of the way.
 
Top