By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,463 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

Strange query results

P: n/a
AP
We commonly use this method to "pivot/crosstab" table results. For
example lets say there are two offices, and we want the total sales
for each ofice in its own column(Next to each other). We do not want
to use a crosstab query so please do not recommend it.

Typically we do this. If the data is structured as follows

Office Sales Month
A 5006.432 1/1/02
B 5056.987 1/1/02
A 8882.625 2/1/02
B 5006.558 2/1/02

Result looks something like this

Month OfficeA OfficeB
1/1/02 5006.432 5056.987
2/1/02 8882.625 5006.558

We build the query with totals like this:

Field: Month (Group By)
Field: ASales: = Sum(iif([office]="A",[Sales],0))
Field: BSales: = Sum(iif([office]="B",[Sales],0))

This usually works great with normal numbers, however this particular
table stores all the numbers with the 3 decimal, and they are exact,
they never have more or less than 3 digits such as 5006.4325879. What
is really odd is sometimes the results will be close, but include
addiional decimals past the 3 spot, it is almost as if the 0 is not
really a zero. Changing the field type from single or double does not
help. Multiplying the number *100 and then later dividing back does
not help either.

Any idea of what is causing this to occur?
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
AP wrote:
We commonly use this method to "pivot/crosstab" table results. For
example lets say there are two offices, and we want the total sales
for each ofice in its own column(Next to each other). We do not want
to use a crosstab query so please do not recommend it.

Typically we do this. If the data is structured as follows

Office Sales Month
A 5006.432 1/1/02
B 5056.987 1/1/02
A 8882.625 2/1/02
B 5006.558 2/1/02

Result looks something like this

Month OfficeA OfficeB
1/1/02 5006.432 5056.987
2/1/02 8882.625 5006.558

We build the query with totals like this:

Field: Month (Group By)
Field: ASales: = Sum(iif([office]="A",[Sales],0))
Field: BSales: = Sum(iif([office]="B",[Sales],0)) This usually works great with normal numbers, however this particular
table stores all the numbers with the 3 decimal, and they are exact,
they never have more or less than 3 digits such as 5006.4325879. What
is really odd is sometimes the results will be close, but include
addiional decimals past the 3 spot, it is almost as if the 0 is not
really a zero. Changing the field type from single or double does not
help. Multiplying the number *100 and then later dividing back does
not help either.

Any idea of what is causing this to occur?


Who knows. Maybe by switching back and forth between currency, double,
and single the precision values have been inserted.

In debug
? Left(cstr(5006.4325879),Instr(cstr(5006.4325879)," .") + 3)
returns
5006.432

Maybe create a new column set to currency 3 with an input mask to allow
only 3 digits. Update the new column with the formula
Ccur(Left(cstr(CurNumber),Instr(cstr(CurNumber),". ") + 3))
where CurNumber is your current number field. Now run your query on
that field. If that returns the correct results, kill the old field,
save the table, then re-open and rename the new field back to the old field.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.