Re: Working with Dates

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


"John Spencer" wrote:

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" <AuditorCMM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2555E986-97BF-4177-AE81-4FAE53B3B975@xxxxxxxxxxxxxxxx
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!







.

Relevant Pages

  • Re: searching for similar records
    ... "John Spencer" wrote: ... anything at all with criteria or joins on partnumber. ... should only the partnumber and customer be ... Open a new query ...
    (microsoft.public.access.queries)
  • Re: Isolate Patterns
    ... Your script was exactly was I've been looking for... ... "John Spencer" wrote: ... Do you want someone to write the SQL for the query? ... using the like criteria versus using criteria on just the stripped result. ...
    (microsoft.public.access.queries)
  • Re: Query w/Date & Time in Same Field
    ... John Spencer ... Access MVP 2002-2005, 2007 ... and then did you apply the criteria against this calculated field? ... My table I am using with this query has several ...
    (microsoft.public.access.queries)
  • Re: query issues - critera being ignored
    ... UNION query is not the answer either. ... If the USER field ALWAYS has a value and is a text field then your criteria ... "John Spencer" wrote: ... Union queries cannot be built ...
    (microsoft.public.access.queries)
  • Re: searching for similar records
    ... "John Spencer" wrote: ... anything at all with criteria or joins on partnumber. ... should only the partnumber and customer be ... Open a new query ...
    (microsoft.public.access.queries)