find specific percentage query

[cvaccess]

This is really starting to irritate me.=) I am trying to create a query that pulls specific dates and a specific percentage for the totals. When I try to specify a percentage, for example, I want 6% and above to show only, I get output with % under 6 and a few over 6% but not all the records over 6%. Why doesn't it recognize the criteria right? The format property is set to percent. 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 Submitted, (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]))
GROUP BY pend_summary.TP_NUM, pend_summary.SCRUB_DATE
HAVING ((((((Sum([pend_summary].[total])/Sum([indiv_tp_sub].[claim_sub])))>=[Enter Pend % as a whole number])*100)<>False))
ORDER BY pend_summary.TP_NUM, (Sum([pend_summary].[total])/Sum([indiv_tp_sub].[claim_sub])*100) DESC;

Any help is appreciated.

Thanks.=)

 

[r937]

oh, man, is that ever hard to read

i think it's the <>FALSE in your HAVING that's screwing it up

not to mention, i think there are unmatched parentheses in there, and i think the *100 belongs in the numerator

try replacing your HAVING clause with this one --

HAVING Sum([pend_summary].[total]) *100
     / Sum([indiv_tp_sub].[claim_sub])
     >=[Enter Pend % as a whole number]

rudy

http://rudy.ca/

 

[cvaccess]

Well, I ended up breaking out the queries and using IIF to get my percentage. Thanks for the help.