Working with Dates

[AuditorCMM]

I currently have a crosstab query that I used to count the number of each
type of employee compliant and summarize by quarter. For example:
Q1 Q2 Q3 Q4
Human Resources 1 0 2 1
Payroll 1 1 1 0
Privacy ...etc

2 Questions - First of all, is there a way that I can set a criteria so that
the query only counts those within a specific date range (i.e. 7/1/06 -
6/30/07). Second, is there a way I can set up a new query to count by
specific quarters over more than one year. For example
FY07-Q1 FY06-Q1 FY07-Q2 FY06-Q2
....etc
Human Resources 1 0 2 1
Payroll 1 1 1 0
Privacy ...etc

Any assistance would be appreciated! Thanks so much!

 

[John Spencer]

Please copy and post the SQL of your query. There should be a way to do
what you want, but it is harder to describe than do.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

===================================
Limit records to a date range
If you are using the query grid (QBE) to build your query
-- Add the date field to the query (again)
-- Replace "Group By" with "Where"
-- Enter
Between #7/1/06# and #6/30/07#
in the criteria

==================================
Get multiple quarters for multiple years

You need to modify the calculation you are using for column heading (the
Pivot clause)
Also you need to tell us what your fiscal years are. The following does
calendar year

Field: Format([YourDateField]\F\YYY\-\Qq")

The following may work for you if your FY starts on July 1

Format(DateAdd("m",[YourDateField]),"\F\YYY\-\Qq")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[AuditorCMM]

Here's my SQL:

TRANSFORM Count([Hotline Log].ID) AS CountOfID
SELECT [Hotline Log].[Action Taken], Count([Hotline Log].ID) AS [Total Of ID]
FROM [Hotline Log]
WHERE ((([Hotline Log].[Date of Incident]) Between #7/1/2006# And
#6/30/2007#))
GROUP BY [Hotline Log].[Action Taken]
PIVOT "Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q");

Please copy and post the SQL of your query. There should be a way to do
what you want, but it is harder to describe than do.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

===================================
Limit records to a date range
If you are using the query grid (QBE) to build your query
-- Add the date field to the query (again)
-- Replace "Group By" with "Where"
-- Enter
Between #7/1/06# and #6/30/07#
in the criteria

==================================
Get multiple quarters for multiple years

You need to modify the calculation you are using for column heading (the
Pivot clause)
Also you need to tell us what your fiscal years are. The following does
calendar year

Field: Format([YourDateField]\F\YYY\-\Qq")

The following may work for you if your FY starts on July 1

Format(DateAdd("m",[YourDateField]),"\F\YYY\-\Qq")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I currently have a crosstab query that I used to count the number of each
type of employee compliant and summarize by quarter. For example:
Q1 Q2 Q3 Q4
Human Resources 1 0 2 1
Payroll 1 1 1 0
Privacy ...etc

2 Questions - First of all, is there a way that I can set a criteria so
that
the query only counts those within a specific date range (i.e. 7/1/06 -
6/30/07). Second, is there a way I can set up a new query to count by
specific quarters over more than one year. For example
FY07-Q1 FY06-Q1 FY07-Q2 FY06-Q2
...etc
Human Resources 1 0 2 1
Payroll 1 1 1
0
Privacy ...etc

Any assistance would be appreciated! Thanks so much!

 

[John Spencer]

Looking back at my earlier posting I note that I missed adding the 6 in the
expression
Format(DateAdd("m",[YourDateField]),"\F\YYY\-\Qq")
That should have read
Format(DateAdd("m",6,[YourDateField]),"\F\YYY\-\Qq")

For today's date (March 27, 2007) that would return FY07-Qtr3

Applying that to your query would give

TRANSFORM Count([Hotline Log].ID) AS CountOfID
SELECT [Hotline Log].[Action Taken]
, Count([Hotline Log].ID) AS [Total Of ID]
FROM [Hotline Log]
WHERE ((([Hotline Log].[Date of Incident]) Between #7/1/2006# And
#6/30/2007#))
GROUP BY [Hotline Log].[Action Taken]
PIVOT Format(DateAdd("m",6,[Date of Incident]),"F\YYY\-\Qtrq")


If you don't understand the formatting, look it up in the help. The "\" is
used to tell the format function that the next character is to be printed as
it is and not to use it to format the date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Here's my SQL:

TRANSFORM Count([Hotline Log].ID) AS CountOfID
SELECT [Hotline Log].[Action Taken], Count([Hotline Log].ID) AS [Total Of
ID]
FROM [Hotline Log]
WHERE ((([Hotline Log].[Date of Incident]) Between #7/1/2006# And
#6/30/2007#))
GROUP BY [Hotline Log].[Action Taken]
PIVOT "Qtr " & Format(DateAdd("m",6,[Date of Incident]),"q");

Please copy and post the SQL of your query. There should be a way to do
what you want, but it is harder to describe than do.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

===================================
Limit records to a date range
If you are using the query grid (QBE) to build your query
-- Add the date field to the query (again)
-- Replace "Group By" with "Where"
-- Enter
Between #7/1/06# and #6/30/07#
in the criteria

==================================
Get multiple quarters for multiple years

You need to modify the calculation you are using for column heading (the
Pivot clause)
Also you need to tell us what your fiscal years are. The following does
calendar year

Field: Format([YourDateField]\F\YYY\-\Qq")

The following may work for you if your FY starts on July 1

Format(DateAdd("m",[YourDateField]),"\F\YYY\-\Qq")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I currently have a crosstab query that I used to count the number of
each
type of employee compliant and summarize by quarter. For example:
Q1 Q2 Q3 Q4
Human Resources 1 0 2 1
Payroll 1 1 1
0
Privacy ...etc

2 Questions - First of all, is there a way that I can set a criteria so
that
the query only counts those within a specific date range (i.e. 7/1/06 -
6/30/07). Second, is there a way I can set up a new query to count by
specific quarters over more than one year. For example
FY07-Q1 FY06-Q1 FY07-Q2 FY06-Q2
...etc
Human Resources 1 0 2 1
Payroll 1 1 1
0
Privacy ...etc

Any assistance would be appreciated! Thanks so much!