Ranking Query (from Postings) won't work!

[sara]

I hope someone can help with this.
Our director wants to have a report that will have the departments
(Retail stores) across the top, stores down the side and the RANKING of
the YTD dept sales within the chain in the cell.

Store/Dept 1 2 3 4
B 8 1 5 2
R 1 3 2 6

(etc.)
I have the following query, but it returns sequential numbers, not
starting from "1" for each department. I have tried the code from
other posts and from the MS knowledge base article
http://support.microsoft.com/kb/q120608/
but I keep getting a Syntax error on the "AS Rank" or right after the
"+1".

I don't need the sales $ or even the date in the results, but I do need
the Dept Number (CreditSalesTo here) and the Store number and the Rank.
I just can't "fill the cell" on the report with the rank as it is
here. I tried to think of a way to put this into a new table with a
make table query, but don't know how to get the Rank converted into a
sequential number, by Dept for that query.

Results and query below.
Many thanks,
sara
Results:
SalesDate CreditSalesTo YTDSales StoreNum DeptRANK
9/23/2006 1 270420.8 1 51
9/23/2006 1 251788.4 2 62
9/23/2006 1 184409.38 3 118
9/23/2006 1 184039.51 6 119
9/23/2006 1 171402.12 5 139
9/23/2006 1 156903.44 16 157
9/23/2006 1 154165.49 14 159
9/23/2006 1 130712.05 4 198
9/23/2006 1 129589.66 15 200
9/23/2006 1 106395.24 21 238
9/23/2006 1 103756.14 9 246
9/23/2006 1 103325.31 10 248
9/23/2006 1 91059.35 7 265
9/23/2006 1 88487.27 12 269
9/23/2006 1 81643.6 20 285
9/23/2006 1 56702.08 18 341
9/23/2006 2 473118.7 1 11
9/23/2006 2 471106.11 2 12
9/23/2006 2 416519 4 13
Query:
SELECT qrySalesByDeptF orDate.SalesDat e,
qrySalesByDeptF orDate.CreditSa lesTo, qrySalesByDeptF orDate.YTDSales ,
qrySalesByDeptF orDate.StoreNum , (SELECT COUNT(1) FROM
[qrySalesBYDeptF orDate] AS T WHERE T.[YTDSales] >=
[qrySalesByDeptF orDate].[YTDSales])+1 AS DeptRANK
FROM qrySalesByDeptF orDate
ORDER BY qrySalesByDeptF orDate.CreditSa lesTo,
qrySalesByDeptF orDate.YTDSales DESC;

[CDMAPoster]

sara wrote:

I hope someone can help with this.
Our director wants to have a report that will have the departments
(Retail stores) across the top, stores down the side and the RANKING of
the YTD dept sales within the chain in the cell.

Store/Dept 1 2 3 4
B 8 1 5 2
R 1 3 2 6

(etc.)
I have the following query, but it returns sequential numbers, not
starting from "1" for each department. I have tried the code from
other posts and from the MS knowledge base article
http://support.microsoft.com/kb/q120608/
but I keep getting a Syntax error on the "AS Rank" or right after the
"+1".

I don't need the sales $ or even the date in the results, but I do need
the Dept Number (CreditSalesTo here) and the Store number and the Rank.
I just can't "fill the cell" on the report with the rank as it is
here. I tried to think of a way to put this into a new table with a
make table query, but don't know how to get the Rank converted into a
sequential number, by Dept for that query.

Results and query below.
Many thanks,
sara

Sara,

Try looking at this post:

http://groups.google.com/group/comp....b6eb508f22fa9d

It should give you an idea about how to rank grouped data. Feel free
to ask any questions you have after reading it. Also, it's starting to
sound like you're moving toward something like a crosstab query. I'm
not fabulous with crosstab queries but I'll try to come up with
something to get you going if you need it.

James A. Fortune
CD********@Fort uneJames.com

Access Tip:

When using the RecordsAffected property to make sure a delete query has
finished when using the Execute method, first check to ensure that the
table has at least one record that will be deleted. Alternatively,
consider the StillExecuting Property.

[sara]

I am OK with Crosstab queries, but I am not understanding how the post
you referenced gets me to where I need to be. Do I need to write
multiple queries? I wasn't clear how the info in that post related to
what I had developed.

Thanks - sorry to be thick on this.
Sara

CD********@Fort uneJames.com wrote:

sara wrote:

I hope someone can help with this.
Our director wants to have a report that will have the departments
(Retail stores) across the top, stores down the side and the RANKING of
the YTD dept sales within the chain in the cell.

Store/Dept 1 2 3 4
B 8 1 5 2
R 1 3 2 6

(etc.)
I have the following query, but it returns sequential numbers, not
starting from "1" for each department. I have tried the code from
other posts and from the MS knowledge base article
http://support.microsoft.com/kb/q120608/
but I keep getting a Syntax error on the "AS Rank" or right after the
"+1".

I don't need the sales $ or even the date in the results, but I do need
the Dept Number (CreditSalesTo here) and the Store number and the Rank.
I just can't "fill the cell" on the report with the rank as it is
here. I tried to think of a way to put this into a new table with a
make table query, but don't know how to get the Rank converted into a
sequential number, by Dept for that query.

Results and query below.
Many thanks,
sara

Sara,

Try looking at this post:

http://groups.google.com/group/comp....b6eb508f22fa9d

It should give you an idea about how to rank grouped data. Feel free
to ask any questions you have after reading it. Also, it's starting to
sound like you're moving toward something like a crosstab query. I'm
not fabulous with crosstab queries but I'll try to come up with
something to get you going if you need it.

James A. Fortune
CD********@Fort uneJames.com

Access Tip:

When using the RecordsAffected property to make sure a delete query has
finished when using the Execute method, first check to ensure that the
table has at least one record that will be deleted. Alternatively,
consider the StillExecuting Property.

[CDMAPoster]

sara wrote:

I am OK with Crosstab queries, but I am not understanding how the post
you referenced gets me to where I need to be. Do I need to write
multiple queries? I wasn't clear how the info in that post related to
what I had developed.

Thanks - sorry to be thick on this.
Sara

This is not a trivial problem so some temporary thickness is O.K.

The first query, qryRankForMedia n, should have the basics. Try to
integrate that information into your query that is based on
qrySalesByDeptF orDate. If the ranking is in place within each group
then the final crosstab query will have something to work with --
theoretically speaking.

You're looking at:

qrySalesByDeptF orDate
qryRankingByDep tGroup
qryDisplayTotal s_Crosstab

Your DeptRANK corresponds to 'RankingWithinG roup' and your individual
dept (or store or Store/Dept, I'm not sure here) corresponds to
'Groups.' Also, how many records are in the original table?

I'll take a deeper look at this problem, probably tomorrow, and also
check to see if a crosstab query can be avoided.

James A. Fortune
CD********@Fort uneJames.com

[sara]

OK, James. IF I'm understanding you correctly, I may have mis-stated
what I have. I think I have the output from the first query
RankForMedian, where my query works - I get the Ranking for the Dept in
each Store within the Chain, but the RANK I calculated isn't 1, 2, 3
(which I could figure out how to put on a report - Crosstab query and
all), but it's increasing numbers, that I cannot put on the report.

My basic question is how can I get the RANK, which is currently 51, 62,
118, 119 (for the first stores - #1, 2, 4 and 6 - for Dept 1 Sales) to
show 1, 2, 3, 4 - which I could then grab in a crosstab query and put
on the report.

I think that's why the example you showed me isn't helping - it
"groups" and I seem to have already accomplished that.

I have 892 records (Number of depts * number of stores) in my ranking
query. I am not totally afraid of code, just not very experienced with
it, and have trouble writing from scratch. It could be one of those
"iterations " (not sure) through all the records in the ranking query,
somehow? I tried a make table and it won't run - I have to cancel it
after 20 mintues or so.

SO, I changed the first query to a Make Table, so I'd have the data
accessible, then I wrote the following Make table Query to try to
"convert" the Ranking to "ordinal within Dept". No luck; I still get
weird ranking numbers.

SELECT RankDeptSales1. SalesDate, RankDeptSales1. CreditSalesTo, (SELECT
COUNT(*) FROM [RankDeptSales1] AS T WHERE T.DeptRank <=
RankDeptSales1. DeptRank)+1 AS RANK, RankDeptSales1. StoreNum INTO
RankDeptSalesFo rADay
FROM RankDeptSales1
ORDER BY RankDeptSales1. CreditSalesTo;

Maybe this helps you help me - or someone else help. It's quitting
time in Boston, so I'll check in on this again tomorrow morning.

thanks so much. I just don't think this should be so hard!
Sara
CD********@Fort uneJames.com wrote:

sara wrote:

I am OK with Crosstab queries, but I am not understanding how the post
you referenced gets me to where I need to be. Do I need to write
multiple queries? I wasn't clear how the info in that post related to
what I had developed.

Thanks - sorry to be thick on this.
Sara

This is not a trivial problem so some temporary thickness is O.K.

The first query, qryRankForMedia n, should have the basics. Try to
integrate that information into your query that is based on
qrySalesByDeptF orDate. If the ranking is in place within each group
then the final crosstab query will have something to work with --
theoretically speaking.

You're looking at:

qrySalesByDeptF orDate
qryRankingByDep tGroup
qryDisplayTotal s_Crosstab

Your DeptRANK corresponds to 'RankingWithinG roup' and your individual
dept (or store or Store/Dept, I'm not sure here) corresponds to
'Groups.' Also, how many records are in the original table?

I'll take a deeper look at this problem, probably tomorrow, and also
check to see if a crosstab query can be avoided.

James A. Fortune
CD********@Fort uneJames.com

[CDMAPoster]

sara wrote:

OK, James. IF I'm understanding you correctly, I may have mis-stated
what I have. I think I have the output from the first query
RankForMedian, where my query works - I get the Ranking for the Dept in
each Store within the Chain, but the RANK I calculated isn't 1, 2, 3
(which I could figure out how to put on a report - Crosstab query and
all), but it's increasing numbers, that I cannot put on the report.

My basic question is how can I get the RANK, which is currently 51, 62,
118, 119 (for the first stores - #1, 2, 4 and 6 - for Dept 1 Sales) to
show 1, 2, 3, 4 - which I could then grab in a crosstab query and put
on the report.

I think that's why the example you showed me isn't helping - it
"groups" and I seem to have already accomplished that.

I have 892 records (Number of depts * number of stores) in my ranking
query. I am not totally afraid of code, just not very experienced with
it, and have trouble writing from scratch. It could be one of those
"iterations " (not sure) through all the records in the ranking query,
somehow? I tried a make table and it won't run - I have to cancel it
after 20 mintues or so.

SO, I changed the first query to a Make Table, so I'd have the data
accessible, then I wrote the following Make table Query to try to
"convert" the Ranking to "ordinal within Dept". No luck; I still get
weird ranking numbers.

SELECT RankDeptSales1. SalesDate, RankDeptSales1. CreditSalesTo, (SELECT
COUNT(*) FROM [RankDeptSales1] AS T WHERE T.DeptRank <=
RankDeptSales1. DeptRank)+1 AS RANK, RankDeptSales1. StoreNum INTO
RankDeptSalesFo rADay
FROM RankDeptSales1
ORDER BY RankDeptSales1. CreditSalesTo;

Maybe this helps you help me - or someone else help. It's quitting
time in Boston, so I'll check in on this again tomorrow morning.

thanks so much. I just don't think this should be so hard!
Sara

It just looked harder than it was.

Given:

MyTable
ID Rank
1 52
2 27
3 33
4 18

Want:

!MyQuery
ID NewRank
1 4
2 2
3 3
4 1

If you have no other Store/Dept's to worry about you can do something
like this:

MyQuery:
SELECT ID, (SELECT COUNT(A.Rank) FROM MyTable AS A WHERE A.Rank <=
MyTable.Rank) AS NewRank FROM MyTable;

Given:

MyTable
ID Rank SD
1 52 1
2 27 1
3 33 1
4 18 1
5 9 2
6 28 2
7 15 2
8 5 2

Want:

!MyQuery
ID NewRank SD
1 4 1
2 2 1
3 3 1
4 1 1
5 2 2
6 4 2
7 3 2
8 1 2

MyQuery:
SELECT ID, (SELECT COUNT(A.Rank) FROM MyTable AS A WHERE A.Rank <=
MyTable.Rank AND A.SD = MyTable.SD) AS NewRank, SD FROM MyTable;

I still think it would be easier to assign the rank within the group at
the same time you create your overall rank.

James A. Fortune
CD********@Fort uneJames.com

One of the more interesting people I met in Boston was Charles Blue Jr.
He had a degree in Physics from Lowell, possibly a Master's degree.
His interests were karate, cello and advanced combinatorics. Some of
his offshoot results from studying non-commutative algebras in an
attempt to model advanced physics problems were beyond anything I had
ever seen anywhere. He had also figured out variable substitutions
that could eliminate the second and third highest order terms from
polynomials, thereby improving linearization of feedback control
systems, whose differential equations transform into algebraic
equations after the application of Laplace Transforms. He was Puerto
Rican and didn't look very bright in spite of his great intellect. He
worked at a print shop. One day while studying advanced combinatorics
during some idle time at the print shop, one of his coworkers asked him
what he was doing. He said, "I'm learning how to count." The coworker
laughed because he really thought Charles didn't know how to count.
Charles never let on.

[sara]

(Great story, BTW)

That did it!!!!

I took your second code (ranking within group when creating overall
rank)
SELECT ID, (SELECT COUNT(A.Rank) FROM MyTable AS A WHERE A.Rank <=
MyTable.Rank AND A.SD = MyTable.SD) AS NewRank, SD FROM MyTable;

and modified to:

SELECT qrySalesByDeptF orDate.SalesDat e,
qrySalesByDeptF orDate.CreditSa lesTo, qrySalesByDeptF orDate.YTDSales ,
qrySalesByDeptF orDate.StoreNum , (SELECT COUNT(A.YTDSAle s) FROM
qrySalesByDeptf orDate AS A WHERE A.YTDSAles >=
qrySalesByDeptf orDate.YTDSAles AND A.CreditSalesTo =
qrySalesByDeptf orDate.CreditSa lesTo) AS RANK INTO RankDeptSalesFo rADay
FROM qrySalesByDeptF orDate
ORDER BY qrySalesByDeptF orDate.CreditSa lesTo,
qrySalesByDeptF orDate.YTDSales ;

Put it into a MakeTableQuery, so that I can pull from that (it's
easier) for the Crosstab report. Also, I can have the Table created in
a separate process, then run the Report if needed.

Thanks ever so much! It looks to me like I was missing the AND saying
the Dept would equal the Dept (Which [I think] is what you were saying
about the qrouping).

Sara

CD********@Fort uneJames.com wrote:

sara wrote:

OK, James. IF I'm understanding you correctly, I may have mis-stated
what I have. I think I have the output from the first query
RankForMedian, where my query works - I get the Ranking for the Dept in
each Store within the Chain, but the RANK I calculated isn't 1, 2, 3
(which I could figure out how to put on a report - Crosstab query and
all), but it's increasing numbers, that I cannot put on the report.

My basic question is how can I get the RANK, which is currently 51, 62,
118, 119 (for the first stores - #1, 2, 4 and 6 - for Dept 1 Sales) to
show 1, 2, 3, 4 - which I could then grab in a crosstab query and put
on the report.

I think that's why the example you showed me isn't helping - it
"groups" and I seem to have already accomplished that.

I have 892 records (Number of depts * number of stores) in my ranking
query. I am not totally afraid of code, just not very experienced with
it, and have trouble writing from scratch. It could be one of those
"iterations " (not sure) through all the records in the ranking query,
somehow? I tried a make table and it won't run - I have to cancel it
after 20 mintues or so.

SO, I changed the first query to a Make Table, so I'd have the data
accessible, then I wrote the following Make table Query to try to
"convert" the Ranking to "ordinal within Dept". No luck; I still get
weird ranking numbers.

SELECT RankDeptSales1. SalesDate, RankDeptSales1. CreditSalesTo, (SELECT
COUNT(*) FROM [RankDeptSales1] AS T WHERE T.DeptRank <=
RankDeptSales1. DeptRank)+1 AS RANK, RankDeptSales1. StoreNum INTO
RankDeptSalesFo rADay
FROM RankDeptSales1
ORDER BY RankDeptSales1. CreditSalesTo;

Maybe this helps you help me - or someone else help. It's quitting
time in Boston, so I'll check in on this again tomorrow morning.

thanks so much. I just don't think this should be so hard!
Sara

It just looked harder than it was.

Given:

MyTable
ID Rank
1 52
2 27
3 33
4 18

Want:

!MyQuery
ID NewRank
1 4
2 2
3 3
4 1

If you have no other Store/Dept's to worry about you can do something
like this:

MyQuery:
SELECT ID, (SELECT COUNT(A.Rank) FROM MyTable AS A WHERE A.Rank <=
MyTable.Rank) AS NewRank FROM MyTable;

Given:

MyTable
ID Rank SD
1 52 1
2 27 1
3 33 1
4 18 1
5 9 2
6 28 2
7 15 2
8 5 2

Want:

!MyQuery
ID NewRank SD
1 4 1
2 2 1
3 3 1
4 1 1
5 2 2
6 4 2
7 3 2
8 1 2

MyQuery:
SELECT ID, (SELECT COUNT(A.Rank) FROM MyTable AS A WHERE A.Rank <=
MyTable.Rank AND A.SD = MyTable.SD) AS NewRank, SD FROM MyTable;

I still think it would be easier to assign the rank within the group at
the same time you create your overall rank.

James A. Fortune
CD********@Fort uneJames.com

One of the more interesting people I met in Boston was Charles Blue Jr.
He had a degree in Physics from Lowell, possibly a Master's degree.
His interests were karate, cello and advanced combinatorics. Some of
his offshoot results from studying non-commutative algebras in an
attempt to model advanced physics problems were beyond anything I had
ever seen anywhere. He had also figured out variable substitutions
that could eliminate the second and third highest order terms from
polynomials, thereby improving linearization of feedback control
systems, whose differential equations transform into algebraic
equations after the application of Laplace Transforms. He was Puerto
Rican and didn't look very bright in spite of his great intellect. He
worked at a print shop. One day while studying advanced combinatorics
during some idle time at the print shop, one of his coworkers asked him
what he was doing. He said, "I'm learning how to count." The coworker
laughed because he really thought Charles didn't know how to count.
Charles never let on.