pivot table

  • Thread starter Thread starter Elsie
  • Start date Start date
E

Elsie

hi, I have a pivot table which has a query in access as record source. the
problem with this table is that I can't get the data to be accurate to 2
decimal places.

when I tried exporting the data from access to excel (using office links,
not ms query) and did the pivot table there, all data can be formatted to be
in 2 decimal places and accurate.

I need the 2 dp, else the grand total will be off by hundred of dollars. but
I need the pivot table to be in access, so that the user do not need to
export then manually do the pivot table in excel

pls help.
 
As in Excel, so in Access - formatting doesn't in any way affect the
accuracy of data. Or you round your data to 2 decimal places in Access, or
you modify your query, so that a calculated field {Round(OldData,2) As
NewData} is returned instead of Access table field (OldData).
 
ok, maybe the way I set up the pivot table was wrong.
I used the pivot wiz and based the pivot table on a query.

the query has 3 fields: acct, amt, acct-unit
acct - account code
amt - amount in domestic
acct-unit - department code

so I set acct as the row, acct-unit as the column and dragged the amt into
the data area.
then I insert a calculated total as sum of amt. but the sum of amt is not
giving the correct calculation,
most of the data has 2 dp, for example: 17025.92 was displayed in the pivot
table as 17025.00 and 19216.12 became 19214.00

what went wrong? pls help
 
Back
Top