Hi Mike,
It's getting trickier ...
I think that the easiest way of getting StartCount into your query will be
to use a dCount function; trying to do this with (perhaps) a sub-query to
limit records in one field to have different criteria than records in all
other fields is beyond me - maybe there's an MVP out there who could do it.
Using domain aggregate functions in queries will slow things down - the
function must be evaluated for every row returned. However, in this case,
you probably don't have a huge number of event types, and only a few
priorities, so your totals query is probably not returning a huge number of
rows and the performance should be OK.
Before getting to the query SQL, a couple of other points. First, if you
declare the parameters for the query, you will be able to refer to them in a
report based on the query; so, for example, you could include the start and
end dates in the report's header in textbox controls with control sources of
[Enter Start Date] and [Enter End Date].
Next, if you have separate StartDate and EndDate fields in your table (I'm
reading that as being the case from your latest post), and (as seems
likely/logical to me) your open records do not have an enddate, then you
will need to also use a criteria of Is Null to get those records into your
dataset.
Together, and removing the Cancelled status field, these points give a query
as follows:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Event.[Event Type], Event.Priority,
Sum(IIf([Event].[Status]="Open",1,0))
AS CountOpen, Sum(IIf([Event].[Status]="Closed",1,0)) AS CountClosed,
DCount("*","[Event]","[Event Type] = '" & [Event Type] & "' AND [Priority]
= '" & [Priority] & "' AND [StartDate] < #" & [Enter Start Date] & "#") AS
StartOpen
FROM Event
WHERE (((Event.StartDate)>=[Enter Start Date]) AND ((Event.EndDate)<=[Enter
End Date])) OR (((Event.StartDate)>=[Enter Start Date]) AND ((Event.EndDate)
Is Null))
GROUP BY Event.[Event Type], Event.Priority;
As for the NetChange and EndCount values, the easiest way to get these is to
use unbound textbox controls on your report, with their control sources
being the expressions that you need. Note also that, since these
expressions refer to fields in the query, you cannot have textbox controls
in your report with the same name as any on those fields; if you create your
report using the wizard, or by dragging/dropping fields from the Field List
box, then the bound controls for those fields will have the same name as the
fields - you should rename them to txtCountOpen, txtCountClosed,
txtStartOpen, etc.
Add an unbound textbox, txtNetChange, and set its ControlSource to the
expression:
=[CountOpen]-[CountClosed]
Add an unbound text, txtEndCount, and set its ControlSource to the
expression:
=[StartOpen]+[txtNetChange]
(Note: this worked for me, but if it gives an error, you might need to
change this to:
=[StartOpen]+([CountOpen]-[CountClosed])
And voila ...
BTW, if you must have the NetChange and EndCount fields in your query, you
can do so with calculated fields within the query, BUT you must use the
underlying expressions for CountOpen, CountClosed, and StartOpen in the
expressions for those calculated fields; you cannot refer to [CountOpen],
[CountClosed] or [StartOpen].
Again, HTH,
Rob