By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

Needing help with a MS Access Query (Ranking Order)

P: 2
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Master-HeldPay-Summary-Final] ( CC, HeldPayAccounts, ASG, [Peer Group], Command, OrderNo, [%HeldPay], [HeldPay-Rank] )
  2. SELECT [Master-HeldPay-Summary].CC, [Master-HeldPay-Summary].HeldPayAccounts, [Master-HeldPay-Summary].ASG, [Master-HeldPay-Summary].[Peer Group], [Master-HeldPay-Summary].Command, [Master-HeldPay-Summary].OrderNo, [Master-HeldPay-Summary].[%HeldPay] AS Expr2, (Select count([Master-HeldPay-Summary].[%HeldPay]) from [Master-HeldPay-Summary]  as [SubRank]  
  3. where 
  4. (([Master-HeldPay-Summary].[%HeldPay] >= [SubRank].[%HeldPay]) 
  5. and ([Master-HeldPay-Summary].[OrderNo] =  [SubRank].[OrderNo]))) AS Rank
  6. FROM [Master-HeldPay-Summary]
  7. GROUP BY [Master-HeldPay-Summary].CC, [Master-HeldPay-Summary].HeldPayAccounts, [Master-HeldPay-Summary].ASG, [Master-HeldPay-Summary].[Peer Group], [Master-HeldPay-Summary].Command, [Master-HeldPay-Summary].OrderNo, [Master-HeldPay-Summary].[%HeldPay]
  8. ORDER BY [Master-HeldPay-Summary].OrderNo;
The above statement is in MS Access 2003 SQL view of Query. Some where I need to add the
Expand|Select|Wrap|Line Numbers
  1. "Select ROW_NUMBER() over (Partition by item order by item) as row_Number?
The query is Segragated/Grouped by ORDERNO and the Ranking is based on %HELDPAY
Oct 2 '09 #1
Share this Question
Share on Google+
2 Replies


P: 5
Can you submit an image of the relevant table structure which will help us to answer faster without learning your database. Access has a view, so you can simply get a screenshot.

Regards,

Neo
Oct 3 '09 #2

P: 2
I could only get it out in XLS do to it's size. The 0.00% should start at Zero and then the 1 should start with the lowest %.

It is grouped by the Order Number, I need to rank the data based on the %, grouped by the Order Number. The only Duplicate allowed should be the 0.00 %, all others no Duplicated and should start with 1, 2, 3, 4, 5, 6, ect......


Thanks for looking at this for me, I am truely lost. I've been doing a lot of research, the closest this I can find close is "Rown_Number" but I can't get it to work.


Thanks again.
Oct 5 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.