Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: Not sure if Excel is best solution for my problem

    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!

  2. #2
    Join Date
    Nov 2008
    Posts
    2

    i think ms access is good to help

    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

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    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.

  4. #4
    Join Date
    Nov 2008
    Posts
    2

    Thanks

    I hope your problem solved

  5. #5
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •