Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Unanswered: Access SQL Problem

    Hi

    I have a complex problem that I am trying to solve, and I can't work out how to write the SQL to do it. I have an Access table with values in it where the fields are names of various retail sales types and the rows are string representations of month and year (e.g. 0406).

    I have to create output to columns in an Excel spreadsheet so that graphs can be created. The first column is the date, which is easy, but the second row will use one of the following formulas:

    value = current month data/previous month data

    or

    value = current month data/12 months ago data

    This would be easy if I had only a single input month, but the input is a range of months from 1 to any number. I need some SQL that will return values for the full range of input dates but I don't know how to manipulate the dates in the formula to get the correct output data.

    Can anyone help?

    Thank you
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't quite get what you're trying to get at here...

    In general, if you want to pull records in a date range, use between:

    SELECT *
    FROM yourTable
    WHERE yourDate BETWEEN date1 AND date2

    I'm not sure I get what you need with the string manipulation. Assuming a 4 digit number representing month and year:

    Left(yourString, 2) + '/1/20' + Right(yourString, 2)



    PS: you should really convert to four digit years. 2 digit is messy to work with..
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    What I am trying to do is to pull a set of records from a date range, but I need the value for each month divided by the value of the previous month and then 1 subtracted from the result (don't ask - it's some kind of standard financial formula). The same can apply to the current month but divided by the previous year.

    The month values are in a 4 digit format in the database, and the source that the database extracts them from is YYMM and the values that are expected in the spreadsheet are YYMM, so they are in that format in the database.

    A user inputs a start date and and end date, and the data for that range needs to be extracted.

    To clarify it, what I need is:

    (current month/previous month) - 1,
    (current month - 1/previous month) - 1,
    (current month - 2/previous month) - 1,
    etc.

    Does this make it clearer?

    Thank you
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    But uhhh... those formulas aren't going to yield integers, and therefore are not going to be valid month values... ??
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Sorry, I should have made it clearer - it is not the months that I am altering, but the data that is associated with that month. Let me give you an example:

    If the user selects a date range of 0403 to 0405 (by inputing the range in text boxes), and they want to see the data for the field 44_45_722 (selected from a combo box), then the data in the date and 44_45_722 columns is:

    date 44_45_722

    0403 335583
    0404 332199
    0405 344399

    Therefore, the output to the spreadsheet would be the date column as shown, but for the second column I would need to show the 44_45_722 data divided by the value for the previous month and then 1 subtracted. For example, for the date 0405 it would be:

    (344399/332199) - 1

    or 0.036724975090232059699156228646083

    so the output that I would like to see for the range of 3 months that I have used as an example would be:

    0403 0.13203212759282560222369899103706
    0404 -0.010083943465550996325797194732749
    0405 0.036724975090232059699156228646083

    What I need is a single SQL query to extract this data if that is possible.

    Thank you
    Make something idiot proof and someone will make a better idiot...

Posting Permissions

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