If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summarise data by Month
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 |
#2
|
|||
|
|||
Summarise data by Month
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" wrote: 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 |
#3
|
|||
|
|||
Summarise data by Month
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Summarise data by Month
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" wrote: 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" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
Summarise data by Month
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. "GavinD" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
Summarise data by Month
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" wrote: 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. "GavinD" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
|
|||
|
|||
Summarise data by Month
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" wrote: 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" wrote: 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. "GavinD" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#8
|
|||
|
|||
Summarise data by Month
Many thanks Karl, works like a dream.
Cheers Gavin "KARL DEWEY" wrote: 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" wrote: 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" wrote: 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. "GavinD" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|