Counting days in a crosstab query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scooter - 05 Dec 2005 16:14 GMT

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?

Reply to this Message

Duane Hookom - 05 Dec 2005 16:35 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

>I need to count the number of days each month and quarter between two
>dates.
[quoted text clipped - 3 lines]
>
> Any ideas?

Reply to this Message

Scooter - 05 Dec 2005 16:50 GMT

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

> 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#).
[quoted text clipped - 8 lines]
> >
> > Any ideas?

Reply to this Message

Duane Hookom - 05 Dec 2005 17:16 GMT

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);

Signature

Duane Hookom
MS Access MVP
--

>I am writing this to assist a friend in counting the number of days per
>month
[quoted text clipped - 19 lines]
>> >
>> > Any ideas?

Reply to this Message

Scooter - 05 Dec 2005 17:41 GMT

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?

> The simple method is to create a table of all dates:
> tblDates
[quoted text clipped - 30 lines]
> >> >
> >> > Any ideas?

Reply to this Message

Duane Hookom - 05 Dec 2005 19:01 GMT

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]);

Signature

Duane Hookom
MS Access MVP

> Please forgive my lack of knowledge, are you using a SQL statement to
> create
[quoted text clipped - 37 lines]
>> >> >
>> >> > Any ideas?

Reply to this Message

Scooter - 05 Dec 2005 19:28 GMT

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.

> This is a totals query. I noticed now that I missed a "]" following Thedate.
> The SQL view of the query should be:
[quoted text clipped - 45 lines]
> >> >> >
> >> >> > Any ideas?

Reply to this Message

Duane Hookom - 05 Dec 2005 23:09 GMT

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.

Signature

Duane Hookom
MS Access MVP
--

>I appreciate your patience with me.
>
[quoted text clipped - 62 lines]
>> >> >> >
>> >> >> > Any ideas?

Reply to this Message

Scooter - 06 Dec 2005 15:01 GMT

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.

> 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
[quoted text clipped - 69 lines]
> >> >> >> >
> >> >> >> > Any ideas?

Reply to this Message

Duane Hookom - 06 Dec 2005 15:32 GMT

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]);

Signature

Duane Hookom
MS Access MVP
--

>I apologize.
>
[quoted text clipped - 101 lines]
>> >> >> >> >
>> >> >> >> > Any ideas?

Reply to this Message