IEHP1

asked on

# Summing Calculated Fields Issue

Hi, so I have created a query using derived tables (I am trying to avoid having to create any series of steps to do this).

The problem is when I use an expression such as Sum([Net]/[Membership]). I autosummed all of the values in the PMPM field that actually show up as values when performing a grouping by another field to list each and every result instead of summing that field (not 0.00) and the result matches what result I get in the query.

But when I bring the two fields Net and Membership and divide manually, the number is way off in the PMPM column. So I have been trying to figure this out and when I did the grouping to list each and every result line and divided the fields Net and Membership for one of the records where PMPM is 0.00, it will come up with a value like 0.0097749155136135, for example.

But the result is not Summing that value and I believe that if I were to sum each and every one of the records (there are 1,306 in all so it would take me forever to calculate all of those long numbers manually), it would come up to be the value I expect to get when dividing the sum of the net by the membership.

I don't have any property sheet for decimal places, input mask, or format set on PMPM field?

How can I get these "invisible 0.00 numbers" which actually have values, albeit tiny (but tiny enough to change the result if summed over 1,306 records) to be included in the Sum??

I am including a sample database with only the result set I got so you can see if you multiply the net by membership, it doesn't come out right???

Is this a fairly common issue. Please let me know, thank you.

Summing-Calculated-Field-Issue.accdb

The problem is when I use an expression such as Sum([Net]/[Membership]). I autosummed all of the values in the PMPM field that actually show up as values when performing a grouping by another field to list each and every result instead of summing that field (not 0.00) and the result matches what result I get in the query.

But when I bring the two fields Net and Membership and divide manually, the number is way off in the PMPM column. So I have been trying to figure this out and when I did the grouping to list each and every result line and divided the fields Net and Membership for one of the records where PMPM is 0.00, it will come up with a value like 0.0097749155136135, for example.

But the result is not Summing that value and I believe that if I were to sum each and every one of the records (there are 1,306 in all so it would take me forever to calculate all of those long numbers manually), it would come up to be the value I expect to get when dividing the sum of the net by the membership.

I don't have any property sheet for decimal places, input mask, or format set on PMPM field?

How can I get these "invisible 0.00 numbers" which actually have values, albeit tiny (but tiny enough to change the result if summed over 1,306 records) to be included in the Sum??

I am including a sample database with only the result set I got so you can see if you multiply the net by membership, it doesn't come out right???

Is this a fairly common issue. Please let me know, thank you.

Summing-Calculated-Field-Issue.accdb

ASKER

Would seeing the actual SQL help you? (I have it in Word format nicely organized)

I believe I am summing the numbers normally (net is a Number data type from the source table and Count is a field name from the other source table that is aliased as Membership which is also a Number data type).

I am not rounding either, there is no formatting in the property sheet (just a simple Sum function used in my expression that is Summing the division between net divided by Membership).

If you manually divide net by membership, you will see that the PMPM is way off (which is what I don't understand.....??)

Let's take the first record in my sample table (1390753.03/62732 shoud result in\= PMPM of 22.16975435184595 or a shorter version if rounded, but it is showing 10.31 I believe for that one I posted??)

Let me know and thank you for your help

I believe I am summing the numbers normally (net is a Number data type from the source table and Count is a field name from the other source table that is aliased as Membership which is also a Number data type).

I am not rounding either, there is no formatting in the property sheet (just a simple Sum function used in my expression that is Summing the division between net divided by Membership).

If you manually divide net by membership, you will see that the PMPM is way off (which is what I don't understand.....??)

Let's take the first record in my sample table (1390753.03/62732 shoud result in\= PMPM of 22.16975435184595 or a shorter version if rounded, but it is showing 10.31 I believe for that one I posted??)

Let me know and thank you for your help

ASKER

So I attached my sample database again so you can see my point.

one of the tables shows when I group by net and the other shows when I sum by net. I want you to notice that for the group by net table, if you manually divide the net by membership for one of the first ones where the PMPM is 0, you will see a small decimal number.

On the other hand, the other PMPM numbers divide correctly I believe.

So let's take the first one (600.06/62732 = 0.0095654530383217)

Here is my "question\argument": I am thinking that all of the many records with a PMPM of 0 are being summed as 0 (which makes sense\Access doing its job correctly if it divides to 0), but they should be showing there true value. I already tried to set the Decimal Places property to 15 to see if it would show, but only showed as 0.000000000000000?? I autosummed the PMPM values that are greater than 0.00 and got 10.31 (matches the result I get normally).

Problem: should be counting all of those tiny decimal numbers (which I believe would add up to the correct PMPM values once I sum on net, not group by--group by table just to show you what my point is).

Do you see what I mean if you manually divide the numbers?? You can imagine that these tiny decimal numbers can change the summed value when spread over more than 1,000 records, right??

Do you understand?

I should be getting that tiny decimal number I explained for the first record......

Summing-Calculated-Field-Issue.accdb

one of the tables shows when I group by net and the other shows when I sum by net. I want you to notice that for the group by net table, if you manually divide the net by membership for one of the first ones where the PMPM is 0, you will see a small decimal number.

On the other hand, the other PMPM numbers divide correctly I believe.

So let's take the first one (600.06/62732 = 0.0095654530383217)

Here is my "question\argument": I am thinking that all of the many records with a PMPM of 0 are being summed as 0 (which makes sense\Access doing its job correctly if it divides to 0), but they should be showing there true value. I already tried to set the Decimal Places property to 15 to see if it would show, but only showed as 0.000000000000000?? I autosummed the PMPM values that are greater than 0.00 and got 10.31 (matches the result I get normally).

Problem: should be counting all of those tiny decimal numbers (which I believe would add up to the correct PMPM values once I sum on net, not group by--group by table just to show you what my point is).

Do you see what I mean if you manually divide the numbers?? You can imagine that these tiny decimal numbers can change the summed value when spread over more than 1,000 records, right??

Do you understand?

I should be getting that tiny decimal number I explained for the first record......

Summing-Calculated-Field-Issue.accdb

Can you post the raw data *before* it is Summed or Grouped?

You have tables there with calculated values stored in them.

Please post the the data as it was before this.

The other issue is that these values are stored as "text" not numbers (as they should be)

You cannot really perform reliable mathematical operations on text data.

I am also confused as to what the data in your tables represents?

The sum of all the "Nets"

net

600.06

602.11

603.39

634.34

634.4

637.77

641.6

646.6

656

662.2

is:

6318.47

...So I am confused as to how you are getting the values of

SumOfnet

1313857.14

and:

1307329.31

I am also confused by a table that is has "Group by" in it's name, yet I cannot discern what the groups are, nor do I see any summaries...?

So again, lets tear this down and start again.

Post the *raw Numerical data only*.

No Summaries and no Grouping

(Because I am still not quite clear what they represent or how they were determined)

Then tell me what your ultimate goal here is...

JeffCoachman

You have tables there with calculated values stored in them.

Please post the the data as it was before this.

The other issue is that these values are stored as "text" not numbers (as they should be)

You cannot really perform reliable mathematical operations on text data.

I am also confused as to what the data in your tables represents?

The sum of all the "Nets"

net

600.06

602.11

603.39

634.34

634.4

637.77

641.6

646.6

656

662.2

is:

6318.47

...So I am confused as to how you are getting the values of

SumOfnet

1313857.14

and:

1307329.31

I am also confused by a table that is has "Group by" in it's name, yet I cannot discern what the groups are, nor do I see any summaries...?

So again, lets tear this down and start again.

Post the *raw Numerical data only*.

No Summaries and no Grouping

(Because I am still not quite clear what they represent or how they were determined)

Then tell me what your ultimate goal here is...

JeffCoachman

Are you storing the results in a table? If so, the Data Type of your field has to be set to Number and the size should be set to Double. You may want to show us the query you are using to do your calculation.

ASKER

This is the function that makes the results accurate (except that the decimal is 3 places too much to the right): PMPM: Sum(Round(([Net]*1000)/[Membership],15))

All I need to know now is how do I get the decimal point over to the left 3 places??

Thanks

All I need to know now is how do I get the decimal point over to the left 3 places??

Thanks

ASKER

Actually this is the function that makes the results accurate: PMPM: Sum(Round(([Net]*1000)/[Membership]/1000,3))

Decimal is where it should be.

This is what I notice though (although not a big problem, I don't think a problem, either):

If Net = 1390753.03 and Membership = 62732, PMPM = 22.16975435184595

If I Round it to 2, PMPM = 18.01

If I Round it to 4, PMPM = 22.1691

If I Round it to 3, PMPM = 22.118

But it would be nice to know how you guys would handle it (let's say, for building a report on top of my query--would you just limit the Decimal places to 2 and use the Round to 4 which is the most accurate, so that way in a report, it would show 22.17)??

Thanks

Decimal is where it should be.

This is what I notice though (although not a big problem, I don't think a problem, either):

If Net = 1390753.03 and Membership = 62732, PMPM = 22.16975435184595

If I Round it to 2, PMPM = 18.01

If I Round it to 4, PMPM = 22.1691

If I Round it to 3, PMPM = 22.118

But it would be nice to know how you guys would handle it (let's say, for building a report on top of my query--would you just limit the Decimal places to 2 and use the Round to 4 which is the most accurate, so that way in a report, it would show 22.17)??

Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you for the good advice. In my case the Net and Count fields in their respective tables are not rounded as far as I know when they get loaded into the SQL tables (net is a number field with two decimal places and Count is just a count of members so you can't have a half a person or something like that).

As far as the query goes, I chose to Round to 4 decimal places, tested a quick report where I limit the decimal places to 2 and it comes out pretty accurate.

So my number that I isolated to test if it was working was 22.1691 when Rounded to 4 decimal places. When I tested it in a report, with two decimal places only to be displayed, it showed 22.17.

I believe that this is going to be accurate enough for reporting purposes. The net value doesn't do any rounding I don't believe though and therefore, isn't storing any inaccurate information.

Outstanding point, though. I will remember that!!

Thank you Jeff

By the way, I believe you were working on my other open question (Automating Parameterized Reports--it recently took me over 2 8 hour days to do it manually so there is very high value in creating this sort of automation)

If you aren't familiar with my open question, please see for yourself. (We can't be the first "chimps" to request this sort of automation, right??)

As far as the query goes, I chose to Round to 4 decimal places, tested a quick report where I limit the decimal places to 2 and it comes out pretty accurate.

So my number that I isolated to test if it was working was 22.1691 when Rounded to 4 decimal places. When I tested it in a report, with two decimal places only to be displayed, it showed 22.17.

I believe that this is going to be accurate enough for reporting purposes. The net value doesn't do any rounding I don't believe though and therefore, isn't storing any inaccurate information.

Outstanding point, though. I will remember that!!

Thank you Jeff

By the way, I believe you were working on my other open question (Automating Parameterized Reports--it recently took me over 2 8 hour days to do it manually so there is very high value in creating this sort of automation)

If you aren't familiar with my open question, please see for yourself. (We can't be the first "chimps" to request this sort of automation, right??)

ASKER

Jeff explained why my question is of great value when analyzing record sets of great numbers such as 1 million records (1 million pennies = $10,000) where decimal values could be stored when they shouldn't be stored.

OK

great.

Just remember that every situation is different.

The bottom line is that, one way or another, you should make it clear if any rounding is taking place.

People might "interpret" the data incorrectly, but you should always have accurate values to make sure 100% accurate totals/sums can be produced.

;-)

JeffCoachman

great.

Just remember that every situation is different.

The bottom line is that, one way or another, you should make it clear if any rounding is taking place.

People might "interpret" the data incorrectly, but you should always have accurate values to make sure 100% accurate totals/sums can be produced.

;-)

JeffCoachman

In other words, NEVER round anything except for the very, very last total.

Not sure what your sample is supposed to be showing me?

But if those hardcoded values are actually rounded, then they should not be.

In other words, post a sample with the *raw* data, then post the sum you got, ...then post the sum you should have gotten.