K
knowshowrosegrows
I love this form - I can't wait to get to work every day so I can use this
form to teach another of my users to write their own reports without my doing
it. FANTASTIC FORM
I am working with the following crosstab query SQL
TRANSFORM Count([<qryMonthlyThreeMonths>].IncidentID) AS CountOfIncidentID
SELECT [<qryMonthlyThreeMonths>].Agency,
[<qryMonthlyThreeMonths>].MonitoringRegion,
[<qryMonthlyThreeMonths>].InitialIncidentCategory,
[<qryMonthlyThreeMonths>].InitialIncidentSubCategory,
Count([<qryMonthlyThreeMonths>].IncidentID) AS [Total Of IncidentID]
FROM [<qryMonthlyThreeMonths>]
WHERE ((([<qryMonthlyThreeMonths>].IncidentDate) Between
DateAdd("yyyy",-20,Date()) And Date()))
GROUP BY [<qryMonthlyThreeMonths>].Agency,
[<qryMonthlyThreeMonths>].MonitoringRegion,
[<qryMonthlyThreeMonths>].InitialIncidentCategory,
[<qryMonthlyThreeMonths>].InitialIncidentSubCategory
PIVOT Format([IncidentDate],"yyyy mm") & "(This month - " &
DateDiff("m",[IncidentDate],Date()) & ")";
I use this to grab specific months of data from the last 20 months. In the
form I can designate which months I want and go.
The trouble is that I get all the groupings of data even if there is no data
for that group in the specific months that I asked for in the form.
So, somewhere in the 20 months there is a month that had one record of
[<qryMonthlyThreeMonths>].Agency, =
A[<qryMonthlyThreeMonths>].MonitoringRegion, =
B[<qryMonthlyThreeMonths>].InitialIncidentCategory, = C
and[<qryMonthlyThreeMonths>].InitialIncidentSubCategory = D
That month is not one the months I am asking for in the form.
I still get that record but when I move across and look in my 4 months there
is no data.
Is there a way to tell the form or the query that I only want records that
have data in one of my designated four (or six or eight) months?
I hope this is clear.
form to teach another of my users to write their own reports without my doing
it. FANTASTIC FORM
I am working with the following crosstab query SQL
TRANSFORM Count([<qryMonthlyThreeMonths>].IncidentID) AS CountOfIncidentID
SELECT [<qryMonthlyThreeMonths>].Agency,
[<qryMonthlyThreeMonths>].MonitoringRegion,
[<qryMonthlyThreeMonths>].InitialIncidentCategory,
[<qryMonthlyThreeMonths>].InitialIncidentSubCategory,
Count([<qryMonthlyThreeMonths>].IncidentID) AS [Total Of IncidentID]
FROM [<qryMonthlyThreeMonths>]
WHERE ((([<qryMonthlyThreeMonths>].IncidentDate) Between
DateAdd("yyyy",-20,Date()) And Date()))
GROUP BY [<qryMonthlyThreeMonths>].Agency,
[<qryMonthlyThreeMonths>].MonitoringRegion,
[<qryMonthlyThreeMonths>].InitialIncidentCategory,
[<qryMonthlyThreeMonths>].InitialIncidentSubCategory
PIVOT Format([IncidentDate],"yyyy mm") & "(This month - " &
DateDiff("m",[IncidentDate],Date()) & ")";
I use this to grab specific months of data from the last 20 months. In the
form I can designate which months I want and go.
The trouble is that I get all the groupings of data even if there is no data
for that group in the specific months that I asked for in the form.
So, somewhere in the 20 months there is a month that had one record of
[<qryMonthlyThreeMonths>].Agency, =
A[<qryMonthlyThreeMonths>].MonitoringRegion, =
B[<qryMonthlyThreeMonths>].InitialIncidentCategory, = C
and[<qryMonthlyThreeMonths>].InitialIncidentSubCategory = D
That month is not one the months I am asking for in the form.
I still get that record but when I move across and look in my 4 months there
is no data.
Is there a way to tell the form or the query that I only want records that
have data in one of my designated four (or six or eight) months?
I hope this is clear.