RANKING

[Guest]

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 Ed:

Ranking can be done with a correlated subquery.

Please post a query that returns everything you want to see except the
Rank column and I'll add that.

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

 

[Guest]

How do I post that query you would like to see?
Thanks for the help ed

 

[Tom Ellison]

Dear Gambler:

You are almost certainly accustomed to working with the Design Grid
view of your queries. There is another view available, the SQL view,
which is available through the icon in the upper left corner, under
the word File. If you pull down the list there, you should see SQL
View as an option. The SQL will then be shown as text, and you can
paste it into your message.

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

 

[Guest]

SELECT [ALL_HX4].[tTRK], [ALL_HX4].[tDATE], [ALL_HX4].[nRACE],
[ALL_HX4].[nDIST], [ALL_HX4].[tSURF], [ALL_HX4].[tCLA], [ALL_HX4].[nCLM],
[ALL_HX4].[tPGM], [ALL_HX4].[tHOR], [ALL_HX4].[nPPO], [ALL_HX4].[nMLO],
[ALL_HX4].[nLAY], [ALL_HX4].[nCLch], [ALL_HX4].[xPL], [PL4].[tPL],
[PL4].[nKLINE], [PL5].[tPL], [PL5].[nKLINE], [ALL_HX4].[tCONDS],
[ALL_HX4].[tLTRK], [ALL_HX4].[nLDIST], [ALL_HX4].[nTRCH], [ALL_HX4].[rTRN],
[ALL_HX4].[nTRN], [ALL_HX4].[nJKY], [ALL_HX4].[rJKY], [ALL_HX4].[nJKCH],
[ALL_HX4].[nTRNITM], [ALL_HX4].[nJKYITM], [ALL_HX4].[nWKSC],
[ALL_HX4].[nLiveP], [ALL_HX4].[nLiveS], [ALL_HX4].[nLiveL], [PL4].[rFr1],
[PL5].[rFr1], [PL4].[rEP], [PL5].[rEP], [PL4].[nFR3], [PL5].[nFR3],
[PL4].[rPER], [PL4].[nPER], [PL5].[rPER], [ALL_HX4].[nLDR], [PL4].[nLONG],
[PL5].[nLONG], [PL4].[nKrat], [PL4].[rKrat], [PL5].[nKrat], [PL5].[rKrat],
[ALL_HX4].[tPOST], [ALL_HX4].[nVI], [ALL_HX4].[nQFIVES], [ALL_HX4].[tAGE],
[ALL_HX4].[nPURS], [ALL_HX4].[nPED], [ALL_HX4].[tRS], [ALL_HX4].[nQP],
[ALL_HX4].[nLiveA], [ALL_HX4].[nLAY90], [ALL_HX4].[tCHG],
[ALL_HX5].[nPP1FIN], [ALL_HX5].[nPP2FIN], [ALL_HX5].[nPP3FIN],
[ALL_HX4].[nTJST], [ALL_HX4].[nTJ], [ALL_HX4].[nTRNST], [ALL_HX4].[nJKYST],
[ALL_HX4].[nLSURF], [ALL_HX4].[nTRNHST], [ALL_HX4].[nTRNHW],
[ALL_HX4].[nJKYHST], [ALL_HX4].[nJKYHW], [ALL_HX4].[nTRNW],
[ALL_HX4].[nJKYW], [PL4].[n2FRAW], [PL5].[n2FRAW], [ALL_HX4].[tSEX],
[ALL_HX4].[nHAG], [ALL_HX4].[tLIFE], [ALL_HX4].[tDS], [ALL_HX4].[nCLAT1],
[ALL_HX4].[nCLAT2], [ALL_HX5].[nPP1DIS], [ALL_HX5].[nPP2DIS],
[ALL_HX5].[nPP3DIS], [ALL_HX5].[nPP1SUR], [ALL_HX5].[nPP2SUR],
[ALL_HX5].[nPP3SUR], [ALL_HX5].[nPP1SHF], [ALL_HX5].[nPP2SHF],
[ALL_HX5].[nPP3SHF], [ALL_HX4].[rC90], [ALL_HX4].[rPSCN], [ALL_HX4].[rPFIG],
[ALL_HX4].[rHTR], [ALL_HX4].[nODDS1], [ALL_HX4].[nODDS2], [ALL_HX4].[nODDS3],
[ALL_HX4].[nCLAT3], [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ((ALL_HX4 INNER JOIN PL4 ON ([ALL_HX4].[tTRK]=[PL4].[tTRK]) AND
([ALL_HX4].[tDATE]=[PL4].[tDATE]) AND ([ALL_HX4].[nRACE]=[PL4].[nRACE]) AND
([ALL_HX4].[tPGM]=[PL4].[tPGM])) INNER JOIN PL5 ON
([PL4].[tTRK]=[PL5].[tTRK]) AND ([PL4].[tDATE]=[PL5].[tDATE]) AND
([PL4].[nRACE]=[PL5].[nRACE]) AND ([PL4].[tPGM]=[PL5].[tPGM])) INNER JOIN
ALL_HX5 ON ([PL5].[tPGM]=[ALL_HX5].[tPGM]) AND
([PL5].[nRACE]=[ALL_HX5].[nRACE]) AND ([PL5].[tDATE]=[ALL_HX5].[tDATE]) AND
([PL5].[tTRK]=[ALL_HX5].[tTRK])
WHERE ((([ALL_HX4].[tTRK])="mnr") And (([ALL_HX4].[tDATE])=#10/8/2004#) And
(([ALL_HX4].[xPL])=5));
Sorry I didnt respond sooner, I didnt know you responded to my question. In
the very last columb is Expr1. This is the columb I would like to rank. I
sure appreciate your help. Im not to smart when it comes to using access, but
i have some books and I try. Thanks for helping. ed

 

[Tom Ellison]

Dear Gambler:

With such a long query, the use of short aliases would be a distinct
improvement for readability.

The subquery for the ranking would be:

(SELECT COUNT(*) + 1 FROM SomeTable T
WHERE T.[nTRNITM] + T.[nJKYITM] + T.[nTJ] >
X.[nTRNITM] + X.[nJKYITM] + X.[nTJ]) AS Rank

There was very sparse information in your query, in spite of its
length. Your Expr1 is the sum of 3 columns, but the query does not
specify from which table(s) they come. This will make it much more
difficult for you to adapt my code to your situation.

I don't know if all 3 components of Expr1 come from one table or more.
You have a considerable inner join going in your query, which
complicates this.

In my code you will need to change SomeTable to be one of your tables,
or perhaps a join of several of the tables. The T alias may need to
be several aliases. Similarly, the X references will be from your
existing FROM clause's list of tables.

Alternatively, you may wish to base a new query on what you already
have and just run the Ranking query on that. That may be much easier.
Just add:

SELECT *,
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.Expr1 > Q.Expr1) AS Rank
FROM YourQuery Q
ORDER BY Expr1

If there is much data, your performance calculating a rank column may
not be good.

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

SELECT [ALL_HX4].[tTRK], [ALL_HX4].[tDATE], [ALL_HX4].[nRACE],
[ALL_HX4].[nDIST], [ALL_HX4].[tSURF], [ALL_HX4].[tCLA], [ALL_HX4].[nCLM],
[ALL_HX4].[tPGM], [ALL_HX4].[tHOR], [ALL_HX4].[nPPO], [ALL_HX4].[nMLO],
[ALL_HX4].[nLAY], [ALL_HX4].[nCLch], [ALL_HX4].[xPL], [PL4].[tPL],
[PL4].[nKLINE], [PL5].[tPL], [PL5].[nKLINE], [ALL_HX4].[tCONDS],
[ALL_HX4].[tLTRK], [ALL_HX4].[nLDIST], [ALL_HX4].[nTRCH], [ALL_HX4].[rTRN],
[ALL_HX4].[nTRN], [ALL_HX4].[nJKY], [ALL_HX4].[rJKY], [ALL_HX4].[nJKCH],
[ALL_HX4].[nTRNITM], [ALL_HX4].[nJKYITM], [ALL_HX4].[nWKSC],
[ALL_HX4].[nLiveP], [ALL_HX4].[nLiveS], [ALL_HX4].[nLiveL], [PL4].[rFr1],
[PL5].[rFr1], [PL4].[rEP], [PL5].[rEP], [PL4].[nFR3], [PL5].[nFR3],
[PL4].[rPER], [PL4].[nPER], [PL5].[rPER], [ALL_HX4].[nLDR], [PL4].[nLONG],
[PL5].[nLONG], [PL4].[nKrat], [PL4].[rKrat], [PL5].[nKrat], [PL5].[rKrat],
[ALL_HX4].[tPOST], [ALL_HX4].[nVI], [ALL_HX4].[nQFIVES], [ALL_HX4].[tAGE],
[ALL_HX4].[nPURS], [ALL_HX4].[nPED], [ALL_HX4].[tRS], [ALL_HX4].[nQP],
[ALL_HX4].[nLiveA], [ALL_HX4].[nLAY90], [ALL_HX4].[tCHG],
[ALL_HX5].[nPP1FIN], [ALL_HX5].[nPP2FIN], [ALL_HX5].[nPP3FIN],
[ALL_HX4].[nTJST], [ALL_HX4].[nTJ], [ALL_HX4].[nTRNST], [ALL_HX4].[nJKYST],
[ALL_HX4].[nLSURF], [ALL_HX4].[nTRNHST], [ALL_HX4].[nTRNHW],
[ALL_HX4].[nJKYHST], [ALL_HX4].[nJKYHW], [ALL_HX4].[nTRNW],
[ALL_HX4].[nJKYW], [PL4].[n2FRAW], [PL5].[n2FRAW], [ALL_HX4].[tSEX],
[ALL_HX4].[nHAG], [ALL_HX4].[tLIFE], [ALL_HX4].[tDS], [ALL_HX4].[nCLAT1],
[ALL_HX4].[nCLAT2], [ALL_HX5].[nPP1DIS], [ALL_HX5].[nPP2DIS],
[ALL_HX5].[nPP3DIS], [ALL_HX5].[nPP1SUR], [ALL_HX5].[nPP2SUR],
[ALL_HX5].[nPP3SUR], [ALL_HX5].[nPP1SHF], [ALL_HX5].[nPP2SHF],
[ALL_HX5].[nPP3SHF], [ALL_HX4].[rC90], [ALL_HX4].[rPSCN], [ALL_HX4].[rPFIG],
[ALL_HX4].[rHTR], [ALL_HX4].[nODDS1], [ALL_HX4].[nODDS2], [ALL_HX4].[nODDS3],
[ALL_HX4].[nCLAT3], [nTRNITM]+[nJKYITM]+[nTJ] AS Expr1
FROM ((ALL_HX4 INNER JOIN PL4 ON ([ALL_HX4].[tTRK]=[PL4].[tTRK]) AND
([ALL_HX4].[tDATE]=[PL4].[tDATE]) AND ([ALL_HX4].[nRACE]=[PL4].[nRACE]) AND
([ALL_HX4].[tPGM]=[PL4].[tPGM])) INNER JOIN PL5 ON
([PL4].[tTRK]=[PL5].[tTRK]) AND ([PL4].[tDATE]=[PL5].[tDATE]) AND
([PL4].[nRACE]=[PL5].[nRACE]) AND ([PL4].[tPGM]=[PL5].[tPGM])) INNER JOIN
ALL_HX5 ON ([PL5].[tPGM]=[ALL_HX5].[tPGM]) AND
([PL5].[nRACE]=[ALL_HX5].[nRACE]) AND ([PL5].[tDATE]=[ALL_HX5].[tDATE]) AND
([PL5].[tTRK]=[ALL_HX5].[tTRK])
WHERE ((([ALL_HX4].[tTRK])="mnr") And (([ALL_HX4].[tDATE])=#10/8/2004#) And
(([ALL_HX4].[xPL])=5));
Sorry I didnt respond sooner, I didnt know you responded to my question. In
the very last columb is Expr1. This is the columb I would like to rank. I
sure appreciate your help. Im not to smart when it comes to using access, but
i have some books and I try. Thanks for helping. ed

Dear Gambler:

You are almost certainly accustomed to working with the Design Grid
view of your queries. There is another view available, the SQL view,
which is available through the icon in the upper left corner, under
the word File. If you pull down the list there, you should see SQL
View as an option. The SQL will then be shown as text, and you can
paste it into your message.

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

 

[Guest]

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

 

[Tom Ellison]

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

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]

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

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:

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

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

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
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

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:

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

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

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]

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

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:

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

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
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

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:

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

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

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 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

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:

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

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

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]

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

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:

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

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