Bizzare query bug (1 Viewer)

Hi there all, I'm a bit of an access noob, so please bear with me.

I have a query that among other things, takes two numbers and divides them by each other to calculate the percentage. This percentage calculation work perfectly fine when I use a select query, but if I try to change it to a make table query and output the data, than this percentage calculation becomes innaccurate.

For example one record has -208984.52 and 10684968.6 so the % field has -0.0195587397421083670755944009. However when I switch it to a make table and create an entirely new table, the other numbers remain unchanged, but now the % field reads -0.01. It seems that the make table is truncating this field, not going past 0.00, or even bothering to round. I have a similar field, however based off of totally different numbers, and they aren't truncated.

I'll also add that I've been using this database for a couple of months and haven't run into this issue until last week.

Any idea what could be causing this, and how to fix?

Considering the simplicity of the calculation, is there any reason you can't store the 2 base values in your temp table, and dividing them when you query it later?

I don't think It'd work. At least, I don't see how to do it.

Basically this table is about 45 records with grouped sums. It's built off another table with 10,000 records. Sums up all records in Group A in year 1, Group B in Year 1, Group A in year 2, etc.

So what's going on is that on the lower level each record has field X numbers, but Field Y numbers only exist for the groupings for each year. So I sum up field X for each year, have a table with it next to field Y each year, I divide Y/X, then i take that percentage down into the the table with 10,000 records, multiply the Y/X by the lower level X, and can estimate the lower level Y.

Is there a way I could look at a record, see what group and year it's in, and sum the group for that year on the fly?

What's most confounding is that I have multple such percentages. Y/X gets truncated, but P/Q does not.

Hmm, I understand your problem, but don't know enough about Make Table queries to suggest a solution (can you set decimal precision?) (I no longer use Access, but I spend most of my work days writing SQL).

I don't suppose changing the
"fieldX/fieldY AS calcPercentage" to
"CDbl(fieldX/fieldY) AS calcPercentage" would work?

How many decimal places would you like?
See the round function or consider Cdbl as was suggested.

A fair number. Rounding errors can add up, and I want to minimize those. Ideally I'd want so that when I tally up all Y/X*X I'll get Y to the hundredth decimal place. I'll have to try the Cdbl fix tomorrow morning. I'm still curious as to why one of the calculated fields would need it, and the other not.

The Cdbl fix works perfectly, it's still slightly different in that it now has the number as -1.95587397421084E-02, but that's not an issue at all. Thanks for your help. (I am still curious as to the why, but oh well)

If I'm not mistaken, when Access creates a table based on a query, it automatically attempts to guess at the required data type. Sometimes (read: quite often), it's wrong.