Summarise data by Month

  • Thread starter Thread starter GavinD
  • Start date Start date
G

GavinD

I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
 
Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;
 
Or use this for last query to not show projects --
SELECT Count([Dates-Business_Days_1].[Days Between]) AS [CountOfDays
Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY CVDate(Format([Days Between],"yyyy\,mm"",1"""));


KARL DEWEY said:
Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;


GavinD said:
I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
Click to expand...
Click to expand...
 
Hi Karl,

Many thanks for your detailed example. I will give it a try and let you know
the result. Could you please explain the CountNumber table as I am not sure
what you mean by “CountNUM with number from 0 (zero) through your maximum
project spreadâ€. Is CountNUM a single record with a value totalling the
number of projects OR is it a record for each project ID; in which case could
I not add CountNUM to the main project table so that it increases each time a
new project is added?

Cheers

Gavin


KARL DEWEY said:
Or use this for last query to not show projects --
SELECT Count([Dates-Business_Days_1].[Days Between]) AS [CountOfDays
Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY CVDate(Format([Days Between],"yyyy\,mm"",1"""));


KARL DEWEY said:
Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;


GavinD said:
I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
Click to expand...
Click to expand...
Click to expand...
 
The table CountNumber has one field, datatype number - long integer, with a
number per record like this --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
......
999998
999999
You will notice the two tables (FROM CountNumber, Dates_LeRoy) are not
joined so as to give a Cartesian effect. This is where for every record in
TableA they are multiplied by the number of records in TableB. This effect
has some useful purposes.
No.

GavinD said:
Hi Karl,

Many thanks for your detailed example. I will give it a try and let you know
the result. Could you please explain the CountNumber table as I am not sure
what you mean by “CountNUM with number from 0 (zero) through your maximum
project spreadâ€. Is CountNUM a single record with a value totalling the
number of projects OR is it a record for each project ID; in which case could
I not add CountNUM to the main project table so that it increases each time a
new project is added?

Cheers

Gavin


KARL DEWEY said:
Or use this for last query to not show projects --
SELECT Count([Dates-Business_Days_1].[Days Between]) AS [CountOfDays
Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY CVDate(Format([Days Between],"yyyy\,mm"",1"""));


KARL DEWEY said:
Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;


:

I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

Thank you for your continued support.

I’m almost there. I had a few issues with the CountNumber table but I have
resolved these. The problem that I have now is that the date calculations are
including weekends. The Holidays are being excluded correctly but I must
remove the weekend.

Any suggestions?

Gavin


KARL DEWEY said:
The table CountNumber has one field, datatype number - long integer, with a
number per record like this --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
.....
999998
999999
You will notice the two tables (FROM CountNumber, Dates_LeRoy) are not
joined so as to give a Cartesian effect. This is where for every record in
TableA they are multiplied by the number of records in TableB. This effect
has some useful purposes.
No.

GavinD said:
Hi Karl,

Many thanks for your detailed example. I will give it a try and let you know
the result. Could you please explain the CountNumber table as I am not sure
what you mean by “CountNUM with number from 0 (zero) through your maximum
project spreadâ€. Is CountNUM a single record with a value totalling the
number of projects OR is it a record for each project ID; in which case could
I not add CountNUM to the main project table so that it increases each time a
new project is added?

Cheers

Gavin


KARL DEWEY said:
Or use this for last query to not show projects --
SELECT Count([Dates-Business_Days_1].[Days Between]) AS [CountOfDays
Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY CVDate(Format([Days Between],"yyyy\,mm"",1"""));


:

Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;


:

I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
Click to expand...
Click to expand...
Click to expand...
 
Use this for the [Dates-Business_Days] query --

SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]) AND
((Weekday(DateAdd("d",[CountNUM],[Start]))) Between 2 And 6))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);


GavinD said:
Hi Karl,

Thank you for your continued support.

I’m almost there. I had a few issues with the CountNumber table but I have
resolved these. The problem that I have now is that the date calculations are
including weekends. The Holidays are being excluded correctly but I must
remove the weekend.

Any suggestions?

Gavin


KARL DEWEY said:
The table CountNumber has one field, datatype number - long integer, with a
number per record like this --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
.....
999998
999999
Is CountNUM a single record with a value totalling the number of projects OR is it a record for each project ID
Click to expand...
You will notice the two tables (FROM CountNumber, Dates_LeRoy) are not
joined so as to give a Cartesian effect. This is where for every record in
TableA they are multiplied by the number of records in TableB. This effect
has some useful purposes.
could I not add CountNUM to the main project table
Click to expand...
No.

GavinD said:
Hi Karl,

Many thanks for your detailed example. I will give it a try and let you know
the result. Could you please explain the CountNumber table as I am not sure
what you mean by “CountNUM with number from 0 (zero) through your maximum
project spreadâ€. Is CountNUM a single record with a value totalling the
number of projects OR is it a record for each project ID; in which case could
I not add CountNUM to the main project table so that it increases each time a
new project is added?

Cheers

Gavin


:

Or use this for last query to not show projects --
SELECT Count([Dates-Business_Days_1].[Days Between]) AS [CountOfDays
Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY CVDate(Format([Days Between],"yyyy\,mm"",1"""));


:

Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;


:

I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
Click to expand...
Click to expand...
Click to expand...
 
Many thanks Karl, works like a dream.

Cheers Gavin

KARL DEWEY said:
Use this for the [Dates-Business_Days] query --

SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]) AND
((Weekday(DateAdd("d",[CountNUM],[Start]))) Between 2 And 6))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);


GavinD said:
Hi Karl,

Thank you for your continued support.

I’m almost there. I had a few issues with the CountNumber table but I have
resolved these. The problem that I have now is that the date calculations are
including weekends. The Holidays are being excluded correctly but I must
remove the weekend.

Any suggestions?

Gavin


KARL DEWEY said:
The table CountNumber has one field, datatype number - long integer, with a
number per record like this --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
.....
999998
999999

Is CountNUM a single record with a value totalling the number of projects OR is it a record for each project ID
You will notice the two tables (FROM CountNumber, Dates_LeRoy) are not
joined so as to give a Cartesian effect. This is where for every record in
TableA they are multiplied by the number of records in TableB. This effect
has some useful purposes.

could I not add CountNUM to the main project table
No.

:

Hi Karl,

Many thanks for your detailed example. I will give it a try and let you know
the result. Could you please explain the CountNumber table as I am not sure
what you mean by “CountNUM with number from 0 (zero) through your maximum
project spreadâ€. Is CountNUM a single record with a value totalling the
number of projects OR is it a record for each project ID; in which case could
I not add CountNUM to the main project table so that it increases each time a
new project is added?

Cheers

Gavin


:

Or use this for last query to not show projects --
SELECT Count([Dates-Business_Days_1].[Days Between]) AS [CountOfDays
Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY CVDate(Format([Days Between],"yyyy\,mm"",1"""));


:

Try this --
Substitute your table name for [Dates_LeRoy] and your ProjectID for ID.
Table CountNumber has field named CountNUM with number from 0 (zero) through
your maximum project spread. Table Holidays has field Holiday with
non-working days that are not weekends.

[Dates-Business_Days]
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

[Dates-Business_Days_1]
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between], CVDate(Format([Days Between],"yyyy\,mm"",1""")) AS
ProjectMon
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, CVDate(Format([Days Between],"yyyy\,mm"",1"""))
ORDER BY [Dates-Business_Days_1].ID;


:

I am creating a basic project tracker database in Access 2003 and have the
following table.

ProjectID Start_Date End_Date
Project1 06/06/2008 30/06/2008
Project2 16/07/2008 30/07/2008
Project3 11/08/2008 11/09/2008
Project4 14/09/2008 06/10/2008
Project5 28/10/2008 07/11/2008
Project6 19/11/2008 05/01/2009
Project7 07/01/2009 15/01/2009

What I am trying to create is a report that calculates the number of days
worked on each project and summarises the results by month. The problem that
I have is that many of the projects span more than one month so the working
days for each month have to be calculated and combined with other projects
that span the same month.

Please note that I want the results to measure the working days (ie Mon –
Fri) only, not just the number of days between the start and the end dates.

Using the above data, the result I am looking for should look something like
this. (The values are hopefully correct, but I did have to calculate manually)

REPORT OUTPUT

Month/Year Total Days Worked

Jun-08 17
Jul-08 11
Aug-08 15
Sep-08 21
Oct-08 8
Nov-08 13
Dec-08 23
Jan-09 10

I had a very similar problem in Excel sometime ago and it was resolved using
arrays (I can’t take the credit for that either), but I cannot figure out how
to use the Excel method in Access.

Any ideas, example or suggestions gratefully received.

Gavin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top