Access query, output records based on date period

Posted on 2011-05-13
Last Modified: 2012-05-11
Have a table looking something like this:
Id, Description, From date,       To date
3, ABC              2011.05.02     2011.05.04
6  QWE             2011.05.10     2011.05.14

What I need is to get a output looking someting like this:
Id, Description, From date,       To date,        Date
3, ABC              2011.05.02     2011.05.04   2011.05.02
3, ABC              2011.05.02     2011.05.04   2011.05.03
3, ABC              2011.05.02     2011.05.04   2011.05.04
6  QWE             2011.05.10     2011.05.14   2011.05.10
6  QWE             2011.05.10     2011.05.14   2011.05.11
6  QWE             2011.05.10     2011.05.14   2011.05.12
6  QWE             2011.05.10     2011.05.14   2011.05.13
6  QWE             2011.05.10     2011.05.14   2011.05.14

The important thing is that the output gives one record for and with each date in the from - to date interval. Can this be done using a query or is something that must be solved with vba code.
If it easier could be solved in excel or sql I'm also open for those solutions. I would appreciate any tips or hints.

Question by:hallpett
    LVL 39

    Accepted Solution

    Test this sample. I've proposed maximal interval can be 500 days. If you need more - there is function in module, where you can fill table Numbers with values (you can also generate it dynamically, basing on max range, but it is other task)
    Query - resulting query
    LVL 48

    Expert Comment

    by:Gustav Brock
    Create a table, tblDay, with field Day and values from 1 to the maximum difference in days between your start and end dates.

    Then create this multiplying query:
      DateAdd("d",[Day]-1,[FromDate]) AS [Date]
      DateAdd("d",[Day]-1,[FromDate]) Between [FromDate] And [ToDate]

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now