finding specific percentages (1 Viewer)

[cvaccess]

Hello,

I am trying to run a query that pulls specific percentages calculated in the same query. When I try to query anything above 1 percent it does not pull the right data. Below is the query. Also, the appended table is set as text ( I have tried different settings but this one seems to work better). Please help.

INSERT INTO PendPercentTable ( TP, [Date], [Pended Total], [Accepted Total], [Pend Percentage] )
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])*100) 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, indiv_tp_sub.claim_sub
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;

Thank you.=)

 

[RV]

A few remarks.

Why do you want to store calculated data in a table, normally you shouldn't.

Don't use Date for a column name as it is a reserved word in Access.
Don't use spaces in column names.

>Also, the appended table is set as text <

What do you mean? Are you talking 'bout column set as datatype text? If so, which columns?
Anyway, you can't do calculations on text columns.

Before going into detail, be sure there's a really good reason to store calculated data, otherwise don't.

I suspect your INSERT statement should be rebuild to an SELECT statement.

Don't hesitate to ask questions, that's what this forum's all about.

RV

 

[cvaccess]

RV,

Thanks for the feedback. I was told to use append query since the query I had was to complex to output the percentage properly? Is it possible to modify the query to show percentage appropriately?

Thx.=)

 

[RV]

You'll have to answer me former questions first to help you out.

Anyway, I suspect you're not using datatype Numeric for your columns you use to calculate percentages.
If so, change the datatype.

Another remark, I suspect you database structure is not normalized looking at your JOIN condition....

RV

 

[cvaccess]

Here are my responses and changes,including additional questions.

Why do you want to store calculated data in a table, normally you shouldn't.

I was told to by someone at my organization that supposedly knows Access inside and out. I would like to take it out but then I can't get the percentage to show right.

Don't use Date for a column name as it is a reserved word in Access.

I changed this. Thank you.

Don't use spaces in column names.

I changed this to underscores.

>Also, the appended table is set as text <

What do you mean? Are you talking 'bout column set as datatype text? If so, which columns?
Anyway, you can't do calculations on text columns.

I changed this to numeric decimal percentage format.

Regardless, I would like to take off the append to table but how could I change my select query to show my percentage I want. What I mean, the prompt for a specific percentage does not look how I want it to look. It shows 1.33333333333, .3333333, 100. I want it to be in this format 1.33%,.33%,100%. I hope this makes it clear.

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])*100) 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
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;


I appreciate any help you can give me or anyone else can.

 

[cvaccess]

I got this fixed. I just changed the format on that criteria. Thanks for your help. RV