Counting days in a crosstab query

[Guest]

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?

 

[Duane Hookom]

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.

 

[Guest]

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]

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

 

[Guest]

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
===========
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
--

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]

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

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
===========
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
--

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#).

I'm not sure what any of this has to do with a crosstab query.

--
Duane Hookom
MS Access MVP
--

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?

 

[Guest]

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:

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

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
===========
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
--

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#).

I'm not sure what any of this has to do with a crosstab query.

--
Duane Hookom
MS Access MVP
--

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?

 

[Duane Hookom]

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

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:

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

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
===========
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
--

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#).

I'm not sure what any of this has to do with a crosstab query.

--
Duane Hookom
MS Access MVP
--

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?

 

[Guest]

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

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:

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


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
===========
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
--

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#).

I'm not sure what any of this has to do with a crosstab query.

--
Duane Hookom
MS Access MVP
--

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?

 

[Duane Hookom]

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

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

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:

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


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
===========
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
--

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#).

I'm not sure what any of this has to do with a crosstab query.

--
Duane Hookom
MS Access MVP
--

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?