Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Unanswered: Create list of dates?

    Hello all,

    I do a fair number of reports where I need to report daily activity/volume, even when the total for the day is 0. Right now I have a second table with a list of all relevant dates and I join to that. I'd really like something that would allow me to specify a startDate and endDate and the query would automatically ensure that all dates were included.

    I found http://www.dbforums.com/showthread.php?t=1632158 in the Postgres forum that seems to do exactly that, but I can't find/figure out the MS Access equivelant of generate_series().

    I know this is a common task, so I'm guessing I'm just missing something very basic. Please help, thank you!

    Chris

    edit: I'm using Access 2003.
    Last edited by ChrisChuffy; 11-03-08 at 12:48. Reason: Added Access verion

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Calendar table!
    Create a table which contains nothing but those lovely dates and join to it appropriately
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    8
    That's what I am doing, so I guess it's going to stay the same. Thanks!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    george.
    OP says he does that already.
    a second table with a list of all relevant dates and I join to that

    Chris
    for sure VBA could construct a calendar table to suit the selected date range on the fly - but what is the point, you would need to call a horrible loop of UPDATEs before you attempt the JOIN. stick with your pre-constructed calendar table ...which is exactly what george said without reading your post

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So to confirm - your current set up does what you want, you just wanted to try a different way right? You don't need anymore help?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2008
    Posts
    8
    Yep, my current method works, I was just looking for a more elegant way.

    It just seems like such a common kind of issue I thought MS would have made an easy way to automate it.

    Thanks for the quick replies!

Posting Permissions

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