access 2003 pivot view

[Guest]

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!

 

[Duane Hookom]

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

 

[Guest]

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];

 

[Guest]

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

 

[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?

 

[Guest]

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

 

[Guest]

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.

 

[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?