Query

[jotb]

Hello All:

I created a query in design view. I used the following fields: Date,DST,DIFFB,DIFFA1 and [DIFFB]-[DIFFA1]. I typed in a specific date and DST in the criteria. I ran the query and the new field expr1 calculated the differences using that expression [DIFFB]-[DIFFA1]. There were 5 records for that date. My question is, how would I now sum expr1 and divide by the 5 records?

Thank you,
Joe

 

[GingerR]

you can do that type of functionality in a TOTALS query.
on the menu, choose VIEW+TOTALS.
in your DiffA-Diffb field, choose AVG in the TOTAL row in the query grid.
you will have to omit the fields DIFFA and DIFFB from your query results, otherwise the GROUP BY in those fields will mess up your results.
you can see all the other functions available in a Totals Query: sum, max, min, etc. You can look up details in HELP.

g

 

[jotb]

Hello Ginger:

You are a life-saver! I omitted the DIFFB and DIFFA1 from the query and yes, I retrieved the results that I was seeking.

How could I group certain distances in the distance field? I ran the query but in the distance field, I typed in 6.0. The results were perfect. Then I added other distances as well and that worked fine. What if I want to group 6.0, 7.0 and 8.0 together and then another group for 8.5,9.0 and 10.0. Right now the query is giving me an average for each distance seperately. How can I group several distances together to obtain the same results.

Thank you in advance,
Joe

 

[GingerR]

i think in the criteria for DST just type

 6 or 7 or 8

and put WHERE in DST field, and uncheck the DST col so you dont see it (otherwise it will group on DST as it has been).

if your DST are text instead of numbers then put them like you have above, "6.0 or 7.0 or 8.0"

if they are numbers, you can always put for criteria:

Between 6 and 8

or

<=8

for one query and

>9

for the other. depends on what you're looking for.

 

[jotb]

Hello Ginger:

I changed the DST COL by using &quot;where&quot; instead of &quot;group by&quot; and unchecked the DST COL as you requested. In the criteria, I typed in <=8 and ran query. The calculation was perfect. One small problem! How can I run the query at the same time for >=9 without affecting the <=8 query calculation? In other words, I would like to view both calculations at the same time if this is possible. I'm trying not to incorporate all distances into one calculation. Anything less than or equal to 8.0 is for sprint races and anything greater or equal to 9.0 are considered route races. They must not be treated the same otherwise they become &quot;oil and vinegar&quot;. By the way, the DST field is numbers rather than text. You have been a tremendous help to me and it is greatly appreciated. I hope that one day I can return the favor.

Thank you in advance,
Joe

 

[GingerR]

sure--in your query design, in the field DST, change DST to:

Race: IIf([DST]<=8,&quot;Sprint&quot;,&quot;Route&quot;)

now it will change the DST to a label of &quot;Sprint&quot; or &quot;Route&quot; (you can change these to whatever you wish), and it will group on that label instead of on each DST. i named this field 'Race' which you can also change.

how's that?

 

[jotb]

Hello Ginger:

What can I say? You are the best! Thank you for helping me understand somewhat this difficult language. Horse racing is my gift but computers are another story. At least I'm done with my first step towards developing my own &quot;speed figure ratings&quot;. The second step of this process will be another nightmare for me. I was wondering if you are interested in working with me on the rest of this project? Maybe we can speak further about the fee's you may charge. I can be reached at joeotb12@aol.com.

Thank you and best regards,
Joe