469,648 Members | 1,582 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

Strange query results

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
1 1280
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.

Similar topics

14 posts views Thread by smilesinblues | last post: by
4 posts views Thread by Ryan | last post: by
6 posts views Thread by Vance Kessler | last post: by
6 posts views Thread by Gary | last post: by
reply views Thread by Wescotte | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.