Thread: field division problem

Hi, im stuck on a new issue with my table, what I have is a query that pulls from table: The names are as follows

Centro, tipos, Divisao, SomaDeMonth, SomaDevalcont, Valcontdivision, Final

What I have correct is the Somademonth, somadevalcont, the problem im having is with the formula for Valcontdivision, and Final.
the Formula for Valcountdivision should be Tmonthsum/Valcount sum, however, it doesn't do this, the formula I have is

divide: Soma(SeImed([valcont]=0;0;[months]/[valcont]))

The total is suppose to be 867917, but I get a I different #. Im not sure how to divide by the somademonth field/somadevalcont field...

Then the other Final, is suppose to be [QUOTE]final: ([divide]/12)QUOTE]
The true # should come out to be 45,83281616 etc... However, Im having some difficulty with the last 2.

Anyone have any ideas what im doing wrong with the formula, I think the major problem is the formul I have for Valcountdivision.

Any help would help, I tried attaching the access but wouldnt work,

I tried attaching the access but wouldnt work,

You need to zip Access files before you upload them. Try again, it will make it easier to help solve your issue. Scroll down and select Manage Attachments. You are using fields named ValCont and tmonth, yet I don't see them as fields in your query. Is this a typo or have you neglected to select them from your table?

Alan

You have posted a db with 20K records. I do not have the time or inclination to validate your data in your query as this would take hours. Would you post only sample (dummy) data that can be quickly validated to determine where there is an issue. Initially your query looks ok, but I have no way to validate the data with the results.

The reason I want to be able to validate is that the formula looks ok to me. I would want to be able to ensure it is working properly by being able to ensure its validity with a smaller sample. Will be travelling for a few days and may be absent from the forum until I return.

Alan

added new modified smaller version of the table,

SomaDeValcont SomaDemonths divide final
64508 16928942,5 54,97 4,58
312267 305304242,3 16.693,53 1.391,13
566 362572655,94 20.098,52 1.674,88

For example # 2 305304242,3/312267, if you divide Months/valcount the # is 977.7026 and then if you divide by 12 I get81.752.
The formula I have gives a different answer

divide: Soma(SeImed([valcont]=0;0;[months]/[valcont]))

Im not sure what im doing wrong, because if I hit group by in the field tabs of DIVIDE, then it divides correctly; but it shows every single order.

WHen I dont have "group by" and the 2 fields SomaDeValcont SomaDemonths, sum the total, it wont divide months/Valcont correctly, im not sure why
let me know what u think, when you get back, no rush, appreciate your help

I took a look at your query and I think the problem is that you really need to do the calculation on the Alias Fields and Access won't let you do that in the same query. So you used the original Valcont Field which because some of the records have zeros in them gives you a "bad" answer. To make this work, you must do the expressions in a query based upon the first query. Look at the attached in which I created a new query with the expressions as you showed in your db based upon the information obtained in your original query. I hope the results are as you desire.

Alan