import data format - pivot table mystery

  • Thread starter Thread starter RC
  • Start date Start date
R

RC

I am using Office XP 2002. I have an Access database containing
records with fields for City, Check Amount, and others. City is a
text field and Check Amount is currency. I want to sum all of the
checks in a city. Now I know I can create a query, but I want to do
it via an Excel pivot table.

I import the data into Excel and create a pivot table with City as the
"row" fields and Check Amount as "data items". The data totals though
are not the sum of the check amounts. Rather, they are the total
number of checks. I tried to define the data in Excel as currency and
refreshed the pivot table to no avail, still just the total number of
checks.

Is there something funny going on here with the import of data? I
also tried to just copy and paste the data over, same result.
 
RC,

It sounds as though you have text in your Check Amount cells, not numbers.
So the pivot table has defaulted to "Count of Check Amount," not "Sum of
Check Amount." If so, post back. One way to determine whether cells
contain text or numbers is to ensure that none of the alignment (left,
center, right, justified) buttons has been selected for the cells, then look
at the data. Text will be left-aligned; numbers right-aligned.
 
Back
Top