linking excel sheet to access and adjust access table format.

[tarek_habal]

I would like to use Access as a source for my pivot tables as i am trying to build a pivot table based on 10 different excel sheets. so I performed the following:

1. I linked the excel sheets into Access
2. created a Union Query between the two tables in access
3. created a pivot table in excel based on external data source
a. clicked Pivot table wizard
b. External data source
c. Get data
d. Tab: Databases, I clicked on <New Data Source>
E. Named the query and selected a driver: "Driver do Microsoft Access (*.mdb)
F. Then clicked on "Connect" in navigated to the Access data base then click OK then another OK
G. in the tab "Databases" you'll find the new created data source highlighted it and click OK
H. pick the Union query in the list and click on ">" then click OK
4. Now i have a pivot table in excel based on access as an external data source.

the issue is when the excel sheet linked to access, the column including "No of products" formatted as number in execl in access automatically set to "Double" and it can't be edited as it is linked table. this causing the pivot table to treat as if its not a number so i can get a count but when i use "sum" in the pivot table I get "0" instead of summing the number of products.

any ideas how can i fix this?