powers and looping

[Karen]

Most my experience has been with MySQL, so I write queries in terms of
SQL statements. I am using access right now as it is on the machine I
have. I am using the SQL window as I couldn't figure out how to get it
to do what I wanted using the design view or wizard. My problem is I
need to take the average of a log of a number (variable name =Ne). In
MySQL the command was:

select avg(Pow(10,Ne)) from TABLE where.....

Access doesn't like the word 'pow'. When I asked help it said to use
'power', but this resulted in same error. what is the proper command?

Also, I am making 15 graphs that need 288 pts, is there a way to loop
this in the SQL window? The MySQL had a Pearl DBI which handled this.
I don't want to have to start 4320 queries by hand.

[Albert D. Kallal]

"Karen" <kr*****@usgs.gov> wrote in message
news:d1**************************@posting.google.c om...

My problem is I
need to take the average of a log of a number (variable name =Ne). In
MySQL the command was:

select avg(Pow(10,Ne)) from TABLE where.....
Hum, I don't see a log function in the above. In MySql pow(x,y) simply
raises x to the power of y.

In JET sql, you can thus use:

select avg(10 ^ [ne])

so,

2 ^ 4 = 2 raised to the power of 4 and that gives 16.

So, it is not clear if Ne is some variable, or Ne is some field in a table.
Since you used the term variable..then I don't think you are talking about a
standard sql statement here. (you might want to clarify this).

Access doesn't like the word 'pow'. When I asked help it said to use
'power', but this resulted in same error. what is the proper command?
see above...you can use the format we learned in high school years ago:

x ^ y
Also, I am making 15 graphs that need 288 pts, is there a way to loop
this in the SQL window? The MySQL had a Pearl DBI which handled this.
I don't want to have to start 4320 queries by hand.

Hum, the above part about starting 4320 quires seems strange! I can well
assume that you have one query, and you need to feed it 4320 values (perhaps
those values come form another table). However, I am at a complete loss if
you do in fact have 4320 queries!!!

Obviously, you started using pearl here, and where not just using sql. For
ms-access, you can also loop, and execute statements by using code. (so, you
have to create a code module to do this). However, while you want to execute
all these select queues, where do you want to send the results to?? (more
temp tables...or what?). I suspect what you really need here is to generate
the data points in code, and then have the sql use that data?

However...the asnwer is yes..you can loop through saved quieres if you
want...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Karen]

> Hum, I don't see a log function in the above. In MySql pow(x,y) simply

raises x to the power of y.
Sorry I was being unclear, I was in quite the state when I posted,
hope this clarifies things. The value Ne (in the field by the same
name - stands for electron number density) is a log of the actual
number I need to average. The average a log is not the same as the log
of the average. Therefor I need to take 10 to the power of Ne for each
entry.
In JET sql, you can thus use:

select avg(10 ^ [ne])
K, thanks, I'll try that.
see above...you can use the format we learned in high school years ago:
lol, I'm old enough that my highschool didn't get computers till the
year after I graduated.
Hum, the above part about starting 4320 quires seems strange! I can well
assume that you have one query, and you need to feed it 4320 values (perhaps
those values come form another table). However, I am at a complete loss if
you do in fact have 4320 queries!!!

Actually the query is mostly the same except for the conditional
statement (WHERE....). I want the average values of 15 different
parameters at different altitudes and times (the parameters, altitudes
and times are all fields in the table). Once I get the numbers, I put
them in a data(text) file to feed to MatLab for graphing. I am looking
to use a nested loop (one to count hours, the other to go through
needed altitudes) to generate the data so I don't have to change
values in the Where statement between each query, the program would do
it. Then I just need to start it a couple times when the perameters
change. (ie averaging electron temperature rather than electron
density) This is also important as the machine the database is on is
old and slow. If I can loop, I can start it before I go home at night
and come in to work to find answers. (WOW! answers at work! what a
concept!)

Very shortly I will be working with oracle and its PL/SQL, so if the
SQL window allows for programing, not just single queries, then I may
try that to see if its syntax works.

Thanks
Karen

[Albert D. Kallal]

"Karen" <kr*****@usgs.gov> wrote in message
news:d1**************************@posting.google.c om...

lol, I'm old enough that my highschool didn't get computers till the
year after I graduated.
that is funny!! Actaully, I not sure where the heck I remmber that notation
from now! It might be from my old calcualr..and not computers!
( hum...not really sure..but it don't matter!!).

Then I just need to start it a couple times when the perameters
change. (ie averaging electron temperature rather than electron
density) This is also important as the machine the database is on is
old and slow. If I can loop, I can start it before I go home at night
and come in to work to find answers. (WOW! answers at work! what a
concept!)

Very shortly I will be working with oracle and its PL/SQL, so if the
SQL window allows for programing, not just single queries, then I may
try that to see if its syntax works.

Actually, in ms-access it is a bit different. You can write statements in
the sql window (as you well have mentioned). However, to run code, you have
use a code module.

Not only can you generate the data..but you also get the code to produce the
text file for you also!

So, it seems to me, you got the data...but just need to generate some more
data from this..and then send it out to a text file..right?
(this is all doable here...).

Lets assume that you have a table with the 15 parameters. And, lets assume
you also have all the data in another table...(am I on the right track?). Is
it also reasonable to assume that you need one text file generated for each
set of parameters...or one big text file with all the results?

So, if you give a few more desalts..I can give you some "sample" code snips
that may very well get you on your way. Also, how many records are we
talking here? 10,000.....or 900,000?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Karen]

> Actually, in ms-access it is a bit different. You can write statements in

the sql window (as you well have mentioned). However, to run code, you have
use a code module.
how do I make a code module?
So, it seems to me, you got the data...but just need to generate some more
data from this..and then send it out to a text file..right?
(this is all doable here...).

I have 1 table, it has 26 fields and 2,962,718 entries. (my poor slow
overworked laptop takes 4 min to run 1 query.) I remembered to ask for
all 5 parameters averages (Ne, Ti, Te, Vi, and Flux) in statement, so
now I just need to start it for each altitude and each time. Below is
roughly what I want to do. (no particular language's syntax, but $ in
front of variables to make them easier to pick out.)

MadLyr is tables name
Ne, Ti, Te, Vi, Flux, Alt, Time, Monthday are all fields in table

for $hour =1:23
for $altitude =100:500:100
select count(*), avg(Pow(10,Ne)), avg(Ti), avg(Te), avg(Vi)
avg(Flux)
from MadLyr where MonthDay>=505 and MonthDay<805 and
Time>=$hour
and Time<($hour+1) and Alt>=$altitude and Alt<($altitude
+100)
print results to file
endfor
endfor

This should give me average values of my 5 parameters with 1 hour and
100km resolution during summer months (I have to do equinox and winter
conditions as well, but summer is easiest to read).

I hope this is enough to let you know what I need to do. I appreciate
your help.
Karen

[Karen]

Actually I messed that up some, it should read:

for $hour =1:23
for $altitude =100:500:50
select count(*), avg(Pow(10,Ne)), avg(Ti), avg(Te), avg(Vi)
avg(Flux)from MadLyr where MonthDay>=505 and MonthDay<805
and Time>=$hour and Time<($hour+1) and Alt>=$altitude and
Alt<($altitude+50)
print results to file
endfor
for $altitude =500:800:100
select count(*), avg(Pow(10,Ne)), avg(Ti), avg(Te), avg(Vi)
avg(Flux)from MadLyr where MonthDay>=505 and MonthDay<805
and Time>=$hour and Time<($hour+1) and Alt>=$altitude and
Alt<($altitude+100)
print results to file
endfor
endfor

This gives the 288 queries per graph I mentioned earlier.