Pivot Tables using VB

[Minstrel]

Currently I have VB script which creates Pivot Tables in Excel from an Access database. The PTs are then used to create graphs on the same worksheet as the PT.
The Access database is a list of suppliers and their Performance Review Scores.
My problem is that I now have to create a PT from the Access database which shows the top 5 most improved suppliers by Performance and the 5 who have deteriorated the most between 2 given dates.

I have created PTs which show the average Scores for any particular time period, but I cannot figure out how to create a Pivot Table showing the difference from one period to another. It doesn't help that some suppliers will have multiple reviews in any one period (the current PTs take the average).

Can anyone suggest anything?

[RobDog888]

Maybe try going over the FAQ on it.

http://vbforums.com/showthread.php?t=451725

[Minstrel]

Thanks for the suggestion, but I'd already searched the forums for PivotTables.
My problem is not in creating a Pivot Table, I'm very familiar with them, but in how to create a specific table from an Access database which doesn't have the actual fields I need.

I have already used VB to create Pivot Tables and graphs showing the average scores for any time period. I need to find some way in VB of creating a Pivot Table which looks at the average scores for one time period and compares them to the average scores for another time period and graphs the results.

Any suggestions are most welcome

[RobDog888]

If it doesnt have the actual fields you need then where are you getting the data from?

[Minstrel]

Sorry, I'm probably not explaining it very clearly.
The current situation is that the data is downloaded in XML which is then run through Access to create the table I use.
Then, using VBA, I create Pivot Tables and graphs in Excel from the table in Access.

My problem is that, although I can create several different Pivot Tables, what I have now been asked to do is to show the difference in Scores for each supplier from one time period to another.

The way I see it, I need some way of determining which suppliers have scores in both time periods specified and then subtracting the score for the earlier time period from the score for the later time period which will then show which suppliers have improved and which have deteriorated.

I can't even figure out how to do this in Access, let alone create a Pivot Table using VBA.

[RobDog888]

Ok I see a bit clearer now. I think trying to create the query in Access may be an initial step so you can see what/how to do it. There is the query wizard that you can use but not 100% sure if it will work on the same table.

Im not too good with complex db queries but maybe someone else can help out.