How do I truncate values returned by a query function (wtd average)

[kujito]

I'm running weighted averages on ore grades for mine resource estimates. I got the wtd. avg. to come back correctly, but I only need the results out to 3 decimal places instead of the 15 it spit out.

Values like '1.09237094258859E-02' and '0.180000000000004' and '0.349999999999998' are just unwieldy.

How do I get them down to a only 3 decimal places?

Thanks In Advance,
Brian

[ChipR]

You might want to use the FormatNumber Function with just the NumDigitsAfterDecimal argument.

[kujito]

Thanks ChipR

The average function of my query looks like:

AVG(((Resource.MeasuredTons * Resource.MeasuredGrade1) + (Resource.IndicatedTons * Resource.IndicatedGrade1) + (Resource.InferredTons * Resource.InferredGrade1))/(Resource.MeasuredTons + Resource.IndicatedTons + Resource.InferredTons)) AS Grade1WtdAvg

From the link you posted, it seems that it should be like:

AVG(((Resource.MeasuredTons * Resource.MeasuredGrade1) + (Resource.IndicatedTons * Resource.IndicatedGrade1) + (Resource.InferredTons * Resource.InferredGrade1))/(Resource.MeasuredTons + Resource.IndicatedTons + Resource.InferredTons) [,3]) AS Grade1WtdAvg

I've tried a few variations on the bracketed 3 part and either get a 'syntax(missing operator)' error or 'wrong number of arguments used' error when I drop the brackets completely. Leaving that part off entirely results in an "Overflow" error! Not sure what happened there. All I changed from earlier is the (attempted)addition of the "[3]".
Tomorrow is my last day with this company and I'd like to finish this up.

[ChipR]

Try this:

FormatNumber(AVG(((Resource.MeasuredTons * Resource.MeasuredGrade1) + (Resource.IndicatedTons * Resource.IndicatedGrade1) + (Resource.InferredTons * Resource.InferredGrade1))/(Resource.MeasuredTons + Resource.IndicatedTons + Resource.InferredTons)),3) AS Grade1WtdAvg

[NeoPa]

If you want the result simply for display purposes then the Format(), or FormatNumber(), functions will do fine for you.

If, on the other hand, you need the returned values to be numeric (the named functions return string values), then you will need to use one of the various rounding functions. The choice of which to use would depend on your precise requirements.

[kujito]

Thanks for the replies.
I ended up using:
Round(AVG(((...)), 3) AS ...

I feel kinda silly for not figuring it out on my own as it's not that hard. The last few days have been pretty hectic here. Today is my last day here and I need to get a bunch of summary reports put together for a 70-something geologist/VP.

Another quickie. I need to run this query for 4 ore grades. When I try to do it (even just 2 at a time) in a single large query, I get an "Overflow" error. As a result, I now have 5 queries in total. What causes that error?

[NeoPa]

That depends on what your values are I expect.

We don't have much info on exactly what you're doing, but I would guess some sort of summing. I'm not sure what type of variable the Avg() and Round() functions take.

If that doesn't help much, tell us what you mean by running multiple ore grades together.

[kujito]

Yeah, it's a kinda convoluted setup. I'll try to explain what I mean. Sorry for the length.

Mine resources are divided up into three categories, depending on economic viability. The three resource categories are: Measured, Indicated, Inferred.
Each metal that is or will/may be mined is graded on one or more of these categories as a percentage of metal contained within an amount(tons) of ore. The ore from the mines in my database contain up to four target metals, usually Copper, Molybdenum, Gold, and Silver.
My weighted average calculations are of the grade of a single metal across the three resource categories. The formula above would give me the weighted average for "Metal1". I also need to run it for Metal2, Metal3, and Metal4. When I try to build a single query to find the weighted averages for more than one "Metal" at a time, I get an "Overflow" error. Ver batim, the message says "Overflow." That's it. It only happens when I try to run the weighted average for more than one metal at a time, or "multiple ore grades."

[NeoPa]

That explains it well enough in general terms, but it tells me very little about the structure of the data you're working with.

Try to explain the situation in database terms.