Newbie question

[englishkid19]

Hi Guys & Gals.

I have been using MS EXCEL for a few years and am proficient in writing VBA in excel to automate data manipulation routines and pivot table creation for monthly reporting tasks. I am wondering what peoples opinions are on the following:

Our current processs is as follows ;

Data manipulation in excel - ( we export .csv files from our data storage area into excel run v b a code on the .csv and save as an .xls )

From that point we use pivot tables to extract the meaningful data that is required to feed it up the chain to management to manage performance via either word docs or excel tables/charts depending on their specific preference.

I have some experience of Access but am by no means proficient. I have a general feeling it would be possible to extract the numbers I need via Access Queries qjuicker than by manually filtering to specific number ranges on a pivot table each month.

My problem is when the initial source data table changed at the end of the month how do you refresh all your queries to accept the path of the new added table ( its basically a like for like replacement of last months data )...........


I am open to any suggestions comments ,
Thanks for your help

 

[sheetspread]

You can update the old table with the new one, and write a query that selects just the new data, then have the other queries select from that query instead of the table.

 

[Darren Bartrup]

Generally I'd use the same table - just add the new data to the bottom of the table, make sure you've got a date field in the table to identify the month and run my queries using the date field to limit the results.