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.=)

 

[pdx_man]

I think you may be trying to do too many things in one query. First, have your query that does you calculations, then create another query based on this query that specifies the criteria.

 

[cvaccess]

find specific percentage query

Ah! See I tried that too. Here are the queries:

First one does the date parameters with totals based on the dates.

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
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
ORDER BY pend_summary.TP_NUM;

This second one does the percentage calculation:

SELECT PendPercent_test.TP, PendPercent_test.ScrubDate, PendPercent_test.Pended, PendPercent_test.Submitted, (PendPercent_test.Pended)/(PendPercent_test.Submitted) AS [Pend %]
FROM PendPercent_test
WHERE (((([PendPercent_test].[Pended])/([PendPercent_test].[Submitted]))>=[Enter Pend %:]))
order by (PendPercent_test.Pended)/(PendPercent_test.Submitted);

Please help.=(

Thanks.

 

[pdx_man]

When entering decimal values into parameter box, you must also include the whole number portion, or the preceding zero.

Use 0.4 instead of .4

Access then knows that you are talking about a number and not text.

 

[cvaccess]

find specific percentage query

Is there an easier way so that the user can enter 6 instead of .06 for 6%?

Thanks.

 

[pdx_man]

>IIf([enter Perc]>1,([enter Perc]/100),CDbl([enter Perc]))

the CDbl will allow you not to enter the preceding zero

 

[cvaccess]

parameter

pdx_man, I feel so stupid, I have never used IIf. Where would this fit in my query?

Thanks.

 

[pdx_man]

SELECT PendPercent_test.TP, PendPercent_test.ScrubDate, PendPercent_test.Pended, PendPercent_test.Submitted, (PendPercent_test.Pended)/(PendPercent_test.Submitted) AS [Pend %]
FROM PendPercent_test
WHERE (((([PendPercent_test].[Pended])/([PendPercent_test].[Submitted]))>= IIf([Enter Pend %:]>1,([Enter Pend %:]/100),CDbl([Enter Pend %:])) ))
order by (PendPercent_test.Pended)/(PendPercent_test.Submitted);

 

[cvaccess]

find specific percentage query

It worked!!!!!

Thanks pdx_man.

This forum is great.

 

[pdx_man]

Happy to help. Did you get your other issue worked out?