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
|
|||
|
|||
Counting days in a crosstab query
I need to count the number of days each month and quarter between two dates.
For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#2
|
|||
|
|||
Counting days in a crosstab query
Your question isn't real clear. The number of days betwee Jan 15 and March
23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#3
|
|||
|
|||
Counting days in a crosstab query
I am writing this to assist a friend in counting the number of days per month
between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#4
|
|||
|
|||
Counting days in a crosstab query
The simple method is to create a table of all dates:
tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#5
|
|||
|
|||
Counting days in a crosstab query
Please forgive my lack of knowledge, are you using a SQL statement to create
that query? If it is, which type of query are you using? "Duane Hookom" wrote: The simple method is to create a table of all dates: tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#6
|
|||
|
|||
Counting days in a crosstab query
This is a totals query. I noticed now that I missed a "]" following Thedate.
The SQL view of the query should be: SELECT Month([TheDate]) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate]); -- Duane Hookom MS Access MVP "Scooter" wrote in message ... Please forgive my lack of knowledge, are you using a SQL statement to create that query? If it is, which type of query are you using? "Duane Hookom" wrote: The simple method is to create a table of all dates: tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#7
|
|||
|
|||
Counting days in a crosstab query
I appreciate your patience with me.
I currently have a table called applications and the two fields are transdate and reldate. How would those two fields fir into the sql statement you created. I chose to use two fields as thre will be many entries and all will have different trans and rel dates. "Duane Hookom" wrote: This is a totals query. I noticed now that I missed a "]" following Thedate. The SQL view of the query should be: SELECT Month([TheDate]) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate]); -- Duane Hookom MS Access MVP "Scooter" wrote in message ... Please forgive my lack of knowledge, are you using a SQL statement to create that query? If it is, which type of query are you using? "Duane Hookom" wrote: The simple method is to create a table of all dates: tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#8
|
|||
|
|||
Counting days in a crosstab query
You have to tell us how you want your table to fit into the result. Your
question was to count the number of days between two dates. Then you added that you wanted these grouped by month. That is what you have. Now you have mentioned a table and two fields. You need to provide several sample records and how your friend would like to see a result displayed. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I appreciate your patience with me. I currently have a table called applications and the two fields are transdate and reldate. How would those two fields fir into the sql statement you created. I chose to use two fields as thre will be many entries and all will have different trans and rel dates. "Duane Hookom" wrote: This is a totals query. I noticed now that I missed a "]" following Thedate. The SQL view of the query should be: SELECT Month([TheDate]) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate]); -- Duane Hookom MS Access MVP "Scooter" wrote in message ... Please forgive my lack of knowledge, are you using a SQL statement to create that query? If it is, which type of query are you using? "Duane Hookom" wrote: The simple method is to create a table of all dates: tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#9
|
|||
|
|||
Counting days in a crosstab query
I apologize.
I have a table with a start date and an end date. These dates are for entry and exit of a program. I need to be able to count the days each month that a person in in the program. meaning if 3 people are in the program in January, one for 4 days, one for 7 days and one for 8 days, I need to create a report that will calculate and give me the total of 15 days. Since the program can span over a month, meaning start in January and end in Feb or March, I need to be able to count the number of days then person is in the program for each month. I thought a cross tab report might be the easiest way to break it down, but I'm open if there is a better way. I apologize for not being clearer with my initial question. "Duane Hookom" wrote: You have to tell us how you want your table to fit into the result. Your question was to count the number of days between two dates. Then you added that you wanted these grouped by month. That is what you have. Now you have mentioned a table and two fields. You need to provide several sample records and how your friend would like to see a result displayed. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I appreciate your patience with me. I currently have a table called applications and the two fields are transdate and reldate. How would those two fields fir into the sql statement you created. I chose to use two fields as thre will be many entries and all will have different trans and rel dates. "Duane Hookom" wrote: This is a totals query. I noticed now that I missed a "]" following Thedate. The SQL view of the query should be: SELECT Month([TheDate]) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate]); -- Duane Hookom MS Access MVP "Scooter" wrote in message ... Please forgive my lack of knowledge, are you using a SQL statement to create that query? If it is, which type of query are you using? "Duane Hookom" wrote: The simple method is to create a table of all dates: tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
#10
|
|||
|
|||
Counting days in a crosstab query
Create a query with your table with no name given and the table of dates
that I suggested earlier. Don't join the tables and change the query to a totals query. Add the field "theDate" to the grid and change the column to Mth:Month([theDate]). Add "theDate" field to the query again change the Total from Group By to Where Set the criteria to Between [Start Date] and [End Date] Add the primary key field from your table with no name change the Total from Group By to Count Your final SQL view should look something like: SELECT Month([TheDate]) AS Mth, Count([ID]) AS NumOfDays FROM tblDates, tblWithNoNameGiven WHERE TheDate Between [Start Date] And [End Date] GROUP BY Month([TheDate]); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I apologize. I have a table with a start date and an end date. These dates are for entry and exit of a program. I need to be able to count the days each month that a person in in the program. meaning if 3 people are in the program in January, one for 4 days, one for 7 days and one for 8 days, I need to create a report that will calculate and give me the total of 15 days. Since the program can span over a month, meaning start in January and end in Feb or March, I need to be able to count the number of days then person is in the program for each month. I thought a cross tab report might be the easiest way to break it down, but I'm open if there is a better way. I apologize for not being clearer with my initial question. "Duane Hookom" wrote: You have to tell us how you want your table to fit into the result. Your question was to count the number of days between two dates. Then you added that you wanted these grouped by month. That is what you have. Now you have mentioned a table and two fields. You need to provide several sample records and how your friend would like to see a result displayed. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I appreciate your patience with me. I currently have a table called applications and the two fields are transdate and reldate. How would those two fields fir into the sql statement you created. I chose to use two fields as thre will be many entries and all will have different trans and rel dates. "Duane Hookom" wrote: This is a totals query. I noticed now that I missed a "]" following Thedate. The SQL view of the query should be: SELECT Month([TheDate]) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate]); -- Duane Hookom MS Access MVP "Scooter" wrote in message ... Please forgive my lack of knowledge, are you using a SQL statement to create that query? If it is, which type of query are you using? "Duane Hookom" wrote: The simple method is to create a table of all dates: tblDates =========== TheDate date/time Then create a query like: SELECT Month([TheDate) as Mth, Count(*) as NumOf FROM tblDates WHERE TheDate Between #1/15/2005# and #3/23/2005# GROUP BY Month([TheDate); -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I am writing this to assist a friend in counting the number of days per month between the start date and the end date. Due to the nature of the report, the total number of days between wont work. I need the number of days in Jan, Feb, and March ie: 15 days in Jan, 28 days in Feb.... I thought a crosstab query might break them down easier. - Scotty "Duane Hookom" wrote: Your question isn't real clear. The number of days betwee Jan 15 and March 23 is DateDiff("d",#1/15/2005#, #3/23/2005#). I'm not sure what any of this has to do with a crosstab query. -- Duane Hookom MS Access MVP -- "Scooter" wrote in message ... I need to count the number of days each month and quarter between two dates. For example Jan 15 and March 23. I need to count the total days for Jan, Feb and March. I'm having a terrible time trying to figure to the calculations. Any ideas? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab Query Help | Becks | New Users | 17 | October 11th, 2005 08:31 PM |
Crosstab Query / Column Headers | Jeff Schneider | Running & Setting Up Queries | 2 | September 7th, 2005 05:30 PM |
Crosstab query with irregularly-spaced dates | Carl Rapson | Running & Setting Up Queries | 2 | March 17th, 2005 10:42 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |