Month Names and number of working days between date range

[MKH2001]

Have a somewhat convoluted requirement.

I have a route mapping table (tbl_RouteMapping)

Contains ProgDateFrom and ProgDateTo
Unfortunately these can span month or number of months.

IE
Route RouteType ProgDateFrom ProgDateTo PerDayOutput
F101 R002 04/04/2011 05/05/2011 100
F201 R002 04/04/2011 06/06/2011 100
F301 R002 04/04/2011 31/07/2011 100
F401 R002 15/05/2011 19/06/2011 100
V101 R003 24/06/2011 15/08/2011 60
V102 R003 31/07/2011 30/09/2011 60

I have a Route Table (Tbl_Routes)
Which identifies which asset is included in each route.

A person runs a report between a seperate date range (DateFrom to DateTo)

I need to be able to identify which routes are included in that input date range (easy enough).

Because I have no idea between date range particular asset is expected to be attended I have to look at a rather round about method to be able to calculate expected performance compared to actual output.

The way I see it I need to identify how many working days (per month) expected to be carried out and equation to calculate expected output, then compare to actual input.

My biggest problem atm seems to be getting a list of Months and the date range for each route which is carried out per month. So I can then parse that output against a Working Days query.

Working Days is easy enough... just need to figure how to parse the date ranges between months to calculate and once can do that (Per route per month) should be simple maths to calculate output and performance.

So for example if someone inputs 01/05/2011 to 07/08/2011 I would like it to return a table

Route RouteType DateFrom DateTo
F101 R002 04/04/2011 30/04/2011
F101 R002 01/05/2011 05/05/2011
F201 R002 04/04/2011 30/04/2011
F201 R002 01/05/2011 31/05/2011
F201 R002 01/06/2011 06/06/2011
F301 R002 04/04/2011 30/04/2011
F301 R002 01/05/2011 31/05/2011
F301 R002 01/06/2011 30/06/2011
F301 R002 01/07/2011 31/07/2011
F401 R002 15/05/2011 31/05/2011
F401 R002 01/06/2011 19/06/2011
V101 R003 24/06/2011 30/06/2011
V101 R003 01/07/2011 31/07/2011
V101 R003 01/08/2011 07/08/2011
V102 R003 31/07/2011 31/07/2011
V102 R003 01/08/2011 07/08/2011

At least that seems to be where I think I need to be at but maybe someone else can suggest a different angle.

Anything to get the desired results works for me =)

 

[MKH2001]

Error in my original scenario ie if user input

01/05/2011 to 07/08/2011

Wouldnt want it to show April as indicated.

Instead lets say input

01/04/2011 to 07/08/2011 to get my made up "desired" result table.

If put in 01/05/2011 to 07/08/2011 would be similar but minus any april rows.

=(

Soz for the confusion.

Any assistance would be greatly appreciated.

 

[MichaelRed]

Start with the fact that Ms. A. considers all dates as "US" formatted (e.g. mm/dd/yy) while your sample data indicate your useage to be the more general (e.g. dd/mm/yy). So most date math will return incorrect date intervals.



MichaelRed

 

[MKH2001]

Well had hoped for something more useful, like an idea how to do what was requested.

The math as mentioned is simple, no matter the date format it is stored dd/mm/yyyy date functions will still work, count of days will work. Can't see any issues there....

Other then trying to get the data extracted as indicated that is, and unfortunately seems noone has been able to shed any light on that.

Still working on it and roundabout methods.
Can't do exactly what I want then if all else fails I try alternatives, even when those might not seem most efficient method.

Sometimes with deadlines and timescales a factor it is the results that matter. And it can be polished when opportunity arises.

 

[dhookom]

I'm not sure why you mentioned "calculate expected performance compared to actual output" and "asset" when it only confuses old guys like me who want just the pertinent facts. I couldn't see anything in your data that relates to these.

The other thing that confuses us in the U.S. is the date format. Regardless of the fact that our date format doesn't make sense, it is the format used in queries etc.

I think you might need a simple table of dates with every date in it.
[tt]
tblDates
=========
TheDate
01/01/2011
01/02/2011
01/03/2011
....
12/29/2015
12/30/2015
12/31/2015
[/tt]
Some developers add columns for day of week, work day, holiday, month, fiscal period, etc. There are a couple threads here that discuss this in wonderfull detail.

You can add tblDates to a query to get month start and end dates for any range of dates.

Duane
Hook'D on Access
MS Access MVP

 

[MKH2001]

Yup Duane,

This is conclusion I came to.

I have list of dates user enters the range and query selects each date within that range and then does all the necessary calculations.

Means I have to maintain a table each cycle to add dates to, but only method I could figure of doing it =(

Mark

 

[dhookom]

I'm not sure why a user has to select each date within the range since they only need begin and end date. Also, why do you have to "maintain a table each cycle to add dates"?

Duane
Hook'D on Access
MS Access MVP