Expression gives wrong results

[newtoAccess]

Any idea what I am doing wrong.
I have a query where I count and sum fields from a table then divide to get the percent.
ex: totalCount, sumoffield01, sumoffield02, sumoffield03, sumoffield4, sumoffield5,

I created a new column used an expression [sumoffield01]\[totalCount]
The result was 0.01234 This result is good.
I created another column for [sumoffield02]\[totalCount]
The result was 0.01234 it is also good
When I get to the 3rd new column for [sumoffield03]\[totalCount]
The result was 1.234 it was of course NO good



I have tried deleting and creating a new column. still not good
I have tried copying and changing just the2 to a 3 still no good

I have created a new query with just the fields that is giving me the headache same problem field 3, 4, 5 all seem to be moving the decimal over 2 spots.
The source is the save query.
When I check the sum of the columns it is good.
The first two expressions work then the next three give the wrong results.

[ConneXionLost]

Break this single query into two queries. First query for:

totalCount, sumoffield01, sumoffield02, sumoffield03, sumoffield4, sumoffield5,

Second query for your expressions: [sumoffield01]\[totalCount], etc.

[newtoAccess]

See attached mdb sample
as you can see fields 3,4,5 give the wrong Math

[newtoAccess]

So what you are saying is to get my data for the math from a different query not from the query I am working in?

[ConneXionLost]

Yes. Like this:

SELECT [SumOfcopy_of_credit_bureau01]/[Countofcopy of credit bureau] AS Percent1, [SumOfcopy_of_credit_bureau02]/[Countofcopy of credit bureau] AS Percent2, [SumOfcopy_of_credit_bureau03]/[Countofcopy of credit bureau] AS Percent3, [SumOfcopy_of_credit_bureau04]/[Countofcopy of credit bureau] AS Percent4, [SumOfcopy_of_credit_bureau05]/[Countofcopy of credit bureau] AS Percent5
FROM [Query Count and Sum test];

Cheers,

[ConneXionLost]

Duplicate post.

[newtoAccess]

I cut and pasted in the above and all that did was change the name of the field (expression Name) the calculations are still wrong.


I has gotten to big to upload, I do not see a win zip on this PC. What site you you recommend to get a zip program from?

[newtoAccess]

Can you open this attachment?

[newtoAccess]

You still there?
Were you able to open the file?
If not I was able to get a newer version of winzip.
Do you see that the math is still not working?

[ConneXionLost]

The calculations are working normally as laid out. I do not see the error you seem to be indicating.

For example, I see:

[SumOfcopy_of_credit_bureau03]/[Countofcopy of credit bureau] = 5.35%

What result are you expecting to see?

[newtoAccess]

See attached.
I have expanded two queries "Query Count and Sum Test" Does as it say and is good.
and "Query Percent from Query count and sum test"
This query uses the "Query Percent from query count and sum test" to create new fields and run ex: "Expr3: [SumOfcopy_of_credit_bureau03]/[CountOfcopy of credit bureau]"

When you run the query you should see that all the percent results are like this"0.0123" and then there are Expr:3 Expr:4 and Expr5 that the results are "5.349" when they should be "0.05349"
For some reason Expr3:, Expr4:, and Expr5: are automatically converting to percent when the rest are not converting.
I have set up my report to show all 25 results and only these three are doing the auto converting to %.

When I print out the code I do not see a difference. Is there a switch inQuery Design that needs to be set to turn off the auto % on these three fields?

[ConneXionLost]

Here is a cropped image of your (unaltered) query output:

[newtoAccess]

Attached is what I see.

I have circled the wrong data. The code is of the same format as the rest of the fields.
I was using the test mdb as above.

[ConneXionLost]

Is that a form?

[newtoAccess]

Is that a form?

-NO- It is a query. I used the same mdb as attached above.
I have rebuild the query and got the same results.