sum function counting field name I think...

[sfgiantsdude]

Hi,

I have a query where one field is a calculated field dividing one field summed by another field summed. But when looking at the data in excel, I figured out that one of the fields was adding a fraction onto the number when I summed up the whole column including the field name leading me to believe that Access was doing this as well. Has anyone encountered something similar to this?

[pbaldy]

Access should not include the field name when summing data. Certainly if there are fractions in the data it will sum that up, unless told otherwise. What are you seeing that makes you think it's summing the field name?

Go Giants!

[sfgiantsdude]

I don't know if you can see the attached graphic but the sum of the numerator is 2 in the raw data and the sum of the denominator is also 2. However, after I run the query, the percentage gives me 2/3 for the specific geographic area I am running the query by.

Any suggestions?

[pbaldy]

Can you post the db here, or a representative sample? My gut tells me it's in the data. Hard to tell without playing with it.

[sfgiantsdude]

I can't actually because the data deals with healthcare records which are HIPAA protected. I don't think it's the data because this issue has been coming up in other division equations as well. My hunch is that it is counting the field name as a value or something that is being affected by the table design. Any suggestions based on that hunch?

Thanks a bunch,
Pierre

[pbaldy]

I can't think of any way it would include the field name. In Excel, you're summing a range that could include the row with the title. Access doesn't work that way. Is there any way the data is actually 1.6 and 2.4 or something, and you're just seeing 2 for both? If you set those to Sum() functions in their own fields, they both return 2? We certainly don't want any sensitive data posted, but can a sanitized table be posted along with the query producing the error?

[sfgiantsdude]

I'm having trouble uploading the excel file with the raw data in it. How would you suggest doing this?

Here's the SQL code for two queries used to get the percentage:

SELECT REPORTING_ENTITIES.CLUSTER, REPORTING_ENTITIES.REPORTING_MCA, Extract_QA1.Provider_Group_Name, Extract_QA1.Sub_Region_Name, Extract_QA1.Screen_Date, Extract_QA1.Case_ID, Extract_QA1.Mbr_Hi_Cat_CD, Extract_QA1.Total_In_Compliance, Extract_QA1.Total_QA_Actions
FROM REPORTING_ENTITIES INNER JOIN Extract_QA1 ON REPORTING_ENTITIES.USER_GROUP = Extract_QA1.Provider_Group_Name;


SELECT qryQAR.Mbr_Hi_Cat_CD, qryQAR.REPORTING_MCA, Sum([qryQAR.Total_In_Compliance])/Sum([qryQAR.Total_QA_Actions]) AS YR_QAR_Rate
FROM qryQAR
WHERE (((qryQAR.Screen_Date) Between [FORMS]![FRMPARAMETERS]![YEAR_FROM] And [FORMS]![FRMPARAMETERS]![YEAR_TO]))
GROUP BY qryQAR.Mbr_Hi_Cat_CD, qryQAR.REPORTING_MCA;

[pbaldy]

In the "Go Advanced" area should be a "Manage Attachments" button that will let you attach the file here. You may need to zip it first.

[sfgiantsdude]

I think that worked. Let me know if you got it or not.

Thanks again.

[pbaldy]

Got it. Are sheet 1 and 2 the two tables in the SQL? They don't appear to be, as neither has a field named USER_GROUP.

[sfgiantsdude]

Use this file. It contains the table that is labeled Extract_QA1. The field "provider_group_name" in Extract_QA1 links to the field "user_group" in a separate table but that linkage is not contributing to the problem.

[sfgiantsdude]

As an example, the "San Diego" value in the "provider_group_name" field should give me 100 percent but the second query doing the division gives me a result of 66.7%

[pbaldy]

What is the SQL or criteria that would reproduce your problem? I've got this:

SELECT Provider_Group_Name, Sum(Total_In_Compliance) AS SumOfTotal_In_Compliance, Sum(Total_QA_Actions) AS SumOfTotal_QA_Actions, Sum([Total_In_Compliance])/Sum([Total_QA_Actions]) AS YR_QAR_Rate
FROM TestTest
GROUP BY Provider_Group_Name;

For let's say Bakersfield, each of the sums is 2 and the rate is 1, which is correct. At a glance, I don't see any records that look out of line.

[sfgiantsdude]

When I run the second query that you have above, these are the results. Notice that San Diego Med Ctr, which links to the San Diego in the Extract_QA1 table I attached has a percentage of 66.7%. There are two records for San Diego in the raw data Extract_QA1 table and the rate 1 which should give me 100 percent.

Mbr_Hi_Cat_CD	REPORTING_MCA	YR_QAR_Rate
Medicare Process	FONTANA MED CTR	1
Medicare Process	INLAND EMPIRE MCRC	0.954314720812183
Medicare Process	PASADENA MCRC	0.956349206349206
Medicare Process	PLEASANTON MCRC	0.99288256227758
Medicare Process	ROSEVILLE MCRC	1
Medicare Process	SAN DIEGO MCRC	1
Medicare Process	SAN DIEGO MED CTR	0.666666666666667
Medicare Process	WALNUT CREEK MED CTR	1
Non-Medicare Process	BAKERSFIELD MED CTR	1
Non-Medicare Process	FONTANA MED CTR	1
Non-Medicare Process	INLAND EMPIRE MCRC	0.959287531806616
Non-Medicare Process	PASADENA MCRC	0.972665148063781
Non-Medicare Process	PLEASANTON MCRC	0.957303370786517
Non-Medicare Process	ROSEVILLE MCRC	0.992805755395684
Non-Medicare Process	SACRAMENTO MED CTR	1
Non-Medicare Process	SAN DIEGO MCRC	1
Non-Medicare Process	SAN DIEGO MED CTR	0.9
Non-Medicare Process	SO. SAN FRANCISCO MED CTR	1
Non-Medicare Process	SOUTH BAY MED CTR	1
Non-Medicare Process	STOCKTON MED CTR	1

[pbaldy]

Are you sure something isn't going wrong in the join? I appear to get the right number, see attached (I don't appear to have the same data). Also, if you also return the two individual sums separately like I've done, do the results appear correct?