number types

[marta]

Hello
i have a crosstab query with the following results:

name avg val1 val2...
marie 2.334 3 2
jane 7.1 9 6
joe 3 2 4

and a table with the following values:

Val type
1 A
2 B
3 C

i've created a second query to tie the above two together
(with joined val fields), however, these are my results:

name val type
joe 3 A

it is only displaying the number whose result matched
exactly that of the table. i need to be able to round to
the nearest whole number and then compare values, what is
the best way to do this?

Thanks so much for your help!
Marta

 

[Michel Walsh]

Hi,



Change your crosstab from

TRANSFORM AVG(something) As AverageOf ...

to:


Bring the crosstab and the second table in a new query :



SELECT x1.*, secondTable.type
FROM x1 LEFT JOIN secondTable ON
Int(0.5+x1.AverageOf) = secondTable.Val



Assuming the crosstab field name was AverageOf.


Hoping it may help,
Vanderghast, Access MVP

 

[marta]

i am so happy, it worked. thank you!

-----Original Message-----
Hi,



Change your crosstab from

TRANSFORM AVG(something) As AverageOf ...

to:


Bring the crosstab and the second table in a new query :



SELECT x1.*, secondTable.type
FROM x1 LEFT JOIN secondTable ON
Int(0.5+x1.AverageOf) = secondTable.Val



Assuming the crosstab field name was AverageOf.


Hoping it may help,
Vanderghast, Access MVP





.