Pivot Table deletes decimal part of the number - help!

[RenatoServos]

When I drag a field (data type: decimal, linked from SQlServer trough ODBC)to detail area of a pivot table, it shows only the integer part of it, even changing the format it shows the number with zeroes in the decimal part of it. The number shows ok in a regular form or in the query or in the table itself.

The worst is that on the Totals it calculates without the fractions.... wrong results!!!

I'm using access 2002. Any ideas?

 

[MichaelRed]

I would suggest that you generate simple recordsets of the information to be used in your piviot table through select queries and then do the piviot on the 'local' recordset. You should be able to preserve the precision of the records/fields in the (simple) select query.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RenatoServos]

Thanks Michael,

I forgot to tell that I'm using a select query as source for the pivot table and when I open the query it shows the correct numbers. I was thinking in using a MakeTable Query, but this is going to take forever.

 

[MichaelRed]

then, you need to check the field properties in the piviot table query. it is at least possible that you have inadvertantly set the field type to fixed or some other type. From the first post, it APPEARS that you are generating a CrossTab query, not a piviot table. I haven't used the "Piviot Table Form" in Ms. A., as it typically does not afford the power and flexibility of the crosstab query, so some aspects of the function (Piviot Table Form)are quite unfmiliar to me. As I hazily understand it, the whole process relies on Ms. Excel, which is a huge hinderance to me (being a (NOT!) big fan of excel. However, the help stuff says/implies that the process actually goes through Excel to generate the info. IF this is TRUE, and some of the "top" records di not include a fractional part, the 'wizz' may interpert the values as Integer or long and simply trunc them in the transfer ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RenatoServos]

Thanks Michael! It's a Pivot table and the formats are correct (most of them currency). What I foud out is that this occurs only with fields that has the format DECIMAL in the table properties, all others are fine.

Looks like this is a bug in Access. anybody knows someting about it?

Thanks

 

[MichaelRed]

Ms. A. early versions (pre ver 2K?) do not know from decimal, and I would not be suprised if the excel thinggyyyy is out of sync. Perhaps you could coerce the data type to single or double and try it from that perspective?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RenatoServos]

Thanks Michael,(once again, hehehe)
I could not change the filed property from the table because it's just linked to an external DB (sql-from Great Plains) and I'm not suppose to change any of the field properties, but I opened a recorset and created a table , and at that table I could change the field type as DOUBLE, because Access really can't work with decimal (type) in pivot tables.
Appreciate your help!