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 qrySalesByDeptForDate.SalesDate,
qrySalesByDeptForDate.CreditSalesTo, qrySalesByDeptForDate.YTDSales,
qrySalesByDeptForDate.StoreNum, (SELECT COUNT(1) FROM
[qrySalesBYDeptForDate] AS T WHERE T.[YTDSales] >=
[qrySalesByDeptForDate].[YTDSales])+1 AS DeptRANK
FROM qrySalesByDeptForDate
ORDER BY qrySalesByDeptForDate.CreditSalesTo,
qrySalesByDeptForDate.YTDSales DESC; 6 3793
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********@FortuneJames.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.
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********@FortuneJames.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********@FortuneJames.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 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, qryRankForMedian, should have the basics. Try to
integrate that information into your query that is based on
qrySalesByDeptForDate. 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:
qrySalesByDeptForDate
qryRankingByDeptGroup
qryDisplayTotals_Crosstab
Your DeptRANK corresponds to 'RankingWithinGroup' 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********@FortuneJames.com
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
RankDeptSalesForADay
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********@FortuneJames.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, qryRankForMedian, should have the basics. Try to
integrate that information into your query that is based on
qrySalesByDeptForDate. 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:
qrySalesByDeptForDate
qryRankingByDeptGroup
qryDisplayTotals_Crosstab
Your DeptRANK corresponds to 'RankingWithinGroup' 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********@FortuneJames.com
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
RankDeptSalesForADay
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********@FortuneJames.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.
(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 qrySalesByDeptForDate.SalesDate,
qrySalesByDeptForDate.CreditSalesTo, qrySalesByDeptForDate.YTDSales,
qrySalesByDeptForDate.StoreNum, (SELECT COUNT(A.YTDSAles) FROM
qrySalesByDeptforDate AS A WHERE A.YTDSAles >=
qrySalesByDeptforDate.YTDSAles AND A.CreditSalesTo =
qrySalesByDeptforDate.CreditSalesTo) AS RANK INTO RankDeptSalesForADay
FROM qrySalesByDeptForDate
ORDER BY qrySalesByDeptForDate.CreditSalesTo,
qrySalesByDeptForDate.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********@FortuneJames.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
RankDeptSalesForADay
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********@FortuneJames.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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Irene |
last post by:
Hi all again,
Well, I have my Athletics database with Athletes, Competitions, Scores
tables.
I have a ranking query where I get back the list of the
competitions-athletes and scores...
|
by: Dalan |
last post by:
I'm not certain if this is doable in the manner explained below, but
I'm certain that there have been Access developers who have done
something similar. So whatever insight and assistance that can...
|
by: ED |
last post by:
I currently have vba code that ranks employees based on their average
job time ordered by their region, zone, and job code. I currently
have vba code that will cycle through a query and ranks each...
|
by: Joseph Bloch |
last post by:
In all the threads on ranking in Access queries, I've not seen
anything that deals with ranking against a subset of the query
results. What I need to do is take the following query results:
Dept...
|
by: Jeff Boes |
last post by:
I hope this helps someone else ... I had struggled some time ago with
attempts to get a rank of values query to work, but then I gave up and
set it aside. I had another reason to attack it, and in...
| |
by: valglad |
last post by:
Hi,
The question below was posted about 4 years ago and noone was able to
answer it back then. I have virtually the same type of problem so would
appreciate if anyone can help.
Thanks
...
|
by: Stan |
last post by:
I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for...
|
by: AnndieMac |
last post by:
I have an Access 2002 database of inventory count results, and I have been asked to create summaries of the items with the most losses at a store, region and national level. Individually, I have been...
|
by: Chris |
last post by:
I was wodering if there was a way to rank numbers in a query like this
#'s Rank
100 1
99 2
98 3
98
97 5
96 6
96
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |