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