Query to calculate the Number of days in a 13 Period Year

[RonanM]

---

Hi


I have a database which is unfortunately in Access 2000 due to that being the only version available within the company.

I need to query the number of days that fall in each period (13 periods in the year). I have attached a sample with 3 records in the table.
I manually added the days for the Course in the Course details table, but need a query that would work it out for me....
I.E. Course 1 = 5 days in Period 1, Course 2 = 7 days (5 in Period 1, and 2 days in Period 2), and Course 3 = 10 days (which has 5 days in Period 2,and 5 days in Period 3).

The table Course details holds the Course name, start and end date (I have also added P1 - P13, for informational purposes, but would like my query to allocate the number of "working" days (I use a function to achieve this) of a course that falls or spans each period....

I can do it in excel with a nested If statement looking at dates in cells, but cannot fathom how to do this in Access.

There are 2 tables, one with the Course Details and the other with the period Details

Any help would be GREATLY appreciated.

Regards

Ronan

[June7]

My suggestion is to create a VBA procedure to calculate the period breakdown of given date range. The code likely would involve recordsets and/or UPDATE sql action, DLookup(), looping structures, and nested If Then Else's.

[jzwp11]

If you use a query that brings in both your class table and the period table (without a join) you will end up with a Cartesian Product of the two tables. You can then filter that query based on the dates to show the applicable periods covered by each class. I did that in the attached database in the query named Q1. From there, you can create a second query that tests how the class start and end dates fall compared to the start and end dates of each period and apply your workdays function accordingly to calculate the number of work days in each period that the class covers. I attempted that in the query named Q2. The data you showed in your fields P1, P2, P3 etc. may not be correct. For example, for class 2 which runs from 4/22/2012 to 4/26/2012, should all be contained within period 1's start and end dates of 4/1/2012 and 4/28/2012 respectively but you show P1=5 and P2=2 (no days should occur in P2). Further, Class 3 spans 5 periods (1 through 5), but you only show data in P2 and P3. I would also double check to make sure your workdays function is calculating as you intended. Also, are you concerned about holidays?

Database attached with the 2 queries mentioned.

[June7]

jzwp11 - amazing! Don't think I would have ever envisioned that ClassDaysInPeriod expression.

Ronan, as you can see by this example, there may be no reason to save the Days data into table as can be calculated whenever needed.

[RonanM]

Thanks... i did it (I think)... file attached... Seemes to don "exactly" what I need it to do
Each Period in the query, uses the line below, just changing the Period each time to P1, P2, etc..

P1: Sum(IIf([YR]="12/13" And [CSD]>[PSD] And [CSD]<=[PED] And ([CED]<=[PED]) And [Periods]="P1",WD([CSD],[CED]),IIf([YR]="12/13" And [Periods]="P1" And [CSD]>[PSD] And [CED]>[PED],WD([CSD],[PED]),IIf([YR]="12/13" And [Periods]="P1" And [CSD]>[PSD] And [CED]>[PED],WD([CSD],[PED]),IIf([YR]="12/13" And [Periods]="P1" And [CSD]<[PSD] And [CED]>[PED],WD([PSD],[PED]),IIf([YR]="12/13" And [Periods]="P1" And [CSD]>[PSD] And [CED]>[PED],WD([CSD],[PED]),IIf([YR]="12/13" And [Periods]="P1" And [CSD]>=[PSD] And [CED]>[PED],WD([CSD],[PED]),IIf([YR]="12/13" And [Periods]="P1" And [CSD]<[PSD] And [CED]<[PED],WD([PSD],[CED]),0))))))))

Cheers

Ronan