Solved Query issue with currency and extra pennies (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:56
Joined
Jan 20, 2009
Messages
12,372
I'm asking "What is the data type of the column containing the explicit percentages?"
Misty showed this was Currency in Post #8.

The problem is that the rounding of each line is not aware of the rounding in the other lines so there is potential to accumulate a discrepancy in the total. I think arnelgp understood this and provided a solution.
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
You misinterpreted my question, I believe, or perhaps I wasn't clear. The explicit percentages in that table you showed us ALSO have a data type that includes a decimal point. You confirmed that all money values were in fact defined as CURRENCY, and that is good. My question is not about money values. I'm asking "What is the data type of the column containing the explicit percentages?" I don't care that you typed them, imported them, somehow computed the percentages, or that they sprang forth from the brow of Zeus. What is that field's data type?

I ask that question because of something called "implicitly coerced type" that occurs in expression evaluation. There is a possibility that despite having an endpoint as CURRENCY, there might be a data path that does not STAY as CURRENCY. I was trying to decide if that is possible.
Thank you for the clarification, I posted the data type for the percentages in post #8 above.
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
I made a function that you can call
in a Query.

the only catch, is that you need to put
your query in a form (maybe datasheet)
and call resetfixDistribute() when the form/report
opens, so that the Collection object is
reset on the function.

on the demo, replace "Query1" on the function
(Const YOUR_QUERY = "QUERY1") with the
name of your Query you posted on Post#1.

create New query from your query (post#1)
and adding a Calculated column.
see my sample Query2.
Thank you. I'll take a look at it. The form requirement is no problem, since I use a form and a combo box to select the year that represents the criteria for two of the queries in this report. I can apply it to that form.
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
Misty showed this was Currency in Post #8.

The problem is that the rounding of each line is not aware of the rounding in the other lines so there is potential to accumulate a discrepancy in the total. I think arnelgp understood this and provided a solution.
Thank you, it is post 8, but the highlighted data type is the Distribution (percentage) field and it is Number/Decimal with one decimal place...
percentages.PNG
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:56
Joined
Feb 28, 2001
Messages
20,831
Misty showed this was Currency in Post #8.

The problem is that the rounding of each line is not aware of the rounding in the other lines so there is potential to accumulate a discrepancy in the total. I think arnelgp understood this and provided a solution.

Technically, no. I looked and she DID say it was DECIMAL - not CURRENCY (which is a subset of DECIMAL.)

The problem is that we can't see the actual computation behind the scenes. We don't know if this computation ever went into a floating-point format because of the fractions that result when dealing in decimal fractions on a binary machine. Those numbers are clearly coming from a problem with some number getting rounded up when it should have been truncated. The trick will be to find which ones to truncate.

I'm still thinking about it but it appears that I am straying from the main path here. So with apologies to Misty if I confused the situation, I will step away. What I think is happening cannot be fixed so easily without some intermediate function to rescale the numbers, truncate them, and then scale them back to their original scale factors. That appears to be conceptually similar to what ArnelGP has suggested.
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
I wrote out a methodology for management, and I thought I would post it (redacted) just to show what I am trying to accomplish. This sort of helps explain the multiple levels of aggregating, slicing and dicing, and aggregating.

methodology.PNG
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
I just found something, and wonder if this could be an issue. I'm asking this because throughout this thread, many of you have asked for the data types of these fields, and I just discovered this one is different from the data type of the distribution percentage (which we have been discussing). So I want to see if this is something that could be contributing to the issue.
creditfield.PNG
creditdatatype.PNG

I just went ahead and changed this one to decimal/percent with one decimal place. It did not have an effect on the overall. I'm currently working on some of the other suggestions in this thread...
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:56
Joined
Feb 28, 2001
Messages
20,831
I will state that your best evidence is that changing the data type didn't fix the problem, so that must not have been the problem.

HOWEVER, I want to call your attention to something you showed us that I think is related. Look at this thread's post #6 (which you posted.)

The right-hand image frame has a highlighted line that shows 5 digits to the right of the decimal. I don't think that can happen with a true CURRENCY field, because that is an integer field SCALED to show only four digits to the right of the decimal point. Something is terribly wrong there. That last "5" cannot be there if that is a CURRENCY field set for four decimal places. But it IS there - which means something is off with that field. I don't know what - but SOMETHING is off.
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
I will state that your best evidence is that changing the data type didn't fix the problem, so that must not have been the problem.

HOWEVER, I want to call your attention to something you showed us that I think is related. Look at this thread's post #6 (which you posted.)

The right-hand image frame has a highlighted line that shows 5 digits to the right of the decimal. I don't think that can happen with a true CURRENCY field, because that is an integer field SCALED to show only four digits to the right of the decimal point. Something is terribly wrong there. That last "5" cannot be there if that is a CURRENCY field set for four decimal places. But it IS there - which means something is off with that field. I don't know what - but SOMETHING is off.
Without digging intensely deep on that one, from the top of my head I can offer that that field is (a) the one that was not subject to the Round() function; and, (b) is the result of being multiplied earlier up in the chain by the other credit field that I posted later in this thread.
 

mistyinca1970

Member
Local time
Today, 12:56
Joined
Mar 17, 2021
Messages
117
then you need two queries. The first to calculate the difference between the sum of the calculations and the initial value which is pretty straight forward. The second query is used to uniquely identify which of the calculated values is going to be modified for this difference. You have to have some rules for this - you could use the first or last record for example or perhaps a record designated in some way. I wouldn't recommend the one with the largest or smallest amount since there is a risk that you have two calculations with the same amount so would not be unique.

Finally use another query to combine these to give your final result.
I did it!!
I ended up using this method suggested by CJ London, but it took a little more than two queries to accomplish.

I knew which one of the calculated values would be ideal for applying the modified difference because there is an entity that appears in each of my zones, and that is a county entity while all the others are cities. So I added a new field to tblDistribution that indicated whether each City is "City" or "County".

Then, I added that field to the original query that I had posted in post #1 of this thread.
I then created an aggregate query off of that one to total the calculated distribution amounts along with the zone totals.
I queried off of that one to calculate the difference between the total for each zone and the sum of the distribution amounts.
This query was then combined with my original query with only the County distributions selected.
A query of my original query was created with only the City distributions selected.
These last two were combined in a union query to capture the results of each. And this is now the query that is tied to my report.

I'm guessing there is a more efficient way to get to this point without as many queries. Please be kind and remember I am self-taught in this, and I have found I can't combine aggregates and other calculations in the same queries. If there is a way to do this, then that would save me a few queries.

Thank you to all the suggestions posted in this thread!

The final query:
Code:
SELECT qrySysEnhDistributionMaster.DistYear, qrySysEnhDistributionMaster.Sequence, qrySysEnhDistributionMaster.ZoneSequence, qrySysEnhDistributionMaster.Zone, qrySysEnhDistributionMaster.AvgOfDistribution, qrySysEnhDistributionMaster.City, qrySysEnhDistributionMaster.Distribution, qrySysEnhDistributionMaster.DistributionAmt AS DistributionAmt2, qrySysEnhDistributionMaster.AnnualReport, qrySysEnhDistributionMaster.EffectiveZone, qrySysEnhDistributionMaster.CityCounty
FROM qrySysEnhDistributionMaster
WHERE (((qrySysEnhDistributionMaster.CityCounty)="City"))
ORDER BY qrySysEnhDistributionMaster.Sequence, qrySysEnhDistributionMaster.City
UNION SELECT qrySysEnhDistributionMaster.DistYear, qrySysEnhDistributionMaster.Sequence, qrySysEnhDistributionMaster.ZoneSequence, qryTotalsOfDistribution2.Zone, qrySysEnhDistributionMaster.AvgOfDistribution, qrySysEnhDistributionMaster.City, qrySysEnhDistributionMaster.Distribution, [DistributionAmt]-[Difference] AS DistributionAmt2, qrySysEnhDistributionMaster.AnnualReport, qrySysEnhDistributionMaster.CityCounty, qryTotalsOfDistribution2.EffectiveZone
FROM qryTotalsOfDistribution2 INNER JOIN qrySysEnhDistributionMaster ON (qryTotalsOfDistribution2.Zone = qrySysEnhDistributionMaster.Zone) AND (qryTotalsOfDistribution2.Zone = qrySysEnhDistributionMaster.Zone)
WHERE (((qrySysEnhDistributionMaster.CityCounty)="county") AND (Not (qryTotalsOfDistribution2.EffectiveZone)="nw-r"))
ORDER BY qrySysEnhDistributionMaster.Sequence, qrySysEnhDistributionMaster.City;
 
Last edited:

Users who are viewing this thread

Top Bottom