Rank Query With Groups (Array?)

Hello, I have a table of data with grades which are grouped by study.  I need 
to rank the grades relative to all the other grades in the same study.  Below 
is an example the study and grade fields with the desired outcome for the 
rank.
Study Grade Rank
11111  89     1
11111  75     2
11111  65     3
22222  99     1
22222  87     2
Hopefully this can be done with simple SQL in a query and not with code but 
I am open to anything that works.  Many thanks in advance for your time and 
expertise!
0
Utf
2/4/2010 4:20:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2820 Views

Similar Articles

[PageSpeed] 49

Try this --
SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE 
YourTable.Study = [XX].Study AND YourTable.Grade <= [XX].Grade) AS Rank  
FROM YourTable
ORDER BY Study, Grade DESC;
 
-- 
Build a little, test a little.


"dmasch" wrote:

> Hello, I have a table of data with grades which are grouped by study.  I need 
> to rank the grades relative to all the other grades in the same study.  Below 
> is an example the study and grade fields with the desired outcome for the 
> rank.
> Study Grade Rank
> 11111  89     1
> 11111  75     2
> 11111  65     3
> 22222  99     1
> 22222  87     2
> Hopefully this can be done with simple SQL in a query and not with code but 
> I am open to anything that works.  Many thanks in advance for your time and 
> expertise!
0
Utf
2/4/2010 5:19:03 PM
Works perfectly. Thanks Karl!

"KARL DEWEY" wrote:

> Try this --
> SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE 
> YourTable.Study = [XX].Study AND YourTable.Grade <= [XX].Grade) AS Rank  
> FROM YourTable
> ORDER BY Study, Grade DESC;
>  
> -- 
> Build a little, test a little.
> 
> 
> "dmasch" wrote:
> 
> > Hello, I have a table of data with grades which are grouped by study.  I need 
> > to rank the grades relative to all the other grades in the same study.  Below 
> > is an example the study and grade fields with the desired outcome for the 
> > rank.
> > Study Grade Rank
> > 11111  89     1
> > 11111  75     2
> > 11111  65     3
> > 22222  99     1
> > 22222  87     2
> > Hopefully this can be done with simple SQL in a query and not with code but 
> > I am open to anything that works.  Many thanks in advance for your time and 
> > expertise!
0
Utf
2/4/2010 6:02:01 PM
Reply:

Similar Artilces:

Delete First storage group
I am running Exchange 2003 server with windows 2000 server. I created a new Storage group with a new Mailbox store in Exchange system manager. I then moved all available mailboxes to the new storage group successfully. I would like to delete the First storage group along with the priv.edb and priv.stm database files but I am unable to do so because the system attendant and SMTP mailboxes are still in the storage group. How can I move or delete these mailboxes to I can delete the Storage group and database files successfully? Thank you, Rob ...

How do I import multiple vcards in groups into the contacts secti.
I have been using another email program for years called Becky. Now I have office 2003 I am going to change and use outlook so it integrates all in one package. I need to know if it possible to import groups of addresses as I had in the Becky Email or do I have do this as one and then put them back into groups. Also the emails can they be imported to the Outlook. Thanks in advance Jeff ...

Windows 7 Discussion Group
Is there one? If so and I missed it I apologize. Thanks "RHinNC" <rhinnc@[get rid of this] hotmail.com> wrote in message news:OEORv3yhKHA.5604@TK2MSFTNGP04.phx.gbl... > Is there one? If so and I missed it I apologize. Thanks > There are Microsoft forums, but no Microsoft newsgroups. There is an alt.windows7.general newsgroup, though, if your ISP carries it. -- SC Tom On 12/27/2009 2:51 PM, RHinNC wrote: > Is there one? If so and I missed it I apologize. Thanks > > I have been using the alt.windows7.general which is available a free...

Grouping the results of Networkdays
I am working on a workbook. Using NETWORKDAYS I have come up with the number of days between the two days with specific dates not to count. For this I have 30 worksheets coming from different schools with their own workbook. On a separate workbook I want to pull how many counted days are under 65 and how many are 65 and over. Each school has its own line to show the differences between each school. Is there a pattern of which schools are consistently over, etc. Help? Any ideas are much appreciate. Thanks fourskunks I have looked in the postings and have gotten myself lost. Summa...

Identify Duplicates (Group Records First)
Hello, I have an invoice table that includes the fields CustomerNumber and DivisionNumber. Customers can deal with multiple divisions. CustomerNumber DivisionNumber ABC Co 10 ABC Co 10 ABC Co 10 Smith Inc 10 Smith Inc 10 Jones Corp 20 Jones Corp 20 Jones Corp 10 XYZ Inc 20 XYZ Inc 20 I am trying to come up with a query that will identify that Jones Corp has records in both division 20 and 10. I would like the results ...

Simple query question 08-05-10
Hi, I'm using SQL Server 2005. What I want is to return a column "venue", but if that column is null, I want to return the value of the column "site" (in either case, I want the column header to be "venue"). How do I construct such a query? Thanks, - Dave You can use expression with COALESCE and alias the expression as venue: SELECT COALESCE(venue, site) AS venue... -- Plamen Ratchev http://www.SQLStudio.com Try this: SELECT COALSECE(venue, site) AS venue FROM my_table -- Gert-Jan laredotornado wrote: > > Hi, &...

Help Query.
Hi, I have 2 tables, one it has customers who are past due and another table that has customers who have paid and not past due after a certain period. My objective is to find the customers who hasn't paid yet. I did a query, joined by account #, this query will return customers who has paid after they were past due. But I am looking for account IDs of customers who are still past due. Anyway to do this? Thansk for the help! Try the query wizard about finding unmatched rows. It seems you need rows from the first table that are NOT in the second table. Hoping it may help, Vander...

Add time to a date time group
Hi, thanks for any help in advance I have my date and time displayed in this format dd hhmm "K" mmm yy which shows up as 03 1800 K Sep 08. I would like a formula that will add 30 min to this time group so it becomes 03 1830 K Sep 08 Assume your date/time is in A1, then you can do this in another cell: =3DA1+30/24/60 Format the cell in the same way as A1 (using the Format Painter icon). Times are stored in Excel as fractions of a 24-hour day, hence the divisions by 24 and 60. Hope this helps. Pete On Sep 4, 12:28=A0am, Patrick <Patr...@discussions.microsoft.com> wr...

Query re Registration & Login to Outlook forum
I am not clear what function the Registration and Login have on the website of the Outlook Forum. I can post messages to the Outlook Forum through my ISP's newsgroup server, apparently without registering or logging in to the Forum. And after posting a message through the newsgroup, I Googled the keywords "microsoft forums outlook", then selected the option at http://office-outlook.com/outlook-forum/ . There I could read my own posting, and I could read the reply to it from a well informed reader. However, I was not able to reply at the website, only from OutlookExpress...

Display Array from VBA
Hi: At the end of a VBA macro I have a set of results saved in an array e Results (1 to 100). How do I display the results in a specified range in a worksheet? I can do it by looping through and doing each one at a time but wha line of code would do it in one go (if there is one)? Thanks Bil -- Message posted from http://www.ExcelForum.com Hi Bill, Try: Sheets(1).Range("A1").Resize(100,1)=Results --- Regards, Norman "BillS >" <<BillS.163hnd@excelforum-nospam.com> wrote in message news:BillS.163hnd@excelforum-nospam.com... > Hi: > > At the en...

Date expression in Query
I am getting 'undefined function' w/this expression in my qry. Expr1: IIf(today()<[PoP_End_Dt],"cur","exp") How do I insert or use todays date in a Query. Expr1: IIf(Date()<[PoP_End_Dt],"cur","exp") -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Steve Stad" wrote: > I am getting 'undefined function' w/this expression in my qry. > Expr1: IIf(today()<[PoP_End_Dt],"cur","exp") > > How do I insert or use to...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Round up a decimal in a query field
Hopefully this is an easy question. How do you round up a number in a query field? Excel has the ROUNDUP function, but I can't find anything similar in Access. Can anyone help? Function:round([tablename!fieldname,2]) I think. It may be round([tablesname!fieldname],2) "bigomega73" wrote: > Hopefully this is an easy question. How do you round up a number in a query > field? Excel has the ROUNDUP function, but I can't find anything similar in > Access. Can anyone help? Thanks Golfinray, but that only rounds to the nearest decimal place. What I want is the numb...

Help with an array
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C4391B.93740150 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =3D{SUM(VLOOKUP($B36,Returns,COLUMN(INDIRECT($B$5:$B$25)))*D$5:D$25)} This formula won't work as an array, but it will work like this: =3D(VLOOKUP($B36,Returns,COLUMN(INDIRECT($B$5))*D$5 Either way, the Vlookup portion works okay, as does INDIRECT, but the = COLUMN stops working when I convert it to an array. Help is sincerely appreciated! ___________________________ Robert Ades Accountancy Corp....

how to access data in a query
I don't know how to set the source property on a query? or how does the query know what db to query? It now says (current) so if you have another db open then it queries the current db when you open the query in datashett view? I'm assuming this is what is meant by the query source property? I have an excel spreadsheet that I would like to populate my db so I can test the query. I tried setting the source property which says (current) to [tables.tblTest] on the query just to get some data in it says the name is invalid. (I imported from an Excel spreadsheet some test data into...

Recorset not updateable in a crosstab query
I have created a crosstab query based on just one linked table using the Access wizard and when I finished I get the erro "The recorset is not updateable", clicking on OK it shows me the tahe results in the corresponding table, but if I try to see the structure of the query it shows me the structure mask but saying that in the expression "Format([DPC]:"aaaa")" there to many closing brackets. I copied the table to the current DB to avoid any links problems but the result is the same. Can anybody tell me how to solve the problem? Thanks very muc...

Linq query
Hello, I have the following list: IList<Int32> roles = { 1, 2, 3 } I need to get all users where in user.Roles there is at least one Role which is id 1, 2 OR 3. var a = users.Where(u => u.Roles. ??? ); How can I do this? Thanks, Miguel shapper wrote: > I have the following list: > > IList<Int32> roles = { 1, 2, 3 } > > I need to get all users where in user.Roles there is at least one Role > which is id 1, 2 OR 3. > var a = users.Where(u => u.Roles. ??? ); users.Where(u => u.Roles.Any(r => roles.Contains(r))) ass...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...

Using Mail Merge with Criteria in a Access query
Hi; In access 2000-2003 it was possible to use the Mail Merge function in Word to access a query that has criteria required for input. You can select the Word document and then the database is opened and the criteria box is opened for input (Enter Parameter Value). This then produces the data for the letters. It used a DDE connection between Word and Access. In Office 2007 the mail merge help document says to go to the Access database and select the query and lists how to link to an existing or new letter. The DDE connection is listed but does not attach to the query? It alway...

How do I run a SQL query against a MS SQL Server database from exc
How do I run a SQL query against a MS SQL Server database from within Excel 2002? I know it can be done somehow using the SQL.Request addin function. However my attempts using this function have continually failed. Can some one please provide a working example to the pubs or Northwind dbs that I can work with and use to help debug my attempt? Thank you! ...

Nest members of a query-based distribution list
Hello Is there any way to nest members of a query-based distribution list within the list on the global address list and maybe make the list title bold. I know this can be done but cannot see where to do it Thanks Robin It's ok I have managed it now http://support.microsoft.com/kb/319213/en-us Thanks anyway Robin "robinwilson16" wrote: > Hello > > Is there any way to nest members of a query-based distribution list within > the list on the global address list and maybe make the list title bold. > > I know this can be done but cannot see where to do it...

Linking a Combo box to variable SQL query in subform
I am in the process of creating a relatively small D'Base that will monitor employee competency. There are several tables and relationships within this, but I don't consider this to be a big project (although my first post-graduation in the real world). I have configured the records to be spread over two tables. One is the tbl_ReviewHearder (containing employee info', review date), the second, tbl_ReviewContent contains the competency information. This is based on a 1-many relationship (1 header, lots of content) I have written VB to filter the combo boxes, but I am having ...

MS Access & Citrix
Hi, all. We have a home-grown Access application that approximately 50 users use to access different, but related, databases on different Linux servers across the US. We are trying to combine all these different databases into one database that everyone can access through a Citrix server. In the initial (very) testing phase, we have found some strange problems with our Access app that may be related to the programming within the application or may be related to the Citrix side of things (although when we try to do an analagous task going just through Access itself we don't have the same...

ranking numbers in a column
I have 100 (100 rows) with different numbers all in one column. I have ranked them in another column by largest to smallest in the past. The 100 numbers change from time to time. I would like the rankings to automatically update. I wish not to sort. Is there a formula? Thanks, Darryl Try the RANK Function. If my comments have helped please hit Yes. Thanks. "Darryl" wrote: > I have 100 (100 rows) with different numbers all in one column. I have > ranked them in another column by largest to smallest in the past. The 100 > numbers change...

Array saving question (lbound)
Hello! I am saving an array to a file and then load it again. Open sPath For Binary As #iFile1 If uLoad Then Get #iFile1, , uArray Else Put #iFile1, , uArray End If My array looks like this: Redim myArr(1 to 3) as long myArr(1) = 100 myArr(2) = 500 myArr(3) = 10000 When I load it again, I say: Redim MyNewArr(1 to 3) and call the above sub. For some reason, MyNewArr now looks like this: MyNewArr(0) = 100 MyNewArr(1) = 500 MyNewArr(2) = 3 When I try to access MyNewArr(3) an out of bounds error is thrown. Can somebo...