Pivot Table View - Using Microsoft Access |
Author |
Message |
Jo |
|
Group | : Members |
Posts | : 12 |
Joined | : 2008-03-21 |
|
Profile |
|
Posted : 2008-10-23 07:16:42 |
I have created a query using following tables "InvoiceLine" & "Item".
I have been able to extract data and looks correct in Datasheet View however in PivotView "InvoiceLineAmount" the decimal placings do not show nor calculate - it is not rounding up just cutting it off.
Any ideas
Thanks
Jo |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-10-23 08:12:46 |
Check the format properties of the calculated cells. |
|
|
|
Jo |
|
Group | : Members |
Posts | : 12 |
Joined | : 2008-03-21 |
|
Profile |
|
Posted : 2008-10-23 09:12:03 |
I think I have checked everything
In Design View & DataSheet View format is "Standard" Decimal Places is 2 - when a go to datasheet view it looks correct.
In PivotTable View the Number is "Standard"
From PivotTable View Menu - Properties - Captions - Following Advanced Information:
Total unique name [Measures.].[Total2]
Total name Total2
Total source field InvoiceLineAmount
Total type Calculated
and I still cannot get it to calculated including the 2 decimal places - do not know where else to look.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-10-23 15:55:04 |
Try opening your pivot table form in design view, select the calculated control(s) that you want formatted, view properties, then set the Format to fixed or standard, and Decimal Places to the required number. Close, save and view; your controls will now show the decimal precision that you specified. |
|
|
|
Jo |
|
Group | : Members |
Posts | : 12 |
Joined | : 2008-03-21 |
|
Profile |
|
Posted : 2008-10-24 07:15:15 |
Tried this it did not work |
|
|
|
Jo |
|
Group | : Members |
Posts | : 12 |
Joined | : 2008-03-21 |
|
Profile |
|
Posted : 2008-10-30 09:26:44 |
Is there anyone else that can help - out there.
I am about to pull my hair out. Tried contacting Microsoft Access - say QB issue - phone QB - different answers.
Know I used the sample database - and tried running the pivot view - the results are the same - format not correct drops of the 2 decimal places and does not round up.
Does anyone use Microsoft Access 2002 and Quick Books - that could shed some light. PLEASE
cheers Jo |
|
|
|
Jo |
|
Group | : Members |
Posts | : 12 |
Joined | : 2008-03-21 |
|
Profile |
|
Posted : 2008-11-02 08:57:08 |
Thanks to ACE training - finally able to isolate problem.
Which quite rightly was pointed out by QODBC support - Microsoft Access 2002 - has a fault in pivot view - where it truncates
A white paper is available on Microsoft website - only requires an expression added to the field.
Is there a transaction table that lists in DETAIL invoices & credit memos together - THEY SEEM TO BE SEPARATE.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-11-03 09:00:32 |
|
|
|
Jo |
|
Group | : Members |
Posts | : 12 |
Joined | : 2008-03-21 |
|
Profile |
|
Posted : 2008-11-03 09:44:05 |
Thanks so much - Started work today - and everything just click (I got so caught up with the Pivot View - investigating just fried my little brain). Regarding stored reports, and extracting data.
So now I understand a little I shall have some fun running reports. |
|
|
|