sum calculation problem (1 Viewer)

[cvaccess]

I think my problem is the way they are linked but am not sure. My query does not combine the same tp and date totals. For example, Scrub_date is 6/24/02 and tp_num is RHA. This date and tp is listed twice with different totals, 300 and 1. I want it to combine these as one. So it would show one output of this tp and date being:scrub_date 6/24/02, tp_num RHA and total 301. Here is the query:

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.SCRUB_DATE = indiv_tp_sub.SCRUB_DATE) AND (pend_summary.TP_NUM = indiv_tp_sub.CHILD_TP)
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]))
GROUP BY pend_summary.TP_NUM, pend_summary.SCRUB_DATE, indiv_tp_sub.CLAIM_SUB,indiv_tp_sub.CHILD_TP
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;

Anyone, please help me.

Thank you.

 

[Pat Hartman]

Get rid of DISTINCTROW it is preventing the summing and grouping.

 

[cvaccess]

That did not work. Same results. Anyone, suggestions?

Thx

 

[Jon K]

Remove:-

,indiv_tp_sub.CLAIM_SUB,indiv_tp_sub.CHILD_TP

from the Group By clause. The problem is caused by different values in these fields.

 

[cvaccess]

It worked! Thx Jon