How to get rank numbers for Top results

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and sort
them by dollars. The query works but I don't know how to
show ranking for each row in a seperate column. like 1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

Dear BG:

If have found the best way to help someone with this question is to
have them post the query the have now that shows the results they want
but without the Rank column. It should sort the results so the #1
ranking row is at the top.

Please be advised that the results may show ties, and will do so as
they would be in a horse race. If two horses finish tied for first,
the next horse is in 3rd place, and there is no 2nd place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG" wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and sort
them by dollars. The query works but I don't know how to
show ranking for each row in a seperate column. like 1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

Yes it's OK if there's a tie.

here's my query. Please let me know if this is what you
asked me to post. Thanks for your help.
-------------------
SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account, vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru INNER JOIN dbo_vw_MM_MATERIAL ON
vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON
dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE (((dbo_vw_MM_MATERIAL.DEPARTMENT)="01"))
GROUP BY vw_sell_thru.week_ending, vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (((vw_sell_thru.week_ending)=[week_ending_date]))
ORDER BY vw_sell_thru.last_wk_sls DESC;



-----Original Message-----
Dear BG:

If have found the best way to help someone with this
question is to
have them post the query the have now that shows the
results they want
but without the Rank column. It should sort the results
so the #1
ranking row is at the top.

Please be advised that the results may show ties, and
will do so as
they would be in a horse race. If two horses finish
tied for first,
the next horse is in 3rd place, and there is no 2nd
place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG"
wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and
sort
them by dollars. The query works but I don't know how
to
show ranking for each row in a seperate column. like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

.

I've done something similar before by outputting to a table with an
autonumber field.

--
Ed

Remove the capital ST spam trap
when replying directly to me.


"BG" wrote in message
...
Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and sort
them by dollars. The query works but I don't know how to
show ranking for each row in a seperate column. like 1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

Dear MBGonul:

SELECT TOP 25 vw_sell_thru.week_ending, vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
WHERE T1.last_wk_sls vw_sell_thru.last_wk_sls) AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending, vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) = [week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

I have added the Rank column as the last one. Since this is a SELECT
INTO the actual position of the Rank column will have to match the
Rank column in the destination table, which I couldn't know.

Since you have ordered the results descending, I have created the
ranking so the largest value will be ranked #1. This is an
assumption, and if it is not correct, change the in my additional
column to be .

Please let me know if this helped and if I can be of any other
assistance.

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


On Thu, 9 Sep 2004 16:33:34 -0700, "BG" wrote:

Yes it's OK if there's a tie.

here's my query. Please let me know if this is what you
asked me to post. Thanks for your help.
-------------------
SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account, vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru INNER JOIN dbo_vw_MM_MATERIAL ON
vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON
dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE (((dbo_vw_MM_MATERIAL.DEPARTMENT)="01"))
GROUP BY vw_sell_thru.week_ending, vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (((vw_sell_thru.week_ending)=[week_ending_date]))
ORDER BY vw_sell_thru.last_wk_sls DESC;



-----Original Message-----
Dear BG:

If have found the best way to help someone with this
question is to
have them post the query the have now that shows the
results they want
but without the Rank column. It should sort the results
so the #1
ranking row is at the top.

Please be advised that the results may show ties, and
will do so as
they would be in a horse race. If two horses finish
tied for first,
the next horse is in 3rd place, and there is no 2nd
place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG"
wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and
sort
them by dollars. The query works but I don't know how
to
show ranking for each row in a seperate column. like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

.

Hi Tom - I ran the query and the result didn't come as
expected. the first rank I get is 2 then 8,
45,47,60....and the last one is 468...
Any idea why it might have come that way?


-----Original Message-----
Dear MBGonul:

SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
WHERE T1.last_wk_sls vw_sell_thru.last_wk_sls)
AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material =
dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) = [week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

I have added the Rank column as the last one. Since
this is a SELECT
INTO the actual position of the Rank column will have to
match the
Rank column in the destination table, which I couldn't
know.

Since you have ordered the results descending, I have
created the
ranking so the largest value will be ranked #1. This is
an
assumption, and if it is not correct, change the in my
additional
column to be .

Please let me know if this helped and if I can be of any
other
assistance.

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


On Thu, 9 Sep 2004 16:33:34 -0700, "BG"
wrote:

Yes it's OK if there's a tie.

here's my query. Please let me know if this is what you
asked me to post. Thanks for your help.
-------------------
SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account, vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru INNER JOIN dbo_vw_MM_MATERIAL ON
vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON
dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE (((dbo_vw_MM_MATERIAL.DEPARTMENT)="01"))
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (((vw_sell_thru.week_ending)=[week_ending_date]))
ORDER BY vw_sell_thru.last_wk_sls DESC;



-----Original Message-----
Dear BG:

If have found the best way to help someone with this
question is to
have them post the query the have now that shows the
results they want
but without the Rank column. It should sort the
results
so the #1
ranking row is at the top.

Please be advised that the results may show ties, and
will do so as
they would be in a horse race. If two horses finish
tied for first,
the next horse is in 3rd place, and there is no 2nd
place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG"
wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and
sort
them by dollars. The query works but I don't know how
to
show ranking for each row in a seperate column. like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

.


.

Dear BG:

That would be because I missed the filter.

SELECT TOP 25 vw_sell_thru.week_ending, vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
INNER JOIN dbo_vw_MM_MATERIAL
ON T1.material = dbo_vw_MM_MATERIAL.MATERIAL
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
AND T1.last_wk_sls vw_sell_thru.last_wk_sls) AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending, vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) = [week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

This is getting fairly complex. I hope I haven't had too much trouble
fixing it up properly. It's not always easy for me.

So, what it was doing was to calculate the Rank using every row in
vw_sell_thru, whereas it needs to use only those rows where the joined
row of dbo_ww_MM_MATERIAL has Department 01. It was apparently
finding a number of rows to count from that table that you wanted to
exclude. Sorry, missed that, but it's obvious given the problem you
had. Your description was just what I needed to know to see what I
had missed.

Hope this is much better. And I hope this isn't getting too complex
for Jet.

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


On Fri, 10 Sep 2004 11:05:25 -0700, "BG"
wrote:

Hi Tom - I ran the query and the result didn't come as
expected. the first rank I get is 2 then 8,
45,47,60....and the last one is 468...
Any idea why it might have come that way?


-----Original Message-----
Dear MBGonul:

SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
WHERE T1.last_wk_sls vw_sell_thru.last_wk_sls)
AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material =
dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) = [week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

I have added the Rank column as the last one. Since
this is a SELECT
INTO the actual position of the Rank column will have to
match the
Rank column in the destination table, which I couldn't
know.

Since you have ordered the results descending, I have
created the
ranking so the largest value will be ranked #1. This is
an
assumption, and if it is not correct, change the in my
additional
column to be .

Please let me know if this helped and if I can be of any
other
assistance.

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


On Thu, 9 Sep 2004 16:33:34 -0700, "BG"
wrote:

Yes it's OK if there's a tie.

here's my query. Please let me know if this is what you
asked me to post. Thanks for your help.
-------------------
SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account, vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru INNER JOIN dbo_vw_MM_MATERIAL ON
vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON
dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE (((dbo_vw_MM_MATERIAL.DEPARTMENT)="01"))
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material, dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (((vw_sell_thru.week_ending)=[week_ending_date]))
ORDER BY vw_sell_thru.last_wk_sls DESC;



-----Original Message-----
Dear BG:

If have found the best way to help someone with this
question is to
have them post the query the have now that shows the
results they want
but without the Rank column. It should sort the
results
so the #1
ranking row is at the top.

Please be advised that the results may show ties, and
will do so as
they would be in a horse race. If two horses finish
tied for first,
the next horse is in 3rd place, and there is no 2nd
place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG"
wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and
sort
them by dollars. The query works but I don't know how
to
show ranking for each row in a seperate column. like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

.


.

autonumber is a good idea but how do you get it to start
from 1 everytime you clear the table?
I created a new table with an autonumber column for rank
and then deleted the records before each time I appended
the records but autonumber keeps on adding. I have to re-
run the query every week and second time I ran it started
from 26.
Is there a way to get it started from 1 everytime?

-----Original Message-----
I've done something similar before by outputting to a
table with an
autonumber field.

--
Ed

Remove the capital ST spam trap
when replying directly to me.


"BG" wrote in message
...
Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week and
sort
them by dollars. The query works but I don't know how
to
show ranking for each row in a seperate column. like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks


.

It ran about 2 hrs and I got a similiar result. still
can't get the ranks from 1 to 25. I wish there was an
easier way to do.
I tried with insert query to a table with an autonumber
column but then when you do it more than once the second
time your rank starts with 26. I can't get it to start
with 1 every time.

so frustrating!


-----Original Message-----
Dear BG:

That would be because I missed the filter.

SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
INNER JOIN dbo_vw_MM_MATERIAL
ON T1.material = dbo_vw_MM_MATERIAL.MATERIAL
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
AND T1.last_wk_sls vw_sell_thru.last_wk_sls)
AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material =
dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) = [week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

This is getting fairly complex. I hope I haven't had
too much trouble
fixing it up properly. It's not always easy for me.

So, what it was doing was to calculate the Rank using
every row in
vw_sell_thru, whereas it needs to use only those rows
where the joined
row of dbo_ww_MM_MATERIAL has Department 01. It was
apparently
finding a number of rows to count from that table that
you wanted to
exclude. Sorry, missed that, but it's obvious given the
problem you
had. Your description was just what I needed to know to
see what I
had missed.

Hope this is much better. And I hope this isn't getting
too complex
for Jet.

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


On Fri, 10 Sep 2004 11:05:25 -0700, "BG"
wrote:

Hi Tom - I ran the query and the result didn't come as
expected. the first rank I get is 2 then 8,
45,47,60....and the last one is 468...
Any idea why it might have come that way?


-----Original Message-----
Dear MBGonul:

SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
WHERE T1.last_wk_sls vw_sell_thru.last_wk_sls)
AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material =
dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) =
[week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

I have added the Rank column as the last one. Since
this is a SELECT
INTO the actual position of the Rank column will have
to
match the
Rank column in the destination table, which I couldn't
know.

Since you have ordered the results descending, I have
created the
ranking so the largest value will be ranked #1. This
is
an
assumption, and if it is not correct, change the in
my
additional
column to be .

Please let me know if this helped and if I can be of
any
other
assistance.

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


On Thu, 9 Sep 2004 16:33:34 -0700, "BG"
wrote:

Yes it's OK if there's a tie.

here's my query. Please let me know if this is what
you
asked me to post. Thanks for your help.
-------------------
SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account, vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru INNER JOIN dbo_vw_MM_MATERIAL ON
vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON
dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE (((dbo_vw_MM_MATERIAL.DEPARTMENT)="01"))
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (((vw_sell_thru.week_ending)=
[week_ending_date]))
ORDER BY vw_sell_thru.last_wk_sls DESC;



-----Original Message-----
Dear BG:

If have found the best way to help someone with this
question is to
have them post the query the have now that shows the
results they want
but without the Rank column. It should sort the
results
so the #1
ranking row is at the top.

Please be advised that the results may show ties,
and
will do so as
they would be in a horse race. If two horses finish
tied for first,
the next horse is in 3rd place, and there is no 2nd
place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG"
wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week
and
sort
them by dollars. The query works but I don't know
how
to
show ranking for each row in a seperate column.
like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

.


.


.

Dear BG:

If you'd like to send me a database with just this table, and if it
will zip to less than a megabyte, you could attach it to your email
and I'll look at it. There's enough code here it may be that I'm
having trouble getting it all right without there being any chance to
test it as I go.

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


On Sun, 12 Sep 2004 16:02:17 -0700, "BG"
wrote:

It ran about 2 hrs and I got a similiar result. still
can't get the ranks from 1 to 25. I wish there was an
easier way to do.
I tried with insert query to a table with an autonumber
column but then when you do it more than once the second
time your rank starts with 26. I can't get it to start
with 1 every time.

so frustrating!


-----Original Message-----
Dear BG:

That would be because I missed the filter.

SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
INNER JOIN dbo_vw_MM_MATERIAL
ON T1.material = dbo_vw_MM_MATERIAL.MATERIAL
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
AND T1.last_wk_sls vw_sell_thru.last_wk_sls)
AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material =
dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) = [week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

This is getting fairly complex. I hope I haven't had
too much trouble
fixing it up properly. It's not always easy for me.

So, what it was doing was to calculate the Rank using
every row in
vw_sell_thru, whereas it needs to use only those rows
where the joined
row of dbo_ww_MM_MATERIAL has Department 01. It was
apparently
finding a number of rows to count from that table that
you wanted to
exclude. Sorry, missed that, but it's obvious given the
problem you
had. Your description was just what I needed to know to
see what I
had missed.

Hope this is much better. And I hope this isn't getting
too complex
for Jet.

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


On Fri, 10 Sep 2004 11:05:25 -0700, "BG"
wrote:

Hi Tom - I ran the query and the result didn't come as
expected. the first rank I get is 2 then 8,
45,47,60....and the last one is 468...
Any idea why it might have come that way?


-----Original Message-----
Dear MBGonul:

SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2 ,
(SELECT COUNT(*) + 1 FROM vw_sell_thru T1
WHERE T1.last_wk_sls vw_sell_thru.last_wk_sls)
AS Rank
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru
INNER JOIN dbo_vw_MM_MATERIAL
ON vw_sell_thru.material =
dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT
ON dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE dbo_vw_MM_MATERIAL.DEPARTMENT = "01"
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
HAVING (vw_sell_thru.week_ending) =
[week_ending_date]
ORDER BY vw_sell_thru.last_wk_sls DESC;

I have added the Rank column as the last one. Since
this is a SELECT
INTO the actual position of the Rank column will have
to
match the
Rank column in the destination table, which I couldn't
know.

Since you have ordered the results descending, I have
created the
ranking so the largest value will be ranked #1. This
is
an
assumption, and if it is not correct, change the in
my
additional
column to be .

Please let me know if this helped and if I can be of
any
other
assistance.

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


On Thu, 9 Sep 2004 16:33:34 -0700, "BG"
wrote:

Yes it's OK if there's a tie.

here's my query. Please let me know if this is what
you
asked me to post. Thanks for your help.
-------------------
SELECT TOP 25 vw_sell_thru.week_ending,
vw_sell_thru.account, vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1,
dbo_vw_MM_MATERIAL.COLOR_2
INTO tbl_fw_top25_styles_lw_pairs
FROM (vw_sell_thru INNER JOIN dbo_vw_MM_MATERIAL ON
vw_sell_thru.material = dbo_vw_MM_MATERIAL.MATERIAL)
INNER JOIN dbo_vw_kd_BUSINESS_UNIT ON
dbo_vw_MM_MATERIAL.BUSINESS_UNIT_CODE =
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_CODE
WHERE (((dbo_vw_MM_MATERIAL.DEPARTMENT)="01"))
GROUP BY vw_sell_thru.week_ending,
vw_sell_thru.account,
vw_sell_thru.last_wk_sls,
dbo_vw_kd_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
vw_sell_thru.material,
dbo_vw_MM_MATERIAL.DESCRIPTION,
dbo_vw_MM_MATERIAL.COLOR_1, dbo_vw_MM_MATERIAL.COLOR_2
HAVING (((vw_sell_thru.week_ending)=
[week_ending_date]))
ORDER BY vw_sell_thru.last_wk_sls DESC;



-----Original Message-----
Dear BG:

If have found the best way to help someone with this
question is to
have them post the query the have now that shows the
results they want
but without the Rank column. It should sort the
results
so the #1
ranking row is at the top.

Please be advised that the results may show ties,
and
will do so as
they would be in a horse race. If two horses finish
tied for first,
the next horse is in 3rd place, and there is no 2nd
place. OK?

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


On Thu, 9 Sep 2004 15:34:25 -0700, "BG"
wrote:

Hi - I appreciate any help you can give me on this.
I am trying to show top 25 styles sold in a week
and
sort
them by dollars. The query works but I don't know
how
to
show ranking for each row in a seperate column.
like
1st,
2nd, 3rd etc.
Can anyone please help.
Thanks

.


.


.