Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Unanswered: How should I approach this? (query related)

    I need to get a count of records that match a certain criteria within a 12 month range. This count needs to be based on a moving range.

    For example, I need the count of records from Mar 02 through Feb 03 (12month period) and then keep moving the date range back one month at a time, i.e.,

    March 02 thru February 03
    February 02 thru January 03
    January 02 - thru December 02
    December 01 thru November 02
    and so on.....

    I know I can create a number of static queries with the pre assigned date ranges, and then have a separate query count the number of records in each of these queries. But, that method doesn't seem very efficient.

    Is there a better way to accomplish this?

    It would be nice to have a macro that would automate this and then populate a table with the totals. I don't have a lot of experience with VBA, but would not be opposed to learning.

    Thank you, for any help,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "and so on....."


    how far back?

  3. #3
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19
    12 months back.


    Originally posted by r937
    "and so on....."


    how far back?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, to start this off, you need an integers table with the following 12 values in it (for the 12 ranges you want to report on) --

    create table integers (i integer);

    insert into integers (i) values (0);
    insert into integers (i) values (1);
    ...
    insert into integers (i) values (11);


    more to come...

    rudy

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry for the delay, had kids to feed and put to bed

    generate two dates based on the integer i --

    the first date is 12 months before the first of the current month

    the second is 1 day less than the first of the current month

    both dates go backwards by 1 month for each successive value of i

    Code:
    SELECT integers.i
         , dateadd("m",-i-12
             ,cdate(cstr(year(now()))
               +'-'+cstr(month(now()))+'-01'))
         , dateadd("d",-1,
           dateadd("m",-i
             ,cdate(cstr(year(now()))
               +'-'+cstr(month(now()))+'-01')))
    FROM integers;
    this query yields the following table:

    i Expr1001 Expr1002
    0 2002-04-01 2003-03-31
    1 2002-03-01 2003-02-28
    2 2002-02-01 2003-01-31
    3 2002-01-01 2002-12-31
    4 2001-12-01 2002-11-30
    5 2001-11-01 2002-10-31
    6 2001-10-01 2002-09-30
    7 2001-09-01 2002-08-31
    8 2001-08-01 2002-07-31
    9 2001-07-01 2002-06-30
    10 2001-06-01 2002-05-31
    11 2001-05-01 2002-04-30

    are those the date ranges you're interested in?

    the next step brings your data in with a cross join


    rudy

  6. #6
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19
    Rudy...

    Thank you for this excellent solution. It worked the first time with no problems whatsoever. I just pasted your code into a query and bingo!

    I just want to let you know how much I appreciate your taking the time out of your busy schedule to help people such as myself. I know you don't have to do this, but it certainly is admirable of you to do so.

    Thanks again!

    Jay

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks jay

    i'm glad you were able to complete the last step (the cross join) yourself

    sql is a great language, isn't it?


  8. #8
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Red face

    Rudy...

    I thought I could do the cross join with a little study.....but haven't gotten this thing to work. Could I ask you to post the last piece of this puzzle? Creating the cross join?

    I thought it was going to be simple, once I had these dates created via your Integer table method.

    I tried referring the two date columns inside another query as follows...but, the query would just hang and not return any results.

    Between "#" & [BeginDate] & "#" And "#" & [EndDate] & "#"

    Thanks Rudy...

    Jay

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hi jay

    try this:
    Code:
    select i
         , dateadd("m",-i-12
             ,cdate(cstr(year(now()))
               +'-'+cstr(month(now()))+'-01')) as begindate
         , dateadd("d",-1,
           dateadd("m",-i
             ,cdate(cstr(year(now()))
               +'-'+cstr(month(now()))+'-01'))) as enddate
         , count(*) as numberofrecords
      from integers
         , yourtable
     where yourdatefield 
         between
           dateadd("m",-i-12
             ,cdate(cstr(year(now()))
               +'-'+cstr(month(now()))+'-01')) 
         and
           dateadd("d",-1,
           dateadd("m",-i
             ,cdate(cstr(year(now()))
               +'-'+cstr(month(now()))+'-01'))) 
    group by i
    order by i
    rudy

  10. #10
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19
    Rudy....

    You're making it too easy on me! 8^ )

    Well, this worked beautifully. One last question. Can you recommend a good tutorial book(s) for expanding my knowledge of Access/SQL?

    I get asked to do a fair amount of database work and will be doing some volunteer work for the Little League...I really would like to learn more.

    What about CBT training? Is it worth the investment?

    Thanks again,

    Jay

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Instant SQL Programming by Joe Celko, published many years ago so it should be fairly cheap now (plus, wrox went out of business just last month, so there won't ever be any reprints)

    if you use that amazon link, it has my associate id attached so i'll get a few pfennigs

    CBT? no idea, sorry, haven't seen any

    should you wish to consider me, i can do sql training and consulting on a contract basis, in any amount (minimum 1/4 hour), telecommuting or onsite if you pay my way over

    please feel free to contact me via my site

    rudy
    http://r937.com/

Posting Permissions

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