number query results

[Guest]

Hopefully I can explain this well enough, I have a database set up to track
product key attributes. Works great. I have a query that will pull the last
9 records where the line and product code match. This also works great. I
need to graph the last 8 hours of lets say "moisture" with the difference
between each hour also graphed (i.e. 8:00 = 4.5 and 9:00 = 5.0 I need to
graph a .5). I have a form to show the query results. My goal is to have an
unbound field that will "number" the query results in the form so that I can
return the value of "query record #8 moisture value" - "query record #7
moisture value". Am I going about this all wrong? All of the code that I
have been able to find gives me the same number in each record depending on
what query record I am in. Can anyone help?

 

[Guest]

Are you doing this so you can pull info out to create a graph in Excel or
similar?
Did you know that you can show a form or query in Pivot Chart View?
This will display a graph from the data in the form. In the Properties
window for the form/query, set Default View to Pivot Chart. You can place
your fields on the chart to give the desired result.

Is that what you are looking for?

Steve

 

[Guest]

I think I have seen this question answered at some time in the past in the
Queries newsgroup. I can't really remember how it is done. I dont' number
things to present to users. You can't depend on them always being the same
number and in my experience, this confuses users (particularly bean
counters). The expect #7 to always be #7.
Anyway, you may want to go to the Queries newsgroup and do a search there.

 

[Guest]

Hey! That works great for my graph! Thanks!

I still don't see how to get the difference between each data point or the
"range" of the numbers for each check. It's a statistical analysis graph
that I need to tell me if our products are out of control. (i.e. the
difference between 7:00 moisture and 8:00 moisture is too high.)

 

[Guest]

OK. So you have a query that pulls the values for each time check?

In another query, you can calculate the difference between each value by
subtracting or adding one from another. Let's say your query returns the
values with field names of T1, T2, T3 and so on...

So, if T1 is less than T2, you want to subtract T1 from T2 to show a rise in
temp. If T2 is less than T1, you still want to subtract T1 from T2 to show a
fall in temp (negative value). If they are equal, return 0.

Your query fields will look like:
Field1: IIf(T1 < T2 Or T1 > T2, T2-T1, 0)
Field2: IIf(T2 < T3 Or T2 > T3, T3-T2, 0)
....and so on...

You will be able to create 8 values from the 9 pulled in your query using
this method. Try it out. Without seeing your data it is difficult to imagine
exactly what you need...

Steve