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

Access query show all dates (not in data) between start date and end date

Is there a way to show all the dates in between a start date and end (dates that are not in the data)? I would like this to be in a query.

What I am attempting is to show how many projects are being worked on per day, per person.

Here is a small example.  The following person has 3 projects ranging from 10/10 - 10/27/08.
So, on 10/10, 10/11 and 10/12  there should be a count of 1; for 10/13, 10/14 and 10/15 there should be a count of 3;
for 10/16, 10/17 a count of 2;
for each date from 10/18 to 10/27 a count of 1.

Name          Start Date         End Date
Person1      10/13/0208       10/17/2008
Person1      10/13/2008       10/27/2008
Person1      10/10/2008       10/15/2008

Thanks for any help!!

0
mlaurin
Asked:
mlaurin
  • 5
  • 4
1 Solution
 
Bradley HaynesCommented:
In your queries you will have a Count keyword in the Select and a range in a where statement.
i.e. where Start Date = 10/13 and End Date = 10/15.
0
 
mlaurinAuthor Commented:
Sorry, I have no idea what you mean.  Could you outline exactly how the query would be set up?
0
 
Jeffrey CoachmanCommented:
"All" days, or "All" Weekdays?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
mlaurinAuthor Commented:
Yikes!  Good question - how about all work days.
0
 
mlaurinAuthor Commented:
It does not actually have to be work days, I could filter them out later.  
Main thing is to find out if it is possible to "add in" the dates between a date range.
Thanks!
0
 
Jeffrey CoachmanCommented:
What do you mean by "Add In dates"

I thought the question was how to get all the days in a date range?

JeffCoachman
0
 
mlaurinAuthor Commented:
show all the dates in between a start date and end (dates that are not in the data).  If you see my initial question, I thought it was clear in my initial question but maybe not.  
However I  resolved it by adding a "calendar table".  So, I can close this question unless anyone knows how to do this without having to have a table with dates.
0
 
Jeffrey CoachmanCommented:
mlaurin,

You don't need a calendar of dates, you just need to loop through all the dates in the range.

But if you have a method that works, then by all means, use it.

JeffCoachman
0
 
mlaurinAuthor Commented:
I would like to know how to loop through all the dates in the range.  It would be way better than having to worry about keeping up with a date calendar table.  If you can step me though it, I would be glad to change my method.
0
 
Jeffrey CoachmanCommented:
mlaurin,

Nope, my way would be way more complicated, involving VBA Loops and temporariy tables.
Not to mention the logic to only count weekdays.

It is best if you stuck with your method

JeffCoachman
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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