sum problem

[cvaccess]

I have two tables indiv_tp_sub and pend_summary. The fields on indiv_tp_sub are:scrub_date,child_tp,claim_sub. The fields on pend_summary are:tp_num,total,scrub_date. Scrub_date are the same on both tables. Also, tp_num on pend_summary and child_tp on indiv_tp_sub are the same.
The below query specifies a specific date period and specific TP. So for tp QC5 the sum of claim_sub is double of what it should be. The table shows 96 but the query outputs 192. It seems like it multiplies based on the amount of records it finds in pend_summary. I noticed this when I removed one of the records and it came out to 96 instead of 192. Because the table has multiple records, I need to be able to have it sum properly. Why is this calculating wrong?

SELECT DISTINCTROW pend_summary.TP_NUM AS TP, pend_summary.SCRUB_DATE AS ScrubDate, Sum(pend_summary.TOTAL) AS Pended, Sum(INDIV_TP_SUB.CLAIM_SUB) AS Accept, (Sum([pend_summary].[total])/Sum([INDIV_TP_SUB].[CLAIM_SUB])) AS [Pend %]
FROM pend_summary INNER JOIN INDIV_TP_SUB ON (pend_summary.TP_NUM = INDIV_TP_SUB.CHILD_TP) AND (pend_summary.SCRUB_DATE = INDIV_TP_SUB.SCRUB_DATE)
WHERE (((pend_summary.SCRUB_DATE) Between [Enter Starting Create Date:] And [Enter Ending Create Date:]) AND ((pend_summary.TP_NUM)=[INDIV_TP_SUB].[CHILD_tp] And (INDIV_TP_SUB.CHILD_TP)=[Enter Trading Partner Number:]))
GROUP BY pend_summary.TP_NUM, pend_summary.SCRUB_DATE
HAVING ((((((Sum([pend_summary].[total])/Sum([INDIV_TP_SUB].[CLAIM_SUB]))*100)>[Enter Pend % as a whole number]))<>0))
ORDER BY pend_summary.TP_NUM, (Sum([pend_summary].[total])/Sum([INDIV_TP_SUB].[CLAIM_SUB])*100) DESC;

Thank you for your help.

 

[r937]

best way to debug this type of query is to select a very specific key from the parent table and instead of doing the GROUP BY, just display the detail rows

the reason you have double the amount expected is probably exactly what you suspected -- &quot;it seems like it multiplies based on the amount of records it finds in pend_summary&quot;

you need to tighten up the code a bit, too

one of the join conditions in the ON clause is repeated in the WHERE clause, and although this will not produce erroneous results, you normally have to be a lot more careful when coding sql

in addition, your HAVING clause is really weird

either you pasted it here into your post incorrectly, or else there's a couple of extra parentheses in there

HAVING ((
         ((( (Sum([pend_summary].[total])
            / Sum([INDIV_TP_SUB].[CLAIM_SUB]
          ))*100
         )   > [Enter Pend % as a whole number]
        )) <> 0
    ))

plus, it seems to be testing X > Y <> 0 which looks like it's testing X > Y to be false, which isn't the normal way of doing it

rudy

http://rudy.ca/