Ranking and restarting the rank on new product

[Mike M]

I'm trying to rank row in a query. I want to get the top 25 for each product
based on dollars.
e.i.

product sales rank
apples 10.00 1
apples 11.00 2
oranges 10.00 1
Bananas 10.00 1
Bananas 11.00 2

etc.

I'm using the following expression:
Rank: (Select Count (*) from T:OpportunitiesReport where [Extended
Cost]>[T:OpportunitiesReport1].[Extended Cost]+1

and can not figure our how to restart the count for each new product in the
query.

Can some on help me?

 

[Tom Ellison]

Dear Mike:

You need to correlate the subquery on product:

Rank: (Select Count (*) from T:OpportunitiesReport
where T.product = OpportunitiesReport.product
AND [Extended Cost]>[T:OpportunitiesReport1].[Extended Cost]+1)

That is, limit the COUNT() to those with the same product value.

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


On Tue, 19 Oct 2004 22:05:07 -0700, Mike M <Mike

 

[Mike M]

How do I create a subquery? Do I place it in my existing query and if so how?

Dear Mike:

You need to correlate the subquery on product:

Rank: (Select Count (*) from T:OpportunitiesReport
where T.product = OpportunitiesReport.product
AND [Extended Cost]>[T:OpportunitiesReport1].[Extended Cost]+1)

That is, limit the COUNT() to those with the same product value.

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


On Tue, 19 Oct 2004 22:05:07 -0700, Mike M <Mike

I'm trying to rank row in a query. I want to get the top 25 for each product
based on dollars.
e.i.

product sales rank
apples 10.00 1
apples 11.00 2
oranges 10.00 1
Bananas 10.00 1
Bananas 11.00 2

etc.

I'm using the following expression:
Rank: (Select Count (*) from T:OpportunitiesReport where [Extended
Cost]>[T:OpportunitiesReport1].[Extended Cost]+1

and can not figure our how to restart the count for each new product in the
query.

Can some on help me?

 

[Tom Ellison]

Dear Mike:

The thing you originally posted IS a subquery. A subquery is a query
embedded within your SQL statement. Your Rank column does just this.
All I did was to change the correlation of it to your outer (main)
query to rely on the product column being the same for each count
made.

I'm not too sure about the exact syntax you need. If you have any
problem, please post the entire SQL of your query here, not just the
one column. This may be necessary for me to help make it work
properly.

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

How do I create a subquery? Do I place it in my existing query and if so how?

Dear Mike:

You need to correlate the subquery on product:

Rank: (Select Count (*) from T:OpportunitiesReport
where T.product = OpportunitiesReport.product
AND [Extended Cost]>[T:OpportunitiesReport1].[Extended Cost]+1)

That is, limit the COUNT() to those with the same product value.

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


On Tue, 19 Oct 2004 22:05:07 -0700, Mike M <Mike

I'm trying to rank row in a query. I want to get the top 25 for each product
based on dollars.
e.i.

product sales rank
apples 10.00 1
apples 11.00 2
oranges 10.00 1
Bananas 10.00 1
Bananas 11.00 2

etc.

I'm using the following expression:
Rank: (Select Count (*) from T:OpportunitiesReport where [Extended
Cost]>[T:OpportunitiesReport1].[Extended Cost]+1

and can not figure our how to restart the count for each new product in the
query.

Can some on help me?

 

[Mike M]

Tom,

Thanks, messed around with it and it works.

Thanks,

Mike

Dear Mike:

The thing you originally posted IS a subquery. A subquery is a query
embedded within your SQL statement. Your Rank column does just this.
All I did was to change the correlation of it to your outer (main)
query to rely on the product column being the same for each count
made.

I'm not too sure about the exact syntax you need. If you have any
problem, please post the entire SQL of your query here, not just the
one column. This may be necessary for me to help make it work
properly.

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

How do I create a subquery? Do I place it in my existing query and if so how?

Dear Mike:

You need to correlate the subquery on product:

Rank: (Select Count (*) from T:OpportunitiesReport
where T.product = OpportunitiesReport.product
AND [Extended Cost]>[T:OpportunitiesReport1].[Extended Cost]+1)

That is, limit the COUNT() to those with the same product value.

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


I'm trying to rank row in a query. I want to get the top 25 for each product
based on dollars.
e.i.

product sales rank
apples 10.00 1
apples 11.00 2
oranges 10.00 1
Bananas 10.00 1
Bananas 11.00 2

etc.

I'm using the following expression:
Rank: (Select Count (*) from T:OpportunitiesReport where [Extended
Cost]>[T:OpportunitiesReport1].[Extended Cost]+1

and can not figure our how to restart the count for each new product in the
query.

Can some on help me?