New to Access and in a pinch for a report

[mike17316]

Hello, I need to create a report in Access 2003. Basically by Event type
(Call Support, Work Request) Priority (Crit,High,Med,Low) and counts based on
status, either open, closed, cancelled etc.

I was able to get one type of Status to work based on a query
SELECT Event.[Event Type], Event.Priority, Count(Event.Status) AS CountOfOpen
FROM Event
WHERE (((Event.Status)="Open"))
GROUP BY Event.[Event Type], Event.Priority;

How can I get the other columns for closed, etc. When I tried, it seemed to
add it as and "AND" condition, but I need sperate results columns in the same
report.

ALSO>> I need to be able to prompt the user for a begin and end dates.

Thanks so much in advance!!
Mike17316 @ yahoo.com

 

[Rob Parker]

Hi Mike,

You'll need to work with calculated fields to get all the Status conditions
into a single query. Try this:

SELECT Event.[Event Type], Event.Priority,
Sum(IIf([Event].[Status]="Open",1,0)) AS CountOpen,
Sum(IIf([Event].[Status]="Closed",1,0)) AS CountClosed,
Sum(IIf([Event].[Status]="Cancelled",1,0)) AS CountCancelled
FROM Event
GROUP BY Event.[Event Type], Event.Priority;

If you want to prompt for a range of dates on a single date field, you can
use:

SELECT Event.[Event Type], Event.Priority,
Sum(IIf([Event].[Status]="Open",1,0)) AS CountOpen,
Sum(IIf([Event].[Status]="Closed",1,0)) AS CountClosed,
Sum(IIf([Event].[Status]="Cancelled",1,0)) AS CountCancelled
FROM Event
WHERE (((Event.EventDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY Event.[Event Type], Event.Priority;

If you've got separate StartDate and EndDate fields you'll need to split
this into criteria on the two fields. Also, be aware that Between may give
odd results if you've got a time component in your DateTime fields; search
this group (and the microsoft.public.access.queries group) and you'll find
lots of discussions on the topic.

HTH,

Rob

 

[mike17316]

Thanks so much for taking the time to help me out on the report question.
The data returned from the query looks great.

If you would be able to answer an additional question on the same report, it
would be a HUGE help. I just started here Monday and they stuck me with this
task, of which I know nothing about MS Access. I know DB2 SQL, and I'm
working through the rest The report is due next Monday.

I have several columns on my report, including the open and closed (they
removed cancelled)
both those rely on the StartDate / EndDate range, these are correct as in
your answer.

I have a StartCount, which is basically all open records PRIOR to (<) the
StartDate

?>> Is it possible to do both in the same query?

I also have 2 other calculated Columns on the report, NetChng which is
CountOpen minus CountClosed, as well as EndCount which is StartCount plus
NetChng

?>> Can I do this in the same query?


Again, many thanks!!

 

[Rob Parker]

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

 

[mike17316]

Thank you Rob.

Worked like a charm!