rewrite access query

[oconshaw]

Hi, I'm new to this forum, but thought I'd see if you guys have some suggestions for improving and/or doing this query in a different way. The following query works in Access, but my gut tells me there must be a simpler way. In fact, Access doesn't really like this query when you try to edit the query from the QBE, but it doesn't complain when it is run. For me at least, the challenging parts are the subqueries in this query that allow me to perform my counts and sum functions and attach as two additional columns along with the other results. The goal being to have one query to populate a report using parameters passed to it from a form.

Thanks in advance for your ideas and insight.

Regards,

--Shawn

select

d.name,
count(a.procname) as CountofProcName,
Sum(IIf(b.AssignCredit=True,(-1)*c.ptqty*(c.ProductPrice+c.ServicePrice),c.ptqty*(c.ProductPrice+c.ServicePrice))) as ProcAmount,
b.hid,
a.procname,
c.procid,
b.sid,
TicketCount,
HeartCaseCount

from
procedures a,
ticket b,
procticket c,
hospitalstaff d,
(SELECT Count(z.tid) AS TicketCount, Sum(IIf(z.heart_case=True,1,0)) AS HeartCaseCount
FROM (( SELECT distinct ticket.tid, ticket.heart_case
FROM TICKET
WHERE ticket.tdate between #01/01/2001# and #06/30/2006#
and ticket.sid='sxt') as z)
)

where
a.procid = c.procid and
b.tid = c.tid and
b.sid = d.staffid and
b.tdate between #01/01/2001# and #06/30/2006#
and b.sid='sxt'
group by d.name, a.procname, c.procid, b.sid, b.hid, TicketCount, HeartCaseCount;

 

[lespaul]

how about some sample data and your expected results from that data?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi