I've been given the task to finish a project that someone else started, and I have a lot of work to do in a short amount of time so bear with me.... I have a view in MSSQL 2005 that I need to use to generate detailed reports (via ODBC). The view contains sales data (sales $ and sales qty) by item by customer for the past 7 years. I need to generate a series of reports that show sales $ and sales qty for a given period (say 01/01/2009 through 01/31/2009) and shows the same period for the previous year (01/01/2008 through 01/31/2008) with a % change between them for $ and qty. I prefer to have the end-user enter the date range once and have the system do the calculation to get the previous year's data. The data just needs to be in a simple row/column format. I tried a pivot table, but I couldn't get the previous year data in with the current data. Someone suggested I try an Excel macro along with some VB code (I don't know anything about VB), but I'm wondering 1) if it's possible to do what I need entirely in Excel, and 2) would I be better off doing this in Access? I know my way around Access fairly well, but Access can be a resource hog and it's not hard to corrupt an entire database. Excel is pretty efficient, but I don't know if something like this is pushing it beyond it's reasonable limits. I'll post this question in the Access forum as well to get some more opinions (sorry for the x-post). Thanks!

isthink that you can create an access database that you can import you data to
then you can create a report that contains data you need
then create a sub report on the first report but data source will be the same month but year back
if you decided to use access call me to help for free

Thanks for the reply. The problem with Access is that my SQL data is a mix of text and numeric, so all of the numeric data was coming in to Access as text via linked tables. I ended up building my reports in Crystal.

I hope your problem solved

but I couldn't get the previous year data in with the current data ... meaning too many rows?
Why not create a data access query in excel from the view based on input parametes by the user that will limit data amounts being retrieved into one or two worksheets if necessary. then you can summarize and use this to return to a pivot table to do your calculations.
EXCEL - Help for excel
the above link shows data access queries and parameters.

The other option is to create a procedure in sql based on the sql view
that will return the data needed into excel which is also explained on the blog.