Count Records By Quarter

A

AuditorCMM

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
D

Duane Hookom

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"
 
A

AuditorCMM

Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

Duane Hookom said:
It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
D

Duane Hookom

You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

Duane Hookom said:
It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
A

AuditorCMM

Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

Duane Hookom said:
You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

Duane Hookom said:
It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
D

Duane Hookom

Try:
"Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q")

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

Duane Hookom said:
You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

:

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
A

AuditorCMM

Thanks! That worked great. Now, how do I go about setting the criteria to
limit the query to only show records with dates from 7/1/06 - 6/30/07? I
can't get anything I try to work!

Duane Hookom said:
Try:
"Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q")

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

Duane Hookom said:
You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

:

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
D

Duane Hookom

You can add a criteria in you crosstab like
WHERE [Date Of Incident] BETWEEN #7/1/06# AND #6/30/07#

I would anticipate you want this to be dynamic either based on values
entered into controls on a form or values based on the current date. I would
use controls on a form and use something like:
WHERE [Date Of Incident] BETWEEN Forms!frmDates!txtStart AND
Forms!frmDates!txtEnd
Then you will need to select Query->Parameters and enter
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Thanks! That worked great. Now, how do I go about setting the criteria to
limit the query to only show records with dates from 7/1/06 - 6/30/07? I
can't get anything I try to work!

Duane Hookom said:
Try:
"Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q")

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

:

You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

:

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
A

AuditorCMM

Where exactly do I go to add that criteria? When I open the query in design
mode, I have 4 columns (Row Header, Column Header, Count, Total Count). I
tried typing that formula in the Criteria box for a few of them, but each
time I ended up with no records in my query.

Duane Hookom said:
You can add a criteria in you crosstab like
WHERE [Date Of Incident] BETWEEN #7/1/06# AND #6/30/07#

I would anticipate you want this to be dynamic either based on values
entered into controls on a form or values based on the current date. I would
use controls on a form and use something like:
WHERE [Date Of Incident] BETWEEN Forms!frmDates!txtStart AND
Forms!frmDates!txtEnd
Then you will need to select Query->Parameters and enter
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Thanks! That worked great. Now, how do I go about setting the criteria to
limit the query to only show records with dates from 7/1/06 - 6/30/07? I
can't get anything I try to work!

Duane Hookom said:
Try:
"Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q")

--
Duane Hookom
Microsoft Access MVP


:

Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

:

You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

:

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
D

Duane Hookom

You should reply back with your sql view. Otherwise, just add your date field
to the grid and enter the criteria. Your grid column might look like:

Field: [Date Of Incident]
Total: Where
Crosstab:
Criteria: BETWEEN #7/1/06# AND #6/30/07#

If you reference controls on a form, you must enter the parameter data types
as I suggested previously.

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Where exactly do I go to add that criteria? When I open the query in design
mode, I have 4 columns (Row Header, Column Header, Count, Total Count). I
tried typing that formula in the Criteria box for a few of them, but each
time I ended up with no records in my query.

Duane Hookom said:
You can add a criteria in you crosstab like
WHERE [Date Of Incident] BETWEEN #7/1/06# AND #6/30/07#

I would anticipate you want this to be dynamic either based on values
entered into controls on a form or values based on the current date. I would
use controls on a form and use something like:
WHERE [Date Of Incident] BETWEEN Forms!frmDates!txtStart AND
Forms!frmDates!txtEnd
Then you will need to select Query->Parameters and enter
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Thanks! That worked great. Now, how do I go about setting the criteria to
limit the query to only show records with dates from 7/1/06 - 6/30/07? I
can't get anything I try to work!

:

Try:
"Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q")

--
Duane Hookom
Microsoft Access MVP


:

Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

:

You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

:

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 
A

AuditorCMM

Great! Thank you so much!! (Sorry about the confusion. I was making it a
lot harder than it needed to be!)

Duane Hookom said:
You should reply back with your sql view. Otherwise, just add your date field
to the grid and enter the criteria. Your grid column might look like:

Field: [Date Of Incident]
Total: Where
Crosstab:
Criteria: BETWEEN #7/1/06# AND #6/30/07#

If you reference controls on a form, you must enter the parameter data types
as I suggested previously.

--
Duane Hookom
Microsoft Access MVP


AuditorCMM said:
Where exactly do I go to add that criteria? When I open the query in design
mode, I have 4 columns (Row Header, Column Header, Count, Total Count). I
tried typing that formula in the Criteria box for a few of them, but each
time I ended up with no records in my query.

Duane Hookom said:
You can add a criteria in you crosstab like
WHERE [Date Of Incident] BETWEEN #7/1/06# AND #6/30/07#

I would anticipate you want this to be dynamic either based on values
entered into controls on a form or values based on the current date. I would
use controls on a form and use something like:
WHERE [Date Of Incident] BETWEEN Forms!frmDates!txtStart AND
Forms!frmDates!txtEnd
Then you will need to select Query->Parameters and enter
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
Microsoft Access MVP


:

Thanks! That worked great. Now, how do I go about setting the criteria to
limit the query to only show records with dates from 7/1/06 - 6/30/07? I
can't get anything I try to work!

:

Try:
"Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q")

--
Duane Hookom
Microsoft Access MVP


:

Sorry to bother you again, but I can't quite get this to work. My original
quarter calculation wasn't shown quite like your example. It is displayed
like this:

"Qtr " & Format([Date of Incident],"q")

I tried adding in the piece you described below, but I keep getting errors.
Here's one of the formulas I tried:

"Qtr " & Format([Date of Incident],("q",DateAdd("m",6,[Date of Incident])))

Could you please let me know what I'm missing? Thanks!

:

You should be able to simply add 6 months to the date within the quarter
calculation
"Q" & DatePart("q",DateAdd("m",6,[Date]))
You can set the criteria in the query to limit the date range returned.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane. That concept worked great, although it leads me to another
question.

Our organization does not follow the calendar year. Our fiscal year runs
from July 1 - June 30. Is there any way I can modify the query so that it
groups by our quarters (i.e. 1st quarter: July-Sept)? Also, I'd like to have
it only show the current fiscal year, not every date regardless of year that
falls in the repective months.

Thanks so much!

:

It looks like you need to create a crosstab query with Type Of Complaint as
the Row Heading, "Q" & DatePart("q",[Date]) as the Column Heading, and Count
of Date as the Value.

You can set the Column Headings property to "Q1","Q2","Q3","Q4"

--
Duane Hookom
Microsoft Access MVP


:

I currently have a database of employee complaints that has fields similar to
the following:

Date Type of Complaint Action Taken

I need to set up a report that lists the types of complaints down the left
hand side and then shows the number of those complaints by quarter. For
example:

Quarter1 Q2 Q3
Q4
Human Resources 1 1 0
2
Payroll 2 0 0
1
Compliance etc.
Policy Violation
etc.

I'm assuming I would have to set up a query 1st that somehow summarizes the
count for each quarter, but I'm really having a hard time getting started.
Any suggestions? Thanks. I really appreciate the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top