• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Access query, output records based on date period

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.


 
0
hallpett
Asked:
hallpett
1 Solution
 
als315Commented:
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
Interval.mdb
0
 
Gustav BrockCIOCommented:
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:
SELECT 
  tblWorkShift.ID, 
  tblWorkShift.Description, 
  tblWorkShift.FromDate, 
  tblWorkShift.ToDate, 
  DateAdd("d",[Day]-1,[FromDate]) AS [Date]
FROM 
  tblWorkShift, 
  tblDays
WHERE 
  DateAdd("d",[Day]-1,[FromDate]) Between [FromDate] And [ToDate]
ORDER BY 
  tblWorkShift.ID, 
  DateAdd("d",[Day]-1,[FromDate]);

Open in new window


/gustav
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now