Is it possible to calculate squareroots, etc. in query?

[tubbers]

I have the following formula which needs to be calculated during a query:

Field(s) involved:
U (nbr)
V (nbr)
W (nbr)
Magnitude (nbr)

Magnitude = squareroot ( U² + V² + W² )

Is this possible to do?

 

[SkipVought]

Hi,

Check out the Expression Builder (clunkie in my book) in the query grid
[tt]
Sqr(U ^ 2 + V ^ 2 + W ^ 2)
[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[tubbers]

That worked great...I had found the Sqr function but wasn't sure how the ^2 would work. Thanks.

 

[LittleSmudge]

If Magnitude = squareroot ( U2 + V2 + W2 )

then hopefully Magnitude is NOT a field at all !

SELECT (U ^ 2 + V ^ 2 + W ^ 2) ^ 0.5 As Magnitude FROM .. .etc.

Makes Magnitude a 'Column' in the query but not a Field in the underlying table.




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

Another way:
Sqr(U * U + V * V + W * W)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

Thanks for all the good info...

I ran into a problem using the Sqr(...) method. I couldn't export the query results ("Data type mismatch in criteria expression" error) or even sort by the magnitude column (You're right LittleSmudge, that Magnitude isn't really a field in the original table but a "column" in the query).

I will try using the format LittleSmudge and PHV offered and post back if I get it working....

 

[PHV]

"Data type mismatch in criteria expression"
Sqr returns a Double value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

I used LittleSmudge's method (^0.5) and I didn't get the datatype mismatch error when I went to sort by magnitude but its still grinding away so I'm not sure if the end result will be right.

Is it typical for this to take so long? There are 1,000,000 or so records in this query.

 

[LittleSmudge]

Wow 10^6 records - I'm not surprised it 'grinding away'.

Remember what the processor is actually doing to calculate
(U ^ 2 + V ^ 2 + W ^ 2) ^ 0.5
for you

Its Log(U) and that's not a quick/easy calculation in its own right
Log(U) * 2
InvLog(Log(U)*2) and thats not quick either.

Repeat for V

Repeat for W

InvLog( (Log( InvLog(Log(U)*2) + InvLog(Log(V)*2) + InvLog(Log(W)*2) ) * 0.5 ))




How often do you do this calculation ?

Do you need to do the calc for EVERY record ?


'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[tubbers]

LittleSmudge,

Unfortunately, I do have to do this calculation for every record as it is calculating vectors for various types of 3D modeling.

Fortunately, there are only two projects at this time that use this query and we should (in theory) only have to run the query once per project.

On an interesting point, when I run the query as a SELECT query, the query actually calculates quite quickly (less than 5 seconds). When I change it to a MAKE TABLE query or try to export the query as a .cvs file, that is when it takes the 2 hours+ to run. (The query results need to be in either table format or a text file to be imported into another program for plotting.)