Number of Days that fall in a Period

[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.
Course 1 = 5 days in Period 1, Course 2 = 7 days (5 in Period 1, and 2 in Period 2), and Course 3, 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 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 (along the lines of this---

 =IF(AND($T8>AI$6,$S8<=AJ$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS($T8,$S8),IF(AND($T8>AI$6,$T8>AJ$6,$S8<AJ$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS(AJ$6,$T8),IF(AND($T8>AI$6,$S8<=AJ$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS($T8,AJ$6),IF(AND($T8>AI$6,$S8>=AJ$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS($T8,AJ$6),IF(AND($T8<AI$6,$S8>=AJ$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS(AI$6,AJ$6),IF(AND($T8<AI$6,$S8<=AJ$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS(AI$6,$S8),IF(AND($T8<=AJ$6,$S8>=AI$6,$T8<=AJ$6,$S8>AI$6),NETWORKDAYS(AI$6,$S8),0)))))))

but cannot fathom how to do this in Access.

Any help would be GREATLY appreciated.

Regards

Ronan

 

[marlan]

Hi, look into DateDiff Functions; You can count how many weeks fall in a Period, and sbtract number of saturdays in the Period, etc...

 

[Ronanm]

Hi

I understand the DateDiff functions etc... But can't work out out to pull the right data from the right periods.

There are 2 tables, one with the Course Details and the other with the period Details, but still can't get it to work...

Cheers

 

[Ronanm]

I've had a little success, but now I get the days, but not for just the periods they land in.... I used a Module(Function) to get the working days..

I've attached an updated file..


Cheers

Ronan

 

[Ronanm]

Done (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