Duane Hookom Query By Form Question

  • Thread starter knowshowrosegrows
  • Start date
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.
 

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