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

    Unanswered: Need help with getting CY vs LY data in Access 2003

    I have a view in MSSQL 2005 that contains sales data (sales $ and sales qty) by item, by customer going back a few years. I need to get the data into a tabular report format. The reports need to show a data range for current year and the same range for the previous year (i.e. 01/01/2009 through 01/31/2009 and 01/01/2008 through 01/31/2008) for sales $ and sales qty with a % change for each. I figure the date range would be entered by the end user.

    Initially I thought I could do this in Excel through an ODBC pivot table, but I'm having trouble getting the previous year date range in the same table as the current year date range. I searched through this site and found this:

    http://www.dbforums.com/microsoft-ac...ars-month.html

    That sounds like it's pretty much what I need, but I'm not sure where the code in post #8 would be used in my query? I'm pretty knowledgeable in Access but not so much in SQL code. Any suggestions are much appreciated!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wouldn't you just need a criteria expression under your data column that says "the month this year" and "the same month last year"?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Not sure what you mean?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How are you specifying your date range? It should be a simple matter of specifying that date range with an OR that gets the same date range with an offset of -12 months. If you can tell me how you are specifying the date range I can give you the expression you'll need to put in your query.... or someone else will beat me to it
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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