Query Help

[Jay Jones]

I have tried to do the following several ways with no
luck, any suggestions?

I would like to calculate proportioned revenue for
equipment that is used on jobs(tickets).

I have a table (tblTickets) that contains [Ticket #] and
[Amount].

Another table (tblTicketsTestEQ) that contains a record
for each equipment used on the ticket designated by a
code

.  The table is one(tblTickets)-to-many
(tblTicketsTestEQ) related to tblTickets by [Ticket #].

First I calculate the prorated amount based on
amount/count([Code]) with the following statement saved
as qryAvgRevPerEQ:

SELECT tblTickets.[TICKET #], Avg(tblTickets.AMOUNT)/Count
(tblTicketTestEQ.CODE) AS AvgRevPerEQ, Count
(tblTicketTestEQ.CODE) AS CountOfCODE
FROM tblTickets INNER JOIN tblTicketTestEQ ON tblTickets.
[TICKET #] = tblTicketTestEQ.[TICKET #]
WHERE (((tblTickets.[DATE OUT]) Between #10/9/2002# And
#10/9/2003#))
GROUP BY tblTickets.[TICKET #];

This gives me a list of all tickets and a prorated amount
for that ticket.  This works fine.

Next I want to sum these averages grouped by Code.  By
using:

SELECT tblTicketTestEQ.CODE, Sum
(qryAvgRevPerEQ.AvgRevPerEQ) AS SumOfAvgRevPerEQ
FROM qryAvgRevPerEQ, tblTicketTestEQ
GROUP BY tblTicketTestEQ.CODE;

When this is run I get a division by zero error.

Is there a better way to do this?  Basically I want to
find an average amount for a ticket based on how many
pieces of equipment was used.  Then sum these amounts
grouped by Code.  Please help?

 

[Jeff Boyce]

Jay

If you can believe your error message, your Count(Code) must be 0 for at
least one Ticket#. The one-to-many relationship is actually a
one-to-(zero-to-many).

If you are only interested in those tickets for which more-than-zero records
exist in tblTicketsTestEQ, include that in your WHERE clause, or use an
initial query to "screen" out the zero-count ticket#.

Good luck

Jeff Boyce
<Access MVP>