Ranking Query

Apr 3, 2007

I've found a few similar posts to this, but nothing exactly answers what I need.

I'm using the following query to get a list of the top five salesmen, based on the number of customers per salesman, per month. I've added a rankings field, based on a query I found here on the forum

SELECT TOP 5 [SalesResults_1].CurrMonthClients,
(SELECT count(*) FROM [SalesResults]
WHERE
[SalesResults].[CurrMonthClients] >=
[SalesResults_1].CurrMonthClients;) AS Rank,
[SalesResults_1].SPName
FROM
[SalesResults] AS [SalesResults_1]
ORDER BY
[SalesResults_1].CurrMonthClients DESC;"


This works perfectly, with one exception.
If two salesmen have the same number of clients and both are the joint highest, their rank is displayed as 2, rather than the desired 1.

Can anyone spot some simple change to the query that would take this eventuality into account?

Thanks in advance.

View Replies


ADVERTISEMENT

How To Run A Subtotal And Ranking Query

May 27, 2005

Have a table containing a column called "BOC" (Budget Object Code) that contains approximately 85 different 4 digit codes, with one selected for each row. (See sample below). Table has 30,000 rows of data. What I need to do is run a query that will give me a subtotal dollar amount for each unique BOC, and then display the results in descending order for both Obligations and Expenditures. Any ideas??

Sample Data:

COST CENTER BOC OBLIGATIONS EXPENDITURES
119900 2670 1000.00 500.00
119901 2685 2000.00 1000.00
119999 2670 5000.00 2000.00
119920 3130 5000.00 5000.00

View 1 Replies View Related

Qualification Ranking Query???

Oct 17, 2006

I am trying to build a report that will rank employees, based on their qualifications for a promotion to a specific position. I need to build one that will work for every position in the organization, based on the requirements for the specific position. I know this is vague, but before I go into a long explanation, can anyone tell me if this is even possible in Access?? Thank you!!

KellyJo

View 1 Replies View Related

Advanced Ranking Query Options…

May 23, 2005

I have been developing a website where users enter details about positions that they would like to fill.
Other users enter details of positions that are available.
I currently run a ranking query for users that matches from the options they enter which positions best match their profile in a ranked order.

I'm using an Access Database and ASP.
The query is taking a very long time to run.

This is how I have the query built at the moment.


Code:strSQL = "SELECT qryApplicant.* FROM qryApplicant WHERE (((qryApplicant. ApplicantRID) & ''='" & applicantRef & "'));" call getFromDatabase(strSQL, objTempRS, currentPage) if not objTempRS.EOF then applicantWants = True applicantGender = objTempRS.Fields("ApplicantGender") applicantAge = objTempRS.Fields("ApplicantAge") applicantBSMinDuration= objTempRS.Fields("ApplicantBSMinDuration") applicantBSMaxDuration= objTempRS.Fields("ApplicantBSMaxDuration") applicantBSEarlyDate= objTempRS.Fields("ApplicantBSEarlyDate") applicantBSLateDate= objTempRS.Fields("ApplicantBSLateDate") applicantLanguage= objTempRS.Fields("ApplicantLanguage") applicantPosition= objTempRS.Fields("ApplicantPosition") applicantPositionType= objTempRS.Fields("ApplicantPositionType") applicantNationality= objTempRS.Fields("ApplicantNationality") end if if applicantWants then applicantPoss = 0 lineTemp = "" strSQL = "" strSQL = strSQL & "SELECT " & applicantRef & " AS ApplicantRef" strSQL = strSQL & ", qryEmployer.EmployerRID" ' ****************************** Check Gender ****************************** if applicantGender & "" <> "" then lineTemp = lineTemp & "(IIf(([EmployerCSGender]='any'),50,(IIf(([EmployerCSGender]='" & applicantGender & "'),50,1))))" else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 50 ' ****************************** Check CheckAge ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if isNumeric(applicantAge) then lineTemp = lineTemp & "IIf(([EmployerCSMinAge]<=" & applicantAge & "),IIf(([EmployerCSMaxAge]>=" & applicantAge & "),20,0),0)" else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check CheckMinMaxDuration ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if isNumeric(applicantBSMinDuration) then if isNumeric(applicantBSMaxDuration) then lineTemp = lineTemp & "IIf(([EmployerCSMinDuration]<=" & applicantBSMaxDuration & "),IIf(([EmployerCSMaxDuration]>=" & applicantBSMinDuration & "),20,0),0)" else lineTemp = lineTemp & "IIf(([EmployerCSMaxDuration]>=" & applicantBSMinDuration & "),20,0" end if else if isNumeric(applicantBSMaxDuration) then lineTemp = lineTemp & "IIf(([EmployerCSMinDuration]<=" & applicantBSMaxDuration & "),20,0" else lineTemp = lineTemp & "(20)" end if end if applicantPoss = applicantPoss + 20 ' ****************************** Check CheckEarlyLateDuration ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if (applicantBSEarlyDate & "" <> "") then if (applicantBSLateDate & "" <> "") then lineTemp = lineTemp & "(IIf([EmployerCSLateDate]&''<>'',(IIf(([EmployerCSLateDate])>=" & DATE_DELIMITER & AusDate(applicantBSEarlyDate) & DATE_DELIMITER & ",(IIf([EmployerCSEarlyDate]&''<>'',(IIf(([EmployerCSEarlyDate])<=" & DATE_DELIMITER & AusDate(applicantBSLateDate) & DATE_DELIMITER & ",20,0)),0)),0)),0))" else lineTemp = lineTemp & "(IIf([EmployerCSEarlyDate]&''<>'',(IIf(([EmployerCSEarlyDate])<=" & DATE_DELIMITER & AusDate(applicantBSLateDate) & DATE_DELIMITER & ",20,0)),0))" end if else if (applicantBSLateDate & "" <> "") then lineTemp = lineTemp & "(IIf([EmployerCSLateDate]&''<>'',(IIf(([EmployerCSLateDate])>=" & DATE_DELIMITER & AusDate(applicantBSEarlyDate) & DATE_DELIMITER & ",20,0)),0))" else lineTemp = lineTemp & "(20)" end if end if applicantPoss = applicantPoss + 20 ' ****************************** Check Language ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantLanguage & "" <> "" then getMultiValues applicantLanguage, ":", arrSplit01, arrSplit01Max itemTemp = "0" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerLanguage] Like '%" & Left(arrSplit01(intCounter01),3) & "_%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check Nationality ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantNationality & "" <> "" then getMultiValues applicantNationality, ":", arrSplit01, arrSplit01Max itemTemp = "0" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerNationality] Like '%" & arrSplit01(intCounter01) & ":%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check Position ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantPosition & "" <> "" then itemTemp = "" getMultiValues applicantPosition, ":", arrSplit01, arrSplit01Max itemTemp = "0" if instr(applicantPosition,"ANY") then itemTemp = "(10)" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then if arrSplit01(intCounter01) <> "ANY" then itemTemp = "(IIf(([EmployerPosition] Like '%" & Left(arrSplit01(intCounter01),3) & "_%'),20," & itemTemp & "))" end if end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 ' ****************************** Check PositionType ****************************** if lineTemp & "" <> "" then lineTemp = lineTemp & "+" if applicantPositionType & "" <> "" then getMultiValues applicantPositionType, ":", arrSplit01, arrSplit01Max itemTemp = "0" strType1 = "" for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "ANY" then strType1 = "(10)" next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "rea" then strType1 = "(IIf(([EmployerPositionType] Like '%r??:%'),15," & itemTemp & "))" end if next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) = "coa" then strType1 = "(IIf(([EmployerPositionType] Like '%c??:%'),15," & itemTemp & "))" end if next if strType1 <> "" then itemTemp = strType1 for intCounter01=0 to arrSplit01Max if arrSplit01(intCounter01) <> "" then itemTemp = "(IIf(([EmployerPositionType] Like '%" & arrSplit01(intCounter01) & ":%'),20," & itemTemp & "))" end if next lineTemp = lineTemp & itemTemp 'Response.Write itemTemp & "<br />" & " " else lineTemp = lineTemp & "(0)" end if applicantPoss = applicantPoss + 20 strSQL = strSQL & ", (" & lineTemp & ") AS Total, qryEmployer.EmployerActive, '" & applicantPoss & "' AS Poss, qryEmployer.* FROM qryEmployer " strSQL = strSQL & "WHERE (((qryEmployer.EmployerActive)=True) AND ((qryEmployer.EmployerLastLoginDate)>" & DATE_DELIMITER & dateAdd("d",(optionDaysListed * -1),now()) & DATE_DELIMITER & ")) " strSQL = strSQL & "ORDER BY (" & lineTemp & ") DESC, qryEmployer.EmployerLastLoginDate DESC;"


Does anyone know any other ways to build this type of Query to show result in a ranked order.

View 1 Replies View Related

Queries :: Ranking Records Within Query

Mar 4, 2014

I'm looking for a way of ranking records within a query. Access doesn't have a Rank formula like in excel ...

View 5 Replies View Related

Queries :: Ranking Matched Records In A Query

Aug 18, 2013

I have a question regarding ranked matches in a query. My data is set up as follows:

Item, Customer, Shipping Plant
65432, 104321, 221
65433, 104321, 221 etc.

For some items and customer combinations, there are multiple plants that can provide the same item item to a customer. I would like to create a ranked column that puts a 1,2,3 and so on for these occurrences. So if a customer get one item from only one plant, the rank would be 1, but if a customer gets the same item from two plants, the first would be rank 1 and second, rank 2 (order doesn't matter).

View 1 Replies View Related

Queries :: DCount Ranking Query Not Working

Sep 10, 2013

I have a DCount Ranking query that worked fine last week and with this week's data came up with a glitch. Here is my DCount statement in the query:

Rank: CInt(DCount("*","qry_YTD_Territory_Totals","[Total]>=" & [Total]))

Here is my result which you can clearly see is not a tie.

Territory Total Rank
A 540.86 1
B 398.85 1
C 341.75 3

[code]...

View 1 Replies View Related

Queries :: Running Count In A Query By Group - Error In Ranking Column Result

Mar 16, 2013

Query is based on 1 table" tblTimeCnv_AgeGroups

Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4

See attachment

My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])

In the Ranking column the result is: #error in the first 3 rows then zeroes

View 3 Replies View Related

Ranking

Sep 27, 2005

I've got a football table ranked by Points and Goal Difference:

e.g.

Team Pts Goal Diff
C 4 +2
A 4 +1
B 0 -3

and I want to introduce another field automatically (in a query) which gives the position of each team:

Pos Team Pts Goal Diff
1 C 4 +2
2 A 4 +1
3 B 0 -3

Can anyone help?

Thanks a lot

Jimbob

View 7 Replies View Related

Ranking

Jul 4, 2006

Could any one tell me how to change the ranking of a field from 1,2,3,4,5,6,....1000 to 1st,2nd,3rd,4th.5th.6th........1000th.

Nested if could only give upto 15, and another method that is i used worked but made the query to run too slow.

tHANKS

View 3 Replies View Related

Ranking

Apr 9, 2008

I have a simple query which lists names and next to each name is a sum of sales. I have sorted this sum field decending and now I need to add a third column in the query which ranks these.

Process Name: Sum of Variation: Rank
Process A 100 1
Process B 98 2
Process C 65 3
Process D 52 4

I am a beginner in access and am unsure how to automatically create the rank field.

View 7 Replies View Related

How To Add A "ranking" To A Query

Nov 2, 2005

Hi guys,

Simple Question perhaps, but it has me stumped. I have a rather complex crosstab query which is used on a website (ASP) that shows a ranking for a competition. Now I need to add the rank itself, so say, just numbers from 1 to 10 which simply correspond with the row # since its ordered properly (descending by score). Sounds simple enough, but I have NO idea how to do that..

Anyone got an idea ?

Thx,
Vertigo

View 5 Replies View Related

Ranking Data

Feb 12, 2007

Is there a way to write a formula in an Access query that would stack rank a group of numbers or dates in a field? I have a set of order dates and i want to rank by the most recent one. Being the recent orders in the top 20%.

If orderdate is in the top 20%, return a 5.
If Orderdate is in the next 20%, return a 4.
If Orderdate is in the next 20%, return a 3.
If orderdate is in the next 20%, return a 2.
Then the remaining numbers, return a 1

View 2 Replies View Related

Ranking In A Listbox

Jul 27, 2006

I have a list box that stores the project names in a Form. The user wants to have the ability to order the project in a way they want. And the report need to present this order in the same way. In other word, user wants to move the proejcts around in the listbox.

Could anyone give a hand? Thank you.

View 4 Replies View Related

Record Ranking By RecordNumber? Please Help

Sep 15, 2005

Is there a way to update a field in a sorted query with its recordnumber value, as if to assign a rank to it?

Example Table contains Name, Grade, Rank sorted by Grade ASC

Record 1: John, A, *Update here*
Record 2: Tony, C, *Update here*
Record 3: Eric, D, *Update here*

The *Update here* is the bit that I need to update with each records recordnumber, so that the end result would be as follows:

Record 1: John, A, 1
Record 2: Tony, C, 2
Record 3: Eric, D, 3

Can anyone please help?

Thank,

Steve Johnson

View 3 Replies View Related

Question On Ranking And Autonumbers (I Think)

Jan 12, 2007

Hello All

Firstly I have been reading this forum for a while picking up hints and tips and am very grateful so big grats and thanks to all you guys for the help

The problem I am stuck on at the moment will hopfully be quite simple, but I should point out I have absolutely no skill or knowlede in Modules and VB language I most of my "stuff" via statements within the Design view.

Ok so here we go. I have a query which brings back the top 50 performing sales items, which is done by sorting them in descending order and using that filter thing at the top of the design view, which is set to 50. What I would like to do is insert a field in the query which gives the rank, so the best seller is given number 1, the second best 2 and so on and so forth.

Sorry if there is a topic already, I have tried to search, but couldnt find what I was looking for.

Ta in advance for any help

Mav

View 4 Replies View Related

Ranking / Top 10 Under Multiple Categories

Sep 18, 2007

In a table I have:

- Category (A, B, C)
- Family (A1, A2, A3, B1, B2, etc)
- Item
- Sales

I'm looking for a way to get the top 10 items in each category + family based on sales (ie: top 10 in A-A1, then A-A2, then A-A3, etc). When I try using the top function, it doesn't treat each category + family combination as unique. Any ideas?

View 5 Replies View Related

Priority Ranking Adjustments

Feb 28, 2008

I found this from different fourm by searching google. This is exactly what I need to do and the answer was given; however, I can't figure it out. Can someone please post a step by step instruction on how to complete the following.

Thank you!


Question

I have a datbase full of projects that must be ranked in priority order. This was simple enough but when I add a project I will likely need to change the priority rank of all the "lesser" projects. With over 500 projects adding a new project with a rank of 50 means changing all the ranks 50 and above. I want to be able to add a project and when I determine its rank I insert the rank number and every rank equal to the project just added is the indexed by 1 digit. No two project can be ranks the same, one is always more important than another.


Answer

Create an update query that sets the priority field to:

[Priority] + 1

then set the criteria to

>= new number

Where new number is the rank of the new number.

This will increment by one each priority.

Hope this helps,
Scott<>

View 5 Replies View Related

Subjective Ranking Within Array

Aug 12, 2015

I reviewed a few ways to rank based on calculations and use them, however what I'm looking to accomplish is subjective ranking whilst limited the ranks to the total number of records in the recordset.

e.g. you have 50 records. Manually moving Record # 30 up to #3 would bump the existing records #3 to #29 down one (to #'s 4 through 30) and records >30 would stay the same.

Would deploy this to the end users within a continuous form, each row having Up/Down arrows to move each record up/down 1 rank as well as provide a combo box so they may choose any value within the array they want to set it to...

View 14 Replies View Related

Ranking According To Student Average

Apr 13, 2013

I want to prepare a query which gives me the ranking of the classroom according to their average. (also there are classrooms in the table, if possible, query should consider the grades) you can see the attached sample document..

View 1 Replies View Related

Record Automatic Ranking On Form

Feb 11, 2012

I have a number of records in my database and I use a form to input information, update fields etc. One of these fields is a ratio. I would like to create another field, in the record, input from the form, that automatically generate the ranking of the record in the database using the ratio, in descending (or ascending) order. So if I have 1000 records, the field would return, right after I update the ratio, which ranking within these 1000 it now holds.

Is this possible? In the event two (or more) records share the same ratio, what happens?

View 9 Replies View Related

Ranking Of Student Based On Their Marks And Average

Aug 26, 2013

How to query the example in the image below. My problem is:

1. If the student get a mark 49 below in any of their subject they will not be included in the ranking.

2. If all of the student mark is above 49, I need to rank the student base on their average.

View 1 Replies View Related

Query In Forms Question (printing A Single Selected Query Record From A Form?)

Oct 19, 2004

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View 1 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Opening Design View Of Append Query Without Timing Out Source Query

Mar 4, 2015

In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.

All is well and fine until source data changes and the pass-through query runs too long and times out.

If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table

Is there a way to open an Append or Make-table query in Design view without invoking the source query?

View 1 Replies View Related

UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.

Oct 28, 2005

Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved