By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Date Range Report Active Count

P: 13
I am trying to develope a report with a query. The raw Data is JobId, RoughDate, and FinalDate. A job is considered Active if the RoughDate is entered and the FinalDate is null. The job is done and not on the report when the Rough Date and FinalDate is both not null.

What I need from the report is by month how many jobs are active by month. eg 10 jobs start in Jan and 1 has Jan 30 entered in the FinalDate--There is 9 Jobs Active in Jan.

Next 10 more jobs start in Feb and 1 has Feb 27 entered in the FinalDate--There is 18 Active Jobs.

Next 10 more jobs start in March and 1 has March 27 entered in the FinalDate--There is 27 Active Jobs.

Next 0 more jobs start in April and 1 has April 12 entered in the FinalDate--There is 26 Active Jobs. And so on.

Overall the report is to give per month how many jobs are active in any month. Past or present.

See Attached Data
Attached Files
File Type: xlsx Test Data.xlsx (15.0 KB, 101 views)
Nov 15 '17 #1

✓ answered by NeoPa

I would just say, to emphasise, that it's in your best interest to make it clear that you're simply asking for help with something particular. It sounds like you appreciate the point now so it needn't be laboured.

As for your question, I'm a little hazy on what you're trying to say. Like most people I don't want to have to download your attachment to work out what your question is. From what I can see you're looking for how to determine if two points in time overlap (Whether they be dates, times or both - points in time). If so then Time Interval Overlapping (MS Access 2003) should help you understand the concept. Let us know if that helps and/or if you need more/something different.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,418
This sounds more like a request for work than a request for assistance with a problem. Please understand that isn't a service we offer.

I've looked at your earlier threads and I do appreciate you're not very experienced and need help with the basics. That we can help with, but you must be careful how you phrase your questions. If you simply state what you want and give no indication of what you've already tried on your own you're likely to have questions removed.
Nov 15 '17 #2

P: 13
Sorry I haven't had a chance to follow up. I can see where it would be construed as a work request, but it wasn't. I was looking for a function that would work in the criteria portion of the query.

What I have tried is to single out the StartDate and FinalDate with the DateFunction in Year-Month (eg 2017-11) which works great for last month. But the problem is I don't know how to evaluate how many was active in previous months knowing that a job can span up to 12 months.
Nov 22 '17 #3

NeoPa
Expert Mod 15k+
P: 31,418
I would just say, to emphasise, that it's in your best interest to make it clear that you're simply asking for help with something particular. It sounds like you appreciate the point now so it needn't be laboured.

As for your question, I'm a little hazy on what you're trying to say. Like most people I don't want to have to download your attachment to work out what your question is. From what I can see you're looking for how to determine if two points in time overlap (Whether they be dates, times or both - points in time). If so then Time Interval Overlapping (MS Access 2003) should help you understand the concept. Let us know if that helps and/or if you need more/something different.
Nov 23 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.