How to create query for difference between values

[CC Brown]

In col 1 there is the rank rPfig and in col2 is the raw number nPfig. I want to do query the difference between the raw numbers of the horse Ranked 1 and the horse ranked 2 . I tried to put it in the criteria box under the nPfig col but it doesn't work. Here is what I tried: (([rPfig]=1)-([rPfig]=2))>4

When I try this under the rPfig col it doesn't work either.

It doesn't give me errors it just doesn't do what I want. I want the result to have only horses/races where the difference in the nPfig is greater than 4.

I thought my daughter could help since she works with Access at work. I described it as Salary Rank in col1 and Salary Amt in col2. I just want to see when the difference of 1 and 2 is X.

I didn't want her think me a horse racing degenerate!! Ha

[Rick]

This is how I would handle it.

You need to do another query that isolates the top figure in every race. What you would end up with is a new column that has the top figure for the race listed for every horse. This query is added to your first query and you have to create a relationship between date, track and race.

I will try to email you the instrctions. The instructions were done for someone that used a different program than HTR so you have to translate the info to fit HTR. That just means replacing field names.

[CC Brown]

I got the instructions I will try tonight. Thanks, someday I will be able to answer some quetions! I can hardlly wait!! within 10-12 years.

[CC Brown]

I got your instructions but when I put Max on the Total line of the value I want to isolate it did not work. I was expecting to see the high value for that race. Example value are 9,7,5,6,4,7. I then expected to see a 9 in that col for that race. What I saw was the same values 9,7,5,6,4,7.
I create the query then run it to check if it has been done correctly and it appears nothing has happened by using the Max function. I also tried it on several other fields- it didn't work there either. Then I tried Min and that also didn't work.
So I could never get to the second part of tying this query back to table and looking at the differences or gap of that factor from the high.

Can you offer any advice

[CC Brown]

Maybe I created the wrong relationship between my query and table. I put trk,date,race and prg#. Your instructions don't include prg #. Let me see what happens! No still no good.

[Rick]

You are trying to use too many fields. In this case you only need four fields, date, track, race and nPfig. And put them in that order.

On the totals line date, track and race are Group By. nPfig is set to Min on totals line.

Save and run.

You should have one line for each race.