Ranking records in sorted list based on expression

[Guest]

I have read many of the threads on this forum re: ranking records but still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS [Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID = tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;

 

[Tom Ellison]

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend writing
another query on top of this one to add a ranking column. I'll assume the
query you already have is named MyQuery. Adjust my code accordingly for the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing query, but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison

 

[Guest]

Thanks Tom,

On my first attempt the result is 1 for all records and does not increment.
I'm gonna have to work on this one but i think I'll work it out. So the idea
is to rank in a subquery right?

Let you know how I get on.

Bruce

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend writing
another query on top of this one to add a ranking column. I'll assume the
query you already have is named MyQuery. Adjust my code accordingly for the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing query, but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison

I have read many of the threads on this forum re: ranking records but still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS
[Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID =
tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;

 

[Tom Ellison]

Dear Bruce:

I've looked at what I provided, and don't see a problem that would cause
what you're seeing. Would you care to post the SQL of what you have done.
Maybe I'll see a problem there.

Tom Ellison

Thanks Tom,

On my first attempt the result is 1 for all records and does not
increment.
I'm gonna have to work on this one but i think I'll work it out. So the
idea
is to rank in a subquery right?

Let you know how I get on.

Bruce

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend
writing
another query on top of this one to add a ranking column. I'll assume
the
query you already have is named MyQuery. Adjust my code accordingly for
the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing query,
but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison

I have read many of the threads on this forum re: ranking records but
still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a
calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS
[Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID =
tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;

 

[Guest]

Tom,

In my first attempt I tried to use my existing code but must have had
something wrong. In a second attempt I used the simplified code you posted
for both the first and second parts of the queries. The results are aas
desired.

Only problem is this query has a huge O/H on my system. The first part of
the query takes 3 - 4 seconds to run (which is fine), but the 2nd query takes
around 3.5 minutes to run. My system if a dual core 2.8GHz with 1GB DDR2 ram
so it should be ok. The table tbl_ADX_Data has around 2.2million records so
I think this is the problem.

I think I might need to run a procedure and append the results to a tempory
table. The result change once daily but I may want to view them a number of
times. What do you think? Is there another way to approach a problem like
this?

Bruce

Dear Bruce:

I've looked at what I provided, and don't see a problem that would cause
what you're seeing. Would you care to post the SQL of what you have done.
Maybe I'll see a problem there.

Tom Ellison

Thanks Tom,

On my first attempt the result is 1 for all records and does not
increment.
I'm gonna have to work on this one but i think I'll work it out. So the
idea
is to rank in a subquery right?

Let you know how I get on.

Bruce

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend
writing
another query on top of this one to add a ranking column. I'll assume
the
query you already have is named MyQuery. Adjust my code accordingly for
the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing query,
but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison


I have read many of the threads on this forum re: ranking records but
still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a
calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS
[Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID =
tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;

 

[Tom Ellison]

Dear Bruce:

I recommend you attempt what you suggest. It sounds pretty simple and easy
to implement. You may want to try this with and without an index on the
Captialisation column just to see if that helps.

If not, come back with your results and I'll make some other, perhaps less
simple recommendations.

Tom Ellison

Tom,

In my first attempt I tried to use my existing code but must have had
something wrong. In a second attempt I used the simplified code you posted
for both the first and second parts of the queries. The results are aas
desired.

Only problem is this query has a huge O/H on my system. The first part of
the query takes 3 - 4 seconds to run (which is fine), but the 2nd query
takes
around 3.5 minutes to run. My system if a dual core 2.8GHz with 1GB DDR2
ram
so it should be ok. The table tbl_ADX_Data has around 2.2million records
so
I think this is the problem.

I think I might need to run a procedure and append the results to a
tempory
table. The result change once daily but I may want to view them a number
of
times. What do you think? Is there another way to approach a problem like
this?

Bruce

Dear Bruce:

I've looked at what I provided, and don't see a problem that would cause
what you're seeing. Would you care to post the SQL of what you have
done.
Maybe I'll see a problem there.

Tom Ellison

Thanks Tom,

On my first attempt the result is 1 for all records and does not
increment.
I'm gonna have to work on this one but i think I'll work it out. So the
idea
is to rank in a subquery right?

Let you know how I get on.

Bruce

:

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend
writing
another query on top of this one to add a ranking column. I'll assume
the
query you already have is named MyQuery. Adjust my code accordingly
for
the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing
query,
but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison


I have read many of the threads on this forum re: ranking records but
still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a
calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS
[Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID =
tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;

 

[Tom Ellison]

Dear Bruce:

I will add that, in a recent situation, I did something very like this, and
achieved very good results. You are on a good track here, one worth
checking out.

Tom Ellison

Tom,

In my first attempt I tried to use my existing code but must have had
something wrong. In a second attempt I used the simplified code you posted
for both the first and second parts of the queries. The results are aas
desired.

Only problem is this query has a huge O/H on my system. The first part of
the query takes 3 - 4 seconds to run (which is fine), but the 2nd query
takes
around 3.5 minutes to run. My system if a dual core 2.8GHz with 1GB DDR2
ram
so it should be ok. The table tbl_ADX_Data has around 2.2million records
so
I think this is the problem.

I think I might need to run a procedure and append the results to a
tempory
table. The result change once daily but I may want to view them a number
of
times. What do you think? Is there another way to approach a problem like
this?

Bruce

Dear Bruce:

I've looked at what I provided, and don't see a problem that would cause
what you're seeing. Would you care to post the SQL of what you have
done.
Maybe I'll see a problem there.

Tom Ellison

Thanks Tom,

On my first attempt the result is 1 for all records and does not
increment.
I'm gonna have to work on this one but i think I'll work it out. So the
idea
is to rank in a subquery right?

Let you know how I get on.

Bruce

:

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend
writing
another query on top of this one to add a ranking column. I'll assume
the
query you already have is named MyQuery. Adjust my code accordingly
for
the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing
query,
but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison


I have read many of the threads on this forum re: ranking records but
still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a
calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS
[Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID =
tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;

 

[Guest]

Ok,

Have setup a daily stats table called 'tbl_MCR_Stats '. So now I can run my
rank off values. Now when I run this query it works and it is very quick (as
it only has to process around 1300 records).

SELECT Q.ASXCode, Q.[12mth_Capitalisation], (SELECT COUNT(*)
FROM tbl_MCR_Stats AS Q1
WHERE Q1.[12mth_Capitalisation] > Q.[12mth_Capitalisation])+1 AS RANK
FROM tbl_MCR_Stats AS Q
ORDER BY Q.[12mth_Capitalisation] DESC;

Since I have gone down the path of creating the table 'tbl_MCR_Stats' I
believe my final step is to store the rank in this table as well. I tried to
convert the first query into an update query to update the result in the
field
[tbl_MCR_Stats].[12mth_Capital_Rank] but it says 'Operation must use an
updatable query'. I recall running into problems like this previously where i
had the same error and my query contained a sub-query. I also get the same
errror if I try to do the update from the first query via a second query.

What should I be looking at here?

Bruce.

UPDATE tbl_MCR_Stats INNER JOIN tbl_MCR_Stats AS Q ON tbl_MCR_Stats.ASXCode
= Q.ASXCode SET tbl_MCR_Stats.[12mth_Capital_Rank] = (SELECT COUNT(*) FROM
tbl_MCR_Stats AS Q1 WHERE Q1.[12mth_Capitalisation] >
Q.[12mth_Capitalisation])+1;

Dear Bruce:

I will add that, in a recent situation, I did something very like this, and
achieved very good results. You are on a good track here, one worth
checking out.

Tom Ellison

Tom,

In my first attempt I tried to use my existing code but must have had
something wrong. In a second attempt I used the simplified code you posted
for both the first and second parts of the queries. The results are aas
desired.

Only problem is this query has a huge O/H on my system. The first part of
the query takes 3 - 4 seconds to run (which is fine), but the 2nd query
takes
around 3.5 minutes to run. My system if a dual core 2.8GHz with 1GB DDR2
ram
so it should be ok. The table tbl_ADX_Data has around 2.2million records
so
I think this is the problem.

I think I might need to run a procedure and append the results to a
tempory
table. The result change once daily but I may want to view them a number
of
times. What do you think? Is there another way to approach a problem like
this?

Bruce

Dear Bruce:

I've looked at what I provided, and don't see a problem that would cause
what you're seeing. Would you care to post the SQL of what you have
done.
Maybe I'll see a problem there.

Tom Ellison


Thanks Tom,

On my first attempt the result is 1 for all records and does not
increment.
I'm gonna have to work on this one but i think I'll work it out. So the
idea
is to rank in a subquery right?

Let you know how I get on.

Bruce

:

Dear Bruce:

I reproduce your query here, formatted for my reading preferences:

SELECT C.ASXCode, round(Avg(A.Close), 3) AS Close,
round(Avg(A.Volume), 0) AS Vol,
round(Avg(A.Close) * Avg(A.Volume), 0) AS Capitalisation
FROM tbl_Company C
INNER JOIN tbl_ASX_Data A
ON C.ID = A.ID
WHERE A.ImportDate > Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")
GROUP BY C.ASXCode
ORDER BY round(Avg(A.Close) * Avg(A.Volume), 0) DESC;

I take it that the ranking is to be 1 for the lowest value of
Capitalisation.

As you have a fair amount of query already, I'm going to recommend
writing
another query on top of this one to add a ranking column. I'll assume
the
query you already have is named MyQuery. Adjust my code accordingly
for
the
actual name.

SELECT ASXCode, Close, Vol, Capitalisation,
(SELECT COUNT(*)
FROM MyQuery Q1
WHERE Q1.Capitalisation > Q.Capitalisation) + 1
AS Rank
FROM MyQuery Q
ORDER BY Capitalisation DESC

Does this help? You could insert the ranking into your existing
query,
but
it would require two copies of the calculation substituted into the
subquery.

Tom Ellison


I have read many of the threads on this forum re: ranking records but
still
fail to have any success.

In my query below I have my data is ranked ny the expression
'Capitalisation' and sorted descending. I simply wan to add a
calculateded
field to my query that ranks based on my capitalisation results.

Any advise much appreciated.

Bruce

SELECT tbl_Company.ASXCode, round(Avg([tbl_ASX_Data]![Close]),3) AS
[Close],
round(Avg([tbl_ASX_Data]![Volume]),0) AS Vol,
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0) AS
Capitalisation
FROM tbl_Company INNER JOIN tbl_ASX_Data ON tbl_Company.ID =
tbl_ASX_Data.ID
WHERE
(((tbl_ASX_Data.ImportDate)>Format(DateAdd("yyyy",-1,Date()),"yyyymmdd")))
GROUP BY tbl_Company.ASXCode
ORDER BY
round(Avg([tbl_ASX_Data]![Close])*Avg([tbl_ASX_Data]![Volume]),0)
DESC;