Sum Problem (1 Viewer)

[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.

 

[Pat Hartman]

The calculation is not wrong. The problem is that you are trying to summarize two levels of detail with the same query and you can't do that. You are trying to sum data from pend_summary and [INDIV_TP_SUB]. To do the calculations properly, you'll need three queries or two queries and a report.

Query1:
Sum pend_summary data
Query2:
Sum [INDIV_TP_SUB] data
Query3:
Join query1 and query2 to calculate average