RANKING

[Guest]

Hi Tom
Im working on an answer that i hope will help. It will take me some time as
Im not a gifted writer. I know you know what your doing. I just have to
explain myself better. I will go over your letter very thoroughly. I think I
might be able to clear things up a lot faster if I could talk to you in
person. I would give you my phone number and you could call me collect. That
way you could be assured I would never bother you. If you have time to do
this let me know when you might call. Im on EST.. If you preferr not to call
me, I will understand and try and explain myself better. I know once you have
the facts, you can answer my question.
Thanks for all the help. Im thankfull you havent given up on me.
Ed

Dear Gambler:

It would appear as though the query is reporting on data for the
columns:

tTRK = "LS"
tDATE = <don't know>
nRACE = 1

where there are more than 14 rows in your table for the specific race
in question. It was my understanding that for a single set of values
for the 3 columns above, all the data is then for just one race. In
addition, I'm thinking that the 14 rows you expect for this race are
for 14 different entrants in that race.

However, I do not have this data. You do. I do not know how it is
organized and entered. But, based on your query, I can make some
observations.

Your WHERE clause filters by nRACE and xPL. Your definition of "a
race" which you gave me is that a race is all the rows with the same
tTRK, tDATE, and nRACE values. Your filter is then apparently not
just for one race. It is not specific enough, in that it does not
filter for tTRK and nRACE. However, it is also more specific than
just one race, in that it filters for xPL = 5 (fifth place finishers?)

It would be useful to know how many rows you have in your table for
the specific tTRK/tDATE/nRACE combination LS/somedate/1 which
represents this race. I have the expectation that there are not just
14 rows in the table for this.

I observe that, in this table, you have information about a race, such
as nDIST (distance run?) which is information about one race and
information about one entrant such as nTRNITM (time to turn 1?) and
nJKYITM (something about the jockey?). This appears to be a problem
in terms of the way the tables are designed.

For example, if you have 14 rows for 14 entrants in a race, are you
going to enter the distance run 14 times, and make sure they are all
exactly identical? Actually, there should be no need to enter the
distance run more than once. There should be a separate table
describing the parameters of the race, one row per race, and a table
of the entrants in that race, on row per entrant per race.

Not knowing what you really have, and just now beginning to try to
make some deductions about it, I am just reacting to a concern I have
about the foundation of things on which we're working.

All this aside for the moment, how many rows are there in the table
for the one specific race with which we're concerned at the moment?
Are these all the rows where tTRK = "LS", tDATE = #1/1/04# (Please
substitute the actual value for the date of that race), and nRACE = 1?
To find out, please run this query:

SELECT COUNT(*) AS RaceRowCount
FROM ALL_HX4
WHERE tTRK = "LS" AND tDATE = #1/1/04# AND nRACE = 1

In the above, substitute the actual date of the race in question.

Are there really only 14 rows in the table for this?

I would like to point out that what the query you and I wrote for this
is doing is this: It is ranking the rows that have the same values of
tTRK/tDATE/nRACE. If there is exactly one row per horse, then it will
rank horses. If not, then it is ranking the rows it finds in the
table, and thereby ranking whatever it is those rows represent.

Now maybe I've completely missed something here. I can only go by
what you tell me, and that may certainly leave a lot for you to figure
out for yourself. I'm trying to give you the clues to help you
determine what that may be. I'm trying to be as specific as possible,
and I'm trying to get inside the thing and tell you what to look for,
and what assumptions I may have made so far that could be the source
of problems. Obviously, this leaves a lot up to you (which, for free
help, is only fair!)

So, I'm going to have to leave it up to you now to try to clue me in
on what is likely causing the problem. I can only hope I've given you
some clues as to how to work it out. Good luck.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.nRACE)=1) AND ((T.xPL)=5));
The above SQL is one race at LS. It is a Breeders Cup Race. There are 14
horses in this race. The EXPR1 numbers go from 81 to 146. I would like to see
the ranking say the 146 is number1 and the next 146 would also be number1.
Then the third highest ranking EXPR1 136 would get a ranking of number3 and
the fourth EXPR1. 134 would get a ranking of number 4. If I can obtain this
then I can put say 100 races in one query and determine how many times the
horse that won had an expr1 rating from say 1 to 4.
I appreciate your patience and I know you know what your doing, Im just
having hard time explaining to you just what I need. I hope the above
explination will help. If not Ill keep trying.
Thanks for helping
ed

Dear Gambler:

I cannot see what kind of results you received, nor how they were
different from what is disired. Could you fill me in so I can try to
help some more?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 17:19:02 -0700, "gambler"

Hi Tom
I tried what you sent me and I got a syntax error code. I believe I found
the problem. In the row that starts out with WHERE there needed to be a space
between and and T1. Once I changed this the error went away. But the ranking
isnt correct. I sent you a SQL of one race. The rank should of went from 1-10
to I think there was 10 horses in that race. Im I correct? If you need more
info let me know.
Thanks for helping
ed
SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#10/22/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));

:

Dear Gambler:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK ANDT1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 27 Oct 2004 13:43:02 -0700, "gambler"

Hi Tom
The reason I didnt get back to you sooner was internet connection was down.
A race day is defined by the TRACK (tTRK) then the DATE ( tDATE) then the
RACE NUMBER (nRACE). There are anywhere from 8 to say 12 races on that date
at that track. They are numbered from 1 for the first race to 8 or 12
depending how many races there are that day. I wasnt sure where to add the
above defination of a race.
Sure appreciate your patients and help.
Thanks again
ed

:

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 17:05:07 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Guest]

Hi Tom
Here is what I do.
1. I download say 30 days of racing for a particular track. I will say MNR
Mountineer.
2. This download goes into their program.
3.I then export this download over into a table in Access 2000
4. This table may contain 50+ fields of information about each horse in each
race.
5. I then make up a query. In that Query I need to choose a TRACK (only if I
downloaded many different tracks. If I only downloaded races from one track
then I don’t need to choose a track in my query.
DATE- I don’t have to choose a date because I want to look at the winners in
all the races.
DIST- this lets me choose a distance I want to look at today. I choose to
look at all the distances.
SURF- this lets me choose the surface of the races I want stats on. It is
normally D for dirt.
CLA- this let me look a different type of races.
HOR- I really don’t need this. I just use it to make sure I’m not looking at
the same race twice.
XFIN-I put a one here, because I only want to look at the horses that won
the race.
XPL-I can put 1-5 in here. This will cause some of my field numbers to be
different.
EXPR1- this is the formula I want to rank. If I don’t rank it, its numbers
are mean less because I don’t know if they were in the top or bottom of that
field of horses.

So in this query I am going to do stats on my table that contains all the
races I downloaded. I only want to look at those horses who finished number
1. ( xFIN—1) on a dirt races ( surf—D) and were running in a Non Maiden race
(Not*M*). The data used to calculate EXPR1 come from xPL 5.

Now I believe I see the problem why the query doesn’t work. Some how I
would have to have EXPR1 numbers that belong to each race in the table ranked
inside the table before I could use them in a query. I don’t think this is
possible. Lets say on 10/22/2004---Race1—there are 10 horses with their own
ITM numbers. I would have to perform my EXPR1 equation with these numbers
within the table and them rank them while their still in the table before I
could use it in the query. I don’t think this can be done. Do you understand
what Im saying and do you agree?

Hi Tom
Im working on an answer that i hope will help. It will take me some time as
Im not a gifted writer. I know you know what your doing. I just have to
explain myself better. I will go over your letter very thoroughly. I think I
might be able to clear things up a lot faster if I could talk to you in
person. I would give you my phone number and you could call me collect. That
way you could be assured I would never bother you. If you have time to do
this let me know when you might call. Im on EST.. If you preferr not to call
me, I will understand and try and explain myself better. I know once you have
the facts, you can answer my question.
Thanks for all the help. Im thankfull you havent given up on me.
Ed

Dear Gambler:

It would appear as though the query is reporting on data for the
columns:

tTRK = "LS"
tDATE = <don't know>
nRACE = 1

where there are more than 14 rows in your table for the specific race
in question. It was my understanding that for a single set of values
for the 3 columns above, all the data is then for just one race. In
addition, I'm thinking that the 14 rows you expect for this race are
for 14 different entrants in that race.

However, I do not have this data. You do. I do not know how it is
organized and entered. But, based on your query, I can make some
observations.

Your WHERE clause filters by nRACE and xPL. Your definition of "a
race" which you gave me is that a race is all the rows with the same
tTRK, tDATE, and nRACE values. Your filter is then apparently not
just for one race. It is not specific enough, in that it does not
filter for tTRK and nRACE. However, it is also more specific than
just one race, in that it filters for xPL = 5 (fifth place finishers?)

It would be useful to know how many rows you have in your table for
the specific tTRK/tDATE/nRACE combination LS/somedate/1 which
represents this race. I have the expectation that there are not just
14 rows in the table for this.

I observe that, in this table, you have information about a race, such
as nDIST (distance run?) which is information about one race and
information about one entrant such as nTRNITM (time to turn 1?) and
nJKYITM (something about the jockey?). This appears to be a problem
in terms of the way the tables are designed.

For example, if you have 14 rows for 14 entrants in a race, are you
going to enter the distance run 14 times, and make sure they are all
exactly identical? Actually, there should be no need to enter the
distance run more than once. There should be a separate table
describing the parameters of the race, one row per race, and a table
of the entrants in that race, on row per entrant per race.

Not knowing what you really have, and just now beginning to try to
make some deductions about it, I am just reacting to a concern I have
about the foundation of things on which we're working.

All this aside for the moment, how many rows are there in the table
for the one specific race with which we're concerned at the moment?
Are these all the rows where tTRK = "LS", tDATE = #1/1/04# (Please
substitute the actual value for the date of that race), and nRACE = 1?
To find out, please run this query:

SELECT COUNT(*) AS RaceRowCount
FROM ALL_HX4
WHERE tTRK = "LS" AND tDATE = #1/1/04# AND nRACE = 1

In the above, substitute the actual date of the race in question.

Are there really only 14 rows in the table for this?

I would like to point out that what the query you and I wrote for this
is doing is this: It is ranking the rows that have the same values of
tTRK/tDATE/nRACE. If there is exactly one row per horse, then it will
rank horses. If not, then it is ranking the rows it finds in the
table, and thereby ranking whatever it is those rows represent.

Now maybe I've completely missed something here. I can only go by
what you tell me, and that may certainly leave a lot for you to figure
out for yourself. I'm trying to give you the clues to help you
determine what that may be. I'm trying to be as specific as possible,
and I'm trying to get inside the thing and tell you what to look for,
and what assumptions I may have made so far that could be the source
of problems. Obviously, this leaves a lot up to you (which, for free
help, is only fair!)

So, I'm going to have to leave it up to you now to try to clue me in
on what is likely causing the problem. I can only hope I've given you
some clues as to how to work it out. Good luck.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.nRACE)=1) AND ((T.xPL)=5));
The above SQL is one race at LS. It is a Breeders Cup Race. There are 14
horses in this race. The EXPR1 numbers go from 81 to 146. I would like to see
the ranking say the 146 is number1 and the next 146 would also be number1.
Then the third highest ranking EXPR1 136 would get a ranking of number3 and
the fourth EXPR1. 134 would get a ranking of number 4. If I can obtain this
then I can put say 100 races in one query and determine how many times the
horse that won had an expr1 rating from say 1 to 4.
I appreciate your patience and I know you know what your doing, Im just
having hard time explaining to you just what I need. I hope the above
explination will help. If not Ill keep trying.
Thanks for helping
ed

:

Dear Gambler:

I cannot see what kind of results you received, nor how they were
different from what is disired. Could you fill me in so I can try to
help some more?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 17:19:02 -0700, "gambler"

Hi Tom
I tried what you sent me and I got a syntax error code. I believe I found
the problem. In the row that starts out with WHERE there needed to be a space
between and and T1. Once I changed this the error went away. But the ranking
isnt correct. I sent you a SQL of one race. The rank should of went from 1-10
to I think there was 10 horses in that race. Im I correct? If you need more
info let me know.
Thanks for helping
ed
SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#10/22/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));

:

Dear Gambler:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK ANDT1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 27 Oct 2004 13:43:02 -0700, "gambler"

Hi Tom
The reason I didnt get back to you sooner was internet connection was down.
A race day is defined by the TRACK (tTRK) then the DATE ( tDATE) then the
RACE NUMBER (nRACE). There are anywhere from 8 to say 12 races on that date
at that track. They are numbered from 1 for the first race to 8 or 12
depending how many races there are that day. I wasnt sure where to add the
above defination of a race.
Sure appreciate your patients and help.
Thanks again
ed

:

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 17:05:07 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Tom Ellison]

Dear Gambler:

One thing I need to know is this: what is the entity represented by
each row of your table ALL_HX4? Is each row one race? Is each row
one horse in one race?

Next, what set of common value in a column, or a set of columns,
represents a single race? That is, what is the key to one race?

Please write a query that counts the number of horses recorded for
each race.

You need to describe these fundamental attributes of your design.

The kind of results you report receiving have me baffled just what
your design is. Without the knowledge of some structure behind this,
I cannot realy give any more help, and what I have tried to do to
assist so far has been frustrated.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
Here is what I do.
1. I download say 30 days of racing for a particular track. I will say MNR
Mountineer.
2. This download goes into their program.
3.I then export this download over into a table in Access 2000
4. This table may contain 50+ fields of information about each horse in each
race.
5. I then make up a query. In that Query I need to choose a TRACK (only if I
downloaded many different tracks. If I only downloaded races from one track
then I don’t need to choose a track in my query.
DATE- I don’t have to choose a date because I want to look at the winners in
all the races.
DIST- this lets me choose a distance I want to look at today. I choose to
look at all the distances.
SURF- this lets me choose the surface of the races I want stats on. It is
normally D for dirt.
CLA- this let me look a different type of races.
HOR- I really don’t need this. I just use it to make sure I’m not looking at
the same race twice.
XFIN-I put a one here, because I only want to look at the horses that won
the race.
XPL-I can put 1-5 in here. This will cause some of my field numbers to be
different.
EXPR1- this is the formula I want to rank. If I don’t rank it, its numbers
are mean less because I don’t know if they were in the top or bottom of that
field of horses.

So in this query I am going to do stats on my table that contains all the
races I downloaded. I only want to look at those horses who finished number
1. ( xFIN—1) on a dirt races ( surf—D) and were running in a Non Maiden race
(Not*M*). The data used to calculate EXPR1 come from xPL 5.

Now I believe I see the problem why the query doesn’t work. Some how I
would have to have EXPR1 numbers that belong to each race in the table ranked
inside the table before I could use them in a query. I don’t think this is
possible. Lets say on 10/22/2004---Race1—there are 10 horses with their own
ITM numbers. I would have to perform my EXPR1 equation with these numbers
within the table and them rank them while their still in the table before I
could use it in the query. I don’t think this can be done. Do you understand
what Im saying and do you agree?

Hi Tom
Im working on an answer that i hope will help. It will take me some time as
Im not a gifted writer. I know you know what your doing. I just have to
explain myself better. I will go over your letter very thoroughly. I think I
might be able to clear things up a lot faster if I could talk to you in
person. I would give you my phone number and you could call me collect. That
way you could be assured I would never bother you. If you have time to do
this let me know when you might call. Im on EST.. If you preferr not to call
me, I will understand and try and explain myself better. I know once you have
the facts, you can answer my question.
Thanks for all the help. Im thankfull you havent given up on me.
Ed

Dear Gambler:

It would appear as though the query is reporting on data for the
columns:

tTRK = "LS"
tDATE = <don't know>
nRACE = 1

where there are more than 14 rows in your table for the specific race
in question. It was my understanding that for a single set of values
for the 3 columns above, all the data is then for just one race. In
addition, I'm thinking that the 14 rows you expect for this race are
for 14 different entrants in that race.

However, I do not have this data. You do. I do not know how it is
organized and entered. But, based on your query, I can make some
observations.

Your WHERE clause filters by nRACE and xPL. Your definition of "a
race" which you gave me is that a race is all the rows with the same
tTRK, tDATE, and nRACE values. Your filter is then apparently not
just for one race. It is not specific enough, in that it does not
filter for tTRK and nRACE. However, it is also more specific than
just one race, in that it filters for xPL = 5 (fifth place finishers?)

It would be useful to know how many rows you have in your table for
the specific tTRK/tDATE/nRACE combination LS/somedate/1 which
represents this race. I have the expectation that there are not just
14 rows in the table for this.

I observe that, in this table, you have information about a race, such
as nDIST (distance run?) which is information about one race and
information about one entrant such as nTRNITM (time to turn 1?) and
nJKYITM (something about the jockey?). This appears to be a problem
in terms of the way the tables are designed.

For example, if you have 14 rows for 14 entrants in a race, are you
going to enter the distance run 14 times, and make sure they are all
exactly identical? Actually, there should be no need to enter the
distance run more than once. There should be a separate table
describing the parameters of the race, one row per race, and a table
of the entrants in that race, on row per entrant per race.

Not knowing what you really have, and just now beginning to try to
make some deductions about it, I am just reacting to a concern I have
about the foundation of things on which we're working.

All this aside for the moment, how many rows are there in the table
for the one specific race with which we're concerned at the moment?
Are these all the rows where tTRK = "LS", tDATE = #1/1/04# (Please
substitute the actual value for the date of that race), and nRACE = 1?
To find out, please run this query:

SELECT COUNT(*) AS RaceRowCount
FROM ALL_HX4
WHERE tTRK = "LS" AND tDATE = #1/1/04# AND nRACE = 1

In the above, substitute the actual date of the race in question.

Are there really only 14 rows in the table for this?

I would like to point out that what the query you and I wrote for this
is doing is this: It is ranking the rows that have the same values of
tTRK/tDATE/nRACE. If there is exactly one row per horse, then it will
rank horses. If not, then it is ranking the rows it finds in the
table, and thereby ranking whatever it is those rows represent.

Now maybe I've completely missed something here. I can only go by
what you tell me, and that may certainly leave a lot for you to figure
out for yourself. I'm trying to give you the clues to help you
determine what that may be. I'm trying to be as specific as possible,
and I'm trying to get inside the thing and tell you what to look for,
and what assumptions I may have made so far that could be the source
of problems. Obviously, this leaves a lot up to you (which, for free
help, is only fair!)

So, I'm going to have to leave it up to you now to try to clue me in
on what is likely causing the problem. I can only hope I've given you
some clues as to how to work it out. Good luck.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 18:31:04 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.nRACE)=1) AND ((T.xPL)=5));
The above SQL is one race at LS. It is a Breeders Cup Race. There are 14
horses in this race. The EXPR1 numbers go from 81 to 146. I would like to see
the ranking say the 146 is number1 and the next 146 would also be number1.
Then the third highest ranking EXPR1 136 would get a ranking of number3 and
the fourth EXPR1. 134 would get a ranking of number 4. If I can obtain this
then I can put say 100 races in one query and determine how many times the
horse that won had an expr1 rating from say 1 to 4.
I appreciate your patience and I know you know what your doing, Im just
having hard time explaining to you just what I need. I hope the above
explination will help. If not Ill keep trying.
Thanks for helping
ed

:

Dear Gambler:

I cannot see what kind of results you received, nor how they were
different from what is disired. Could you fill me in so I can try to
help some more?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 17:19:02 -0700, "gambler"

Hi Tom
I tried what you sent me and I got a syntax error code. I believe I found
the problem. In the row that starts out with WHERE there needed to be a space
between and and T1. Once I changed this the error went away. But the ranking
isnt correct. I sent you a SQL of one race. The rank should of went from 1-10
to I think there was 10 horses in that race. Im I correct? If you need more
info let me know.
Thanks for helping
ed
SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#10/22/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));

:

Dear Gambler:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK ANDT1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 27 Oct 2004 13:43:02 -0700, "gambler"

Hi Tom
The reason I didnt get back to you sooner was internet connection was down.
A race day is defined by the TRACK (tTRK) then the DATE ( tDATE) then the
RACE NUMBER (nRACE). There are anywhere from 8 to say 12 races on that date
at that track. They are numbered from 1 for the first race to 8 or 12
depending how many races there are that day. I wasnt sure where to add the
above defination of a race.
Sure appreciate your patients and help.
Thanks again
ed

:

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 17:05:07 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Guest]

Hi Tom
I sure appreciated your help. I realize I havent given you enough info to
solve my problem and you are becoming flustrated. The last thing I want to do
is cause you flustrarion because you have help me much more than one could
ask for. So that I dont cause you anymore flustration I have two soultions
1. We stop here and I thank you for all your help. I believe you could solve
my problem if I was smart enough to be able to give you the correct info.
2. I would send you my phone number and you could call me collect. Maybe
talking to me while Im on my computer would clear up some problems. If
calling is a problem I will understand.

Bottom line is I sure apprecaited all the effort you put forth trying to
help me and I dont want you to get so flustrated that if sometime in the
future I may have simple question and you choose not to answer it because of
your past flustratuion with me.

Thanks for all the help
ed

Dear Gambler:

One thing I need to know is this: what is the entity represented by
each row of your table ALL_HX4? Is each row one race? Is each row
one horse in one race?

Next, what set of common value in a column, or a set of columns,
represents a single race? That is, what is the key to one race?

Please write a query that counts the number of horses recorded for
each race.

You need to describe these fundamental attributes of your design.

The kind of results you report receiving have me baffled just what
your design is. Without the knowledge of some structure behind this,
I cannot realy give any more help, and what I have tried to do to
assist so far has been frustrated.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
Here is what I do.
1. I download say 30 days of racing for a particular track. I will say MNR
Mountineer.
2. This download goes into their program.
3.I then export this download over into a table in Access 2000
4. This table may contain 50+ fields of information about each horse in each
race.
5. I then make up a query. In that Query I need to choose a TRACK (only if I
downloaded many different tracks. If I only downloaded races from one track
then I don’t need to choose a track in my query.
DATE- I don’t have to choose a date because I want to look at the winners in
all the races.
DIST- this lets me choose a distance I want to look at today. I choose to
look at all the distances.
SURF- this lets me choose the surface of the races I want stats on. It is
normally D for dirt.
CLA- this let me look a different type of races.
HOR- I really don’t need this. I just use it to make sure I’m not looking at
the same race twice.
XFIN-I put a one here, because I only want to look at the horses that won
the race.
XPL-I can put 1-5 in here. This will cause some of my field numbers to be
different.
EXPR1- this is the formula I want to rank. If I don’t rank it, its numbers
are mean less because I don’t know if they were in the top or bottom of that
field of horses.

So in this query I am going to do stats on my table that contains all the
races I downloaded. I only want to look at those horses who finished number
1. ( xFIN—1) on a dirt races ( surf—D) and were running in a Non Maiden race
(Not*M*). The data used to calculate EXPR1 come from xPL 5.

Now I believe I see the problem why the query doesn’t work. Some how I
would have to have EXPR1 numbers that belong to each race in the table ranked
inside the table before I could use them in a query. I don’t think this is
possible. Lets say on 10/22/2004---Race1—there are 10 horses with their own
ITM numbers. I would have to perform my EXPR1 equation with these numbers
within the table and them rank them while their still in the table before I
could use it in the query. I don’t think this can be done. Do you understand
what Im saying and do you agree?

Hi Tom
Im working on an answer that i hope will help. It will take me some time as
Im not a gifted writer. I know you know what your doing. I just have to
explain myself better. I will go over your letter very thoroughly. I think I
might be able to clear things up a lot faster if I could talk to you in
person. I would give you my phone number and you could call me collect. That
way you could be assured I would never bother you. If you have time to do
this let me know when you might call. Im on EST.. If you preferr not to call
me, I will understand and try and explain myself better. I know once you have
the facts, you can answer my question.
Thanks for all the help. Im thankfull you havent given up on me.
Ed

:

Dear Gambler:

It would appear as though the query is reporting on data for the
columns:

tTRK = "LS"
tDATE = <don't know>
nRACE = 1

where there are more than 14 rows in your table for the specific race
in question. It was my understanding that for a single set of values
for the 3 columns above, all the data is then for just one race. In
addition, I'm thinking that the 14 rows you expect for this race are
for 14 different entrants in that race.

However, I do not have this data. You do. I do not know how it is
organized and entered. But, based on your query, I can make some
observations.

Your WHERE clause filters by nRACE and xPL. Your definition of "a
race" which you gave me is that a race is all the rows with the same
tTRK, tDATE, and nRACE values. Your filter is then apparently not
just for one race. It is not specific enough, in that it does not
filter for tTRK and nRACE. However, it is also more specific than
just one race, in that it filters for xPL = 5 (fifth place finishers?)

It would be useful to know how many rows you have in your table for
the specific tTRK/tDATE/nRACE combination LS/somedate/1 which
represents this race. I have the expectation that there are not just
14 rows in the table for this.

I observe that, in this table, you have information about a race, such
as nDIST (distance run?) which is information about one race and
information about one entrant such as nTRNITM (time to turn 1?) and
nJKYITM (something about the jockey?). This appears to be a problem
in terms of the way the tables are designed.

For example, if you have 14 rows for 14 entrants in a race, are you
going to enter the distance run 14 times, and make sure they are all
exactly identical? Actually, there should be no need to enter the
distance run more than once. There should be a separate table
describing the parameters of the race, one row per race, and a table
of the entrants in that race, on row per entrant per race.

Not knowing what you really have, and just now beginning to try to
make some deductions about it, I am just reacting to a concern I have
about the foundation of things on which we're working.

All this aside for the moment, how many rows are there in the table
for the one specific race with which we're concerned at the moment?
Are these all the rows where tTRK = "LS", tDATE = #1/1/04# (Please
substitute the actual value for the date of that race), and nRACE = 1?
To find out, please run this query:

SELECT COUNT(*) AS RaceRowCount
FROM ALL_HX4
WHERE tTRK = "LS" AND tDATE = #1/1/04# AND nRACE = 1

In the above, substitute the actual date of the race in question.

Are there really only 14 rows in the table for this?

I would like to point out that what the query you and I wrote for this
is doing is this: It is ranking the rows that have the same values of
tTRK/tDATE/nRACE. If there is exactly one row per horse, then it will
rank horses. If not, then it is ranking the rows it finds in the
table, and thereby ranking whatever it is those rows represent.

Now maybe I've completely missed something here. I can only go by
what you tell me, and that may certainly leave a lot for you to figure
out for yourself. I'm trying to give you the clues to help you
determine what that may be. I'm trying to be as specific as possible,
and I'm trying to get inside the thing and tell you what to look for,
and what assumptions I may have made so far that could be the source
of problems. Obviously, this leaves a lot up to you (which, for free
help, is only fair!)

So, I'm going to have to leave it up to you now to try to clue me in
on what is likely causing the problem. I can only hope I've given you
some clues as to how to work it out. Good luck.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 18:31:04 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.nRACE)=1) AND ((T.xPL)=5));
The above SQL is one race at LS. It is a Breeders Cup Race. There are 14
horses in this race. The EXPR1 numbers go from 81 to 146. I would like to see
the ranking say the 146 is number1 and the next 146 would also be number1.
Then the third highest ranking EXPR1 136 would get a ranking of number3 and
the fourth EXPR1. 134 would get a ranking of number 4. If I can obtain this
then I can put say 100 races in one query and determine how many times the
horse that won had an expr1 rating from say 1 to 4.
I appreciate your patience and I know you know what your doing, Im just
having hard time explaining to you just what I need. I hope the above
explination will help. If not Ill keep trying.
Thanks for helping
ed

:

Dear Gambler:

I cannot see what kind of results you received, nor how they were
different from what is disired. Could you fill me in so I can try to
help some more?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 17:19:02 -0700, "gambler"

Hi Tom
I tried what you sent me and I got a syntax error code. I believe I found
the problem. In the row that starts out with WHERE there needed to be a space
between and and T1. Once I changed this the error went away. But the ranking
isnt correct. I sent you a SQL of one race. The rank should of went from 1-10
to I think there was 10 horses in that race. Im I correct? If you need more
info let me know.
Thanks for helping
ed
SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#10/22/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));

:

Dear Gambler:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK ANDT1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 27 Oct 2004 13:43:02 -0700, "gambler"

Hi Tom
The reason I didnt get back to you sooner was internet connection was down.
A race day is defined by the TRACK (tTRK) then the DATE ( tDATE) then the
RACE NUMBER (nRACE). There are anywhere from 8 to say 12 races on that date
at that track. They are numbered from 1 for the first race to 8 or 12
depending how many races there are that day. I wasnt sure where to add the
above defination of a race.
Sure appreciate your patients and help.
Thanks again
ed

:

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 17:05:07 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Tom Ellison]

Please communicate next with me by email. Mine is
(e-mail address removed). Be sure you have a valid return email address
there or I cannot return it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
I sure appreciated your help. I realize I havent given you enough info to
solve my problem and you are becoming flustrated. The last thing I want to do
is cause you flustrarion because you have help me much more than one could
ask for. So that I dont cause you anymore flustration I have two soultions
1. We stop here and I thank you for all your help. I believe you could solve
my problem if I was smart enough to be able to give you the correct info.
2. I would send you my phone number and you could call me collect. Maybe
talking to me while Im on my computer would clear up some problems. If
calling is a problem I will understand.

Bottom line is I sure apprecaited all the effort you put forth trying to
help me and I dont want you to get so flustrated that if sometime in the
future I may have simple question and you choose not to answer it because of
your past flustratuion with me.

Thanks for all the help
ed

Dear Gambler:

One thing I need to know is this: what is the entity represented by
each row of your table ALL_HX4? Is each row one race? Is each row
one horse in one race?

Next, what set of common value in a column, or a set of columns,
represents a single race? That is, what is the key to one race?

Please write a query that counts the number of horses recorded for
each race.

You need to describe these fundamental attributes of your design.

The kind of results you report receiving have me baffled just what
your design is. Without the knowledge of some structure behind this,
I cannot realy give any more help, and what I have tried to do to
assist so far has been frustrated.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
Here is what I do.
1. I download say 30 days of racing for a particular track. I will say MNR
Mountineer.
2. This download goes into their program.
3.I then export this download over into a table in Access 2000
4. This table may contain 50+ fields of information about each horse in each
race.
5. I then make up a query. In that Query I need to choose a TRACK (only if I
downloaded many different tracks. If I only downloaded races from one track
then I don’t need to choose a track in my query.
DATE- I don’t have to choose a date because I want to look at the winners in
all the races.
DIST- this lets me choose a distance I want to look at today. I choose to
look at all the distances.
SURF- this lets me choose the surface of the races I want stats on. It is
normally D for dirt.
CLA- this let me look a different type of races.
HOR- I really don’t need this. I just use it to make sure I’m not looking at
the same race twice.
XFIN-I put a one here, because I only want to look at the horses that won
the race.
XPL-I can put 1-5 in here. This will cause some of my field numbers to be
different.
EXPR1- this is the formula I want to rank. If I don’t rank it, its numbers
are mean less because I don’t know if they were in the top or bottom of that
field of horses.

So in this query I am going to do stats on my table that contains all the
races I downloaded. I only want to look at those horses who finished number
1. ( xFIN—1) on a dirt races ( surf—D) and were running in a Non Maiden race
(Not*M*). The data used to calculate EXPR1 come from xPL 5.

Now I believe I see the problem why the query doesn’t work. Some how I
would have to have EXPR1 numbers that belong to each race in the table ranked
inside the table before I could use them in a query. I don’t think this is
possible. Lets say on 10/22/2004---Race1—there are 10 horses with their own
ITM numbers. I would have to perform my EXPR1 equation with these numbers
within the table and them rank them while their still in the table before I
could use it in the query. I don’t think this can be done. Do you understand
what Im saying and do you agree?


:

Hi Tom
Im working on an answer that i hope will help. It will take me some time as
Im not a gifted writer. I know you know what your doing. I just have to
explain myself better. I will go over your letter very thoroughly. I think I
might be able to clear things up a lot faster if I could talk to you in
person. I would give you my phone number and you could call me collect. That
way you could be assured I would never bother you. If you have time to do
this let me know when you might call. Im on EST.. If you preferr not to call
me, I will understand and try and explain myself better. I know once you have
the facts, you can answer my question.
Thanks for all the help. Im thankfull you havent given up on me.
Ed

:

Dear Gambler:

It would appear as though the query is reporting on data for the
columns:

tTRK = "LS"
tDATE = <don't know>
nRACE = 1

where there are more than 14 rows in your table for the specific race
in question. It was my understanding that for a single set of values
for the 3 columns above, all the data is then for just one race. In
addition, I'm thinking that the 14 rows you expect for this race are
for 14 different entrants in that race.

However, I do not have this data. You do. I do not know how it is
organized and entered. But, based on your query, I can make some
observations.

Your WHERE clause filters by nRACE and xPL. Your definition of "a
race" which you gave me is that a race is all the rows with the same
tTRK, tDATE, and nRACE values. Your filter is then apparently not
just for one race. It is not specific enough, in that it does not
filter for tTRK and nRACE. However, it is also more specific than
just one race, in that it filters for xPL = 5 (fifth place finishers?)

It would be useful to know how many rows you have in your table for
the specific tTRK/tDATE/nRACE combination LS/somedate/1 which
represents this race. I have the expectation that there are not just
14 rows in the table for this.

I observe that, in this table, you have information about a race, such
as nDIST (distance run?) which is information about one race and
information about one entrant such as nTRNITM (time to turn 1?) and
nJKYITM (something about the jockey?). This appears to be a problem
in terms of the way the tables are designed.

For example, if you have 14 rows for 14 entrants in a race, are you
going to enter the distance run 14 times, and make sure they are all
exactly identical? Actually, there should be no need to enter the
distance run more than once. There should be a separate table
describing the parameters of the race, one row per race, and a table
of the entrants in that race, on row per entrant per race.

Not knowing what you really have, and just now beginning to try to
make some deductions about it, I am just reacting to a concern I have
about the foundation of things on which we're working.

All this aside for the moment, how many rows are there in the table
for the one specific race with which we're concerned at the moment?
Are these all the rows where tTRK = "LS", tDATE = #1/1/04# (Please
substitute the actual value for the date of that race), and nRACE = 1?
To find out, please run this query:

SELECT COUNT(*) AS RaceRowCount
FROM ALL_HX4
WHERE tTRK = "LS" AND tDATE = #1/1/04# AND nRACE = 1

In the above, substitute the actual date of the race in question.

Are there really only 14 rows in the table for this?

I would like to point out that what the query you and I wrote for this
is doing is this: It is ranking the rows that have the same values of
tTRK/tDATE/nRACE. If there is exactly one row per horse, then it will
rank horses. If not, then it is ranking the rows it finds in the
table, and thereby ranking whatever it is those rows represent.

Now maybe I've completely missed something here. I can only go by
what you tell me, and that may certainly leave a lot for you to figure
out for yourself. I'm trying to give you the clues to help you
determine what that may be. I'm trying to be as specific as possible,
and I'm trying to get inside the thing and tell you what to look for,
and what assumptions I may have made so far that could be the source
of problems. Obviously, this leaves a lot up to you (which, for free
help, is only fair!)

So, I'm going to have to leave it up to you now to try to clue me in
on what is likely causing the problem. I can only hope I've given you
some clues as to how to work it out. Good luck.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 18:31:04 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.nRACE)=1) AND ((T.xPL)=5));
The above SQL is one race at LS. It is a Breeders Cup Race. There are 14
horses in this race. The EXPR1 numbers go from 81 to 146. I would like to see
the ranking say the 146 is number1 and the next 146 would also be number1.
Then the third highest ranking EXPR1 136 would get a ranking of number3 and
the fourth EXPR1. 134 would get a ranking of number 4. If I can obtain this
then I can put say 100 races in one query and determine how many times the
horse that won had an expr1 rating from say 1 to 4.
I appreciate your patience and I know you know what your doing, Im just
having hard time explaining to you just what I need. I hope the above
explination will help. If not Ill keep trying.
Thanks for helping
ed

:

Dear Gambler:

I cannot see what kind of results you received, nor how they were
different from what is disired. Could you fill me in so I can try to
help some more?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 28 Oct 2004 17:19:02 -0700, "gambler"

Hi Tom
I tried what you sent me and I got a syntax error code. I believe I found
the problem. In the row that starts out with WHERE there needed to be a space
between and and T1. Once I changed this the error went away. But the ranking
isnt correct. I sent you a SQL of one race. The rank should of went from 1-10
to I think there was 10 horses in that race. Im I correct? If you need more
info let me know.
Thanks for helping
ed
SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#10/22/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));

:

Dear Gambler:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK ANDT1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 27 Oct 2004 13:43:02 -0700, "gambler"

Hi Tom
The reason I didnt get back to you sooner was internet connection was down.
A race day is defined by the TRACK (tTRK) then the DATE ( tDATE) then the
RACE NUMBER (nRACE). There are anywhere from 8 to say 12 races on that date
at that track. They are numbered from 1 for the first race to 8 or 12
depending how many races there are that day. I wasnt sure where to add the
above defination of a race.
Sure appreciate your patients and help.
Thanks again
ed

:

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 17:05:07 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Tom Ellison]

Dear gambler:

After a few minutes on the phone, I think I know what was missing.
I'll correlate this a bit differently:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
ORDER BY tTRK, tDATE, tRACE

It now seems almost obvious this had to be what you wanted, now that I
have a few facts I was missing before.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Tom Ellison]

Now, correlating for the pace line:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE AND T1.xPL = T.xPL
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
ORDER BY tTRK, tDATE, nRACE, xPL

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear gambler:

After a few minutes on the phone, I think I know what was missing.
I'll correlate this a bit differently:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
ORDER BY tTRK, tDATE, nRACE

It now seems almost obvious this had to be what you wanted, now that I
have a few facts I was missing before.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed

 

[Guest]

Thanks for helping me. All appears to be working fine. For anyone out there
that needs help, once Tom understands what you want to do, he can do it.
Thanks for doing what I didnt think could be done.
Thanks Tom
ed

Now, correlating for the pace line:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE AND T1.xPL = T.xPL
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
ORDER BY tTRK, tDATE, nRACE, xPL

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear gambler:

After a few minutes on the phone, I think I know what was missing.
I'll correlate this a bit differently:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.tTRK = T.tTRK AND T1.tDATE = T.tDATE
AND T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
ORDER BY tTRK, tDATE, nRACE

It now seems almost obvious this had to be what you wanted, now that I
have a few facts I was missing before.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Dear Gambler:

What I did not know before is that you want to apply this ranking to
each race. In order to do this, I must know the definition of what is
a race. Then the query can rank the results race-by-race, rather than
for all races taken together.

Assuming that nRACE defines a race, the query could be:

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.nRACE = T.nRACE
AND T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T

Please notice the change involves simply equating nRACE in the
subquery. If there are other columns necessary to delineate one race
from another, add them similarly with the AND conjunction.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 17:05:07 -0700, "gambler"

SELECT T.tTRK, T.tDATE, T.nRACE, T.nDIST, T.tSURF, T.tCLA, T.tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, (SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank, T.tHOR, T.xPL
FROM ALL_HX4 AS T
WHERE (((T.tDATE)=#8/13/2004#) AND ((T.nRACE)=1) AND ((T.xPL)=5));
Hi Tom
I hope I used the right formula you made for me. I had a hard time telling
if I used the correct one. This is a query of one race with the EXPR1 in
Asending order. Instead of the ranking going from 1 to 10, it has all those
high numbers. What Im trying to do is look at the winner of a 100 races and
see how many times the winner was in the top 5 ranking of EXPR1. The query I
sent you was of one race. If I confusing you let me know and Ill try and be
more detailed.
Sure appreciate the help
ed

:

Dear Gambler:

I'm glad you let me know you have this problem. I'd rather not make
the effort to help you and fail because you don't know how to use the
help I sent.

I suggest you create a new query and open the SQL View of that query
using the control right under the File menu in the upper left hand
corner of the screen. Click on the arrow pointed downward to open a
list of options and choose SQL View. Paste in the text I sent and
save it as a new query. You can then run and test it.

In this newsgroup, almost all exchanges are SQL View because this is
text and can be sent back and forth precisely.

Please let me know how this is coming along for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 26 Oct 2004 13:03:01 -0700, "gambler"

Hi Tom
Im a little confused as this is the first time I used this board. I believe
you answered my question, but I dont know where to type your answer in my
query so I can find out if it works for me. If you did answer my question,
where do I type it in my query and what do I type in? Hope Im not upsetting
you with my stupitity.
I truly appreciate your help
ed

:

Dear Gambler:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1, xPL,f
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

So, is it working?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 25 Oct 2004 15:51:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS
Expr1, ALL_HX4.xPL
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));
Tom
I missed one key element in my last post. I forgot xPL. If it is still
possible could you add it to your formula. Im sorry I just forgot it. I sure
appreciate you help. You dont know how happy it makes one to come this close
to getting answer to something that I have been looking for a long long time.
If it is too much trouble to add xPl, I will be happy with whatever help you
give me. Just tell me where I type it in.

Thanks again
ed

:

Dear Ed:

First, I'll modify your query a bit:

SELECT tTRK, tDATE, nRACE, nDIST, tSURF, tCLA, tPGM,
[nTRNITM]+[nJKYITM]+[nTJ] AS Expr1,
(SELECT COUNT(*) + 1 FROM ALL_HX4 T1
WHERE T1.[nTRNITM] + T1.[nJKYITM] + T1.[nTJ] >
T.[nTRNITM] + T.[nJKYITM] + T.[nTJ]) AS Rank
FROM ALL_HX4 T
WHERE tTRK = "MNR" AND tDATE = #10/10/2004#;

That does make it easier to modify.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 24 Oct 2004 14:43:03 -0700, "gambler"

SELECT ALL_HX4.tTRK, ALL_HX4.tDATE, ALL_HX4.nRACE, ALL_HX4.nDIST,
ALL_HX4.tSURF, ALL_HX4.tCLA, ALL_HX4.tPGM, [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ALL_HX4
WHERE (((ALL_HX4.tTRK)="MNR") AND ((ALL_HX4.tDATE)=#10/10/2004#));

Here is a simpler query [I think]. I might be able to take a few things out
of it. The info all comes from same table. The only thing I would change from
time to time is the Expr1. Maybe this will make things easier.
Where at in the query do I type in your answer. Select*(Select
Count(*)+/From etc. ?
I sure appreciate your help. Im trying my best to help without being to
dumb. But without you Im lost.
Thanks again ed

:

I have a list of numbers( that come from a formula--Eg. FR1 + FR2) in one of
my query fields. I would like to take these numbers and give them a ranking.
The highest number would be ranked number1, the second highest number would
be ranked number 2 etc. I would appreciate any help you would give me on how
I could rank these numbers.
I am using Windows Xp and have Office 2000.
Thanks ed