Count Records By Quarter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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"
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top