THIS IS A TOUGH ONE

[jotb]

Hello ALL:

I was wondering if it's possible to query the following and if yes, what type of formula is needed?

Here is an example of my database fields and type of data


DATE (FROM 06-DEC-00 TO 20-APR-03) Each date has up to records.

TRK Text AQU,BEL,SAR)
SUR Text (M,ID)
SEX Text (M,F)
AGE Text (2YO,3YO,3UP)
DST Number(4.5,5.0.5.5,6.0,6.5,7.0,8.0,8.42,8.5,9.0)
PURSE Number (ANYWHERE FROM 10000 TO 90000)
1FR Number (ANYWHERE FROM 2160 TO 7520)
1FRB Number (ANYWHERE FROM 2154 TO 7500)
DIFFB Number (ANYWHERE FROM 0 to 600)
AVGFF Number (ANYWHERE FROM 2200 TO 7500)
DIFFA1 Number (ANYWHERE FROM 0 to 600)
2FR Number (SAME TYPE OF DATA AS 1FR)
2FTB Number (SAME TYPE OF DATA AS 1FRB)
DIFFB2 Number (SAME TYPE OF DATA AS DIFFB)
AVGSF Number (SAME TYPE OF DATA AS AVGFF)
DIFFA2 Number (SAME TYPE OF DATA AS DIFFA1)
FT Number (SAME TYPE OF DATA AS 1FR)
FTB Number (SAME TYPE OF DATA AS 1FRB)
DIFFB3 Number (SAME TYPE OF DATA AS DIFFB)
AVGFT Number (SAME TYPE OF DATA AS AVGFF)
DIFFA3 Number (SAME TYPE OF DATA AS DIFFA1)

Here are 9 sample rows of the fields of interest I need a formula for from left to right.

DATE DST DIFFB DIFFA1 DIFFB2 DIFFA2 DIFFB3 DIFFA3
04-APR-03 6.0 85 118 248 265 223 298
04-APR-03 7.0 217 118 326 265 508 316
04-APR-03 7.0 131 118 225 265 145 316
04-APR-03 7.0 34 118 122 265 213 316
04-APR-03 7.0 136 118 205 265 42 316
04-APR-03 8.0 155 233 210 360 250 466
04-APR-03 8.0 176 233 305 360 297 466
04-APR-03 9.0 115 263 196 319 182 380
04-APR-03 9.0 84 263 108 319 195 380
04-APR-03 9.0 140 263 130 319 74 380

If I sum the DIFFB (85 down to 140) and divide by 10 it comes out to 127.3. If I sum DIFFA1 (118 TO 263) It comes out to 184.5. Subtract 127.3 from 184.5 and arrive at 57.2.
Now I need to do the same for DIFFB2 with DIFFA2. Last would be DIFFB3 with DIFFA3. As you can see, I want to focus on 2 fields at a time. (DIFFB,DIFFA1) (DIFFB2,DIFFA2) (DIFFB3,DIFFA3)

Like I said earlier, each date has a certain amount of records, anywhere from 5 to 10. I need to calculate the above for each day seperately. Lastly, if there is a way to perform these calculations, would it be possible to seperate certain distances within the same day. For example, on 04-apr-03 there were 7 records for less than 9.0 and 3 records above 8.0. I consider any distance under 9.0 as a sprint and any distance above 8.0 as a route. How can I add the first 7 in the DIFFB field and divide by 7 and then take the 3 at 9.0 in the DIFFB field add them together and divide by 3. Of course the same needs to be done to the field DIFFA1 and finally subtract DIFFB (FIRST 7) from the field DIFFA1 (FIRST 7).

I hope that someone understands this long,long, message. Please help if possible.

Thank you and best regards to everyone.
Joe

 

[EasyTarget]

Most likely the best approach is the following:

Build a simple select query where you are only pulling one or more day's worth of records.

I then would use the qry to build a report that shows the detail of the records. In the group footing for the day instead of using just the simple Sum function on the fields, I would build a custom function to do the calculation using a couple of Public variables to hold the values.

 

[jotb]

Hello:

I'm sorry but, is it possible for you to provide me with step by step instructions. My experience with query functions is limited. Please help me with this.

Regards,
Joe

 

[jotb]

Hello All:

Please provide me with step by step instructions.

Thank you in advance,
Joe

 

[gol4]

Joe'
If I understand what you want I think it can be done in a query. Using that avg() function and then grouping by date and dist

SELECT mytable.date,dist, Avg([diffb])-Avg([diffa]) AS Expr1
,Avg([diffb1])-Avg([diffa1]) AS Expr2,Avg([diffb3])-Avg([diffa3]) AS Expr3
FROM mytable
GROUP BY mytable.occ_date,dist;

the above should be close after you clean up the field and table names

 

[jotb]

Hello GOL4:

Thank you for the taking the time to help.

Best regards,
Joe