Query Help

  • Thread starter Thread starter Jay Jones
  • Start date Start date
J

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
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?
 
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>
 
Back
Top