linking excel pivot tables to access 2000

Hi,

I have an access 2000 database with many tables of data in it.

The tables have 12 fields containing customer information as follows:

1-vendor#, 2-bar code #, 3-store#, 4-department#, 5-style#, 6-item colour, 7-size, 8-active in basics stock, 9-sales, 10-stock on hand, 11-orders placed, 12-week end date

the pivot tables in excel 2000 track this data week by week, which are linked to tables in the access database.

Everything was working fine until I tried to perform a security wizard on my access database so users could not make any changes to the tables in access>>>>>>This corrupted my database some how!

As a possible solution I re-created an entirely new access database, thinking that I could continue to create pivot tables and everything would move smoothly as prior to the corruption, but ive been having problems.

PROBLEM:

After linking the data from access to excel to create my pivot tables the data from the table that is retrieved from access table is incorrect.
it come up with repetative figures eg....15 15 15 or 18 18 18 when the

Ive tried sources on the site and web vbut nothing helps tried manuals etc-
contacted my It dept at work but they have no clue on this issue.

Hope there is a guru expert out there some where... who can help!

please see below:
CORRECT DATA
store (All)
date 13/10/2007
Sum of Stk on hand size
style colour L M S XL Grand
Total
HEDI 99 ASSORTED 74 486
JEDI ASSORTED 74 55 37 41 207
KEDI ASST 137 155 71 75 438
M1018 NAVY 223
WHITE 221
PEDI ASSORTED 79 64 49 47 239
U1008 BLACK 137 127 80 54 398
GREY HEATHER 107 57 78 61 303
WHITE 107 107 68 64 346




INCORRECT DATA

store (All)
date 13/10/2007

Sum of Stk on hand size
style colour L M S XL Grand
Total

HEDI 99 ASSORTED 8
JEDI ASSORTED 4 4 6
KEDI ASST 5 5 5
M1018 NAVY 5 5 6 4
WHITE 5 5 5 4
PEDI ASSORTED 5 5 6 4
U1008 BLACK 8 7 7 4
GREY HEATHER 7 7 7 4
WHITE 8 5
U1009 BLACK 4 4


PS Ive also tried to compact repair the access database... didnt work- tried refreshing the pivot table didnt work....im fairly certain that the data in the access table is correct.....im beginning to think there is a problem with the pivot table although they used to work when i did them before so not quite sure why this is happening and how I can resolve it?

Your help is greatly appreciated!!!!

Many Thanks
Natalie

It looks as if the data in your pivot table is set to count instead of sum. Try right clicking on any of the data fields, choose field settings, and ensure it is set to SUM and not count or some other custom field.

It is odd that the number of records next to each entry is not the same. Is this just a different point in time, or is it screwing up the number of entries as well?

What type of data is your access query pulling from, is it a joined table query? Do the values appear correctly there, if so are there the same number of values as is in your pivot table?

Hi there,

sorry the 2 example I gave you above were 2 different occasions----olny the data is incorrect--- it isnt skipping information or hiding fields----all is correct just the data under each size break is incorrect.

I will try what you have suggested and get back to you if I have any further problems.

Thanks for the tip!

Natalie

I have tried specifying sum instead of count it made all the values 0 I normally have sum and it works but in this case it isnt working for some reason- it didnt work.

I have just checked my database and the data in it is definitely correct. I havent used a query for this I have just linked the pivot to a regular access table with raw which is imported data in it from a .txt file.

The reason I know the data is wrong is because when I add up the stock on hand column the grand total is approx 17,000 when it should be 20,000.

the way i have created the pivot is as follows:

data item:
store#
date

row field:
style
colour

column field:
size

Data item:
SOH- stock on hand

thanks again
Nat

appologies the stock on hand total in my pivot grand total comes up to:70,000 when it should only be approx 20,000


thanks
Nat

i just found out:

Pivot Table Restrictions
You cannot create a pivot table that contains more than 8000 total items.
You cannot create a pivot table that contains more than 256 fields in the Data Area.


I also for got to mention that the table i am linking my excel database to is approx 77,000 row worth of data with 12 fields.......6 of which i use in my pivot table.

Could it be possible that due to the above restrictions and because I am linking a very large table that the data the pivot returns is becomming corrup and therefore showing incorrect values????


thanks again
nat

suggestions anyone?????????? or given up

I havent given up yet!!!!!

I have searched the net all day at work could not find anything basically all I got was stuff I already know about the pivot tables and access..........

still remains a mystery....................

anyone that can crack this mystery i would be entiely greatfull.


Many Thanks
Natalie

Originally Posted by psumvp

It looks as if the data in your pivot table is set to count instead of sum. Try right clicking on any of the data fields, choose field settings, and ensure it is set to SUM and not count or some other custom field.

It is odd that the number of records next to each entry is not the same. Is this just a different point in time, or is it screwing up the number of entries as well?

What type of data is your access query pulling from, is it a joined table query? Do the values appear correctly there, if so are there the same number of values as is in your pivot table?

Ive tried this any other suggestions?

Thanks
Natalie

Originally Posted by psumvp

It looks as if the data in your pivot table is set to count instead of sum. Try right clicking on any of the data fields, choose field settings, and ensure it is set to SUM and not count or some other custom field.

It is odd that the number of records next to each entry is not the same. Is this just a different point in time, or is it screwing up the number of entries as well?

What type of data is your access query pulling from, is it a joined table query? Do the values appear correctly there, if so are there the same number of values as is in your pivot table?

Ive also posted some more information just so you are aware
thanks