Appending columns to tables? Showing trending data?

[Bmo]

We have an office full of people that all have the same metrics to manage and we are riding the dashboard wave. I have the report that spits out a page for each person with the data they have to manage daily and that works great.

What I need to do now is display the trending data. Each day I run the report I have a query that sums each persons data that I need. Now I have to find a way to put that into a table so I can add the graph to the report to show the trend. So every day I would like to append a column to the table with that days information so the graph will show a history. Additionally I would like only keep the record for a rolling 30 day period. Lastly I am trying to limit the amount of work in the future if people leave/hire new in the office. I could write/manage tables and queries for all 15 people but I am trying to keep it flexible so we can contiune to use the system if I leave I can hand it off to a capable person and not have to have him or her worry about re writing querys and things of the such. Right now I have 3 tables and 2 queries to run the whole thing and it's not sensitive to the amount of data or people in the workflow.

I am decent with .vba. I did alot of work with excel but with offsets in a more visual type environment so I wouldn't be opposed to scripting it via macro/code since there is a good chance I will have to and it would help me learn a great deal which is why I took a stab at this project to begin with.

Thanks for all your help in advance.

[dcrake]

Wouldit not be wiser to add the activity date in to the query and group by date by person and set a condition under the date field to display only the last 30 days.

>= Date()-30

[Bmo]

I don't have a table with all the info in it like that. Right now it only has 1 day worth of data, tomrrow I would like it to have 2. Day 31 I would have 30 records and would delete the 1st entry and insert one at the end.

[Bmo]

I ran into another problem while I was trying that method. The derivative database is interfaced through excel, business warehouse, sap/bex front end. When I go to pull that much data I over run the entry limit in excel '03.

I'm going to try a crosstab today to group by date and then by person. Who knows, reccomend any good books?