Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: Querying with lagging dates

    I have a query that groups results data in fields A, B, C and a date field (by month and year, with the day always equal to 1).

    Is it possible to restructure the results to have an additional column "lag" the date by one month?

    For example, suppose I have the following:

    Date District Sales Segment Salesperson Revenue
    1/1/2007 1 A Joe Schmoe 1500
    2/1/2007 1 A Joe Schmoe 2000


    Is it possible to transform this into the following, or something similar?

    Date District Sales Segment Salesperson Revenue Prior Period
    1/1/2007 1 A Joe Schmoe 1500 (12/1/2006 data)
    2/1/2007 1 A Joe Schmoe 2000 1500

    The dates change each month and I may have to query a larger and larger data set (going further back in time), so the results need to be a bit dynamic.

    In a nutshell, can a query search the dataset based on a calculated item (the date field)?

    If this approach is not very good, please advise on a better way -- I can easily scrap this one. A crosstab query will not suit because the actual data will be more than just one data field.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could do it with a subquery:

    SELECT [date], district, sales, segment, salesperson, revenue, (SELECT TOP 1 revenue FROM yourTable t2 WHERE t2.segment = t1.segment AND t2.salesperson = t1.salesperson AND month(t2.[date]) = month(dateadd("m", -1, t1.[date])) and year(t2.[date]) = year(dateadd("m", -1, t1.[date]))
    FROM yourTable t1


    That's the general idea anyways.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Making it dynamic may be your downfall.

    In a straight forward query, it will be specifically what you write it to do, and it will get complicated fast. You will have to manually rewite the queries when you want to change the number of lagging months.

    You could use subqueries, each one providing one month's data, lagged by different amounts, and then assemble them together as one. Lot's a queries, though, could be a performance hit and a pain to maintain.

    1) If you follow the SQL model Teddy proposed, you could write a VB routine that assembles the SQL statement for you and then run the query.

    2) You could pull the data into a recordset and use code to assemble the data you want (this is one of those cases where an unbound recordset would be handy). Since Access doesn't let you use unbound recordsets, you've have to use a temp table, or simply add text to a text box.

    The first method is easier to code, but could end up making a very slow query if you have a lot of data or a slow network,

    The second method is a lot more efficient in that it pulls the data once, processes it locally and the gives the result, but will be more coding.

    Good luck,
    tc
    Last edited by tcace; 03-22-07 at 17:44.

Posting Permissions

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