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.
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 RelatedI am working a database that manages student's Records including marks for various subjects, but i would like to do the following on each of the students' report cards:
a) Display the marks for all the 12 subjects .This one i have done.
b) Display a total for all marks of each student.Done as well.
c) The problem is, i don't know how to display the position of each student on the individual report cards.I want to base this grading on the total marks obtained by each student in the same class.
I am trying to get the Total average speed answered (ASA) based on two columns sums. (i.e. sum of calls handled and sum total speed answer). I need to get the total avg speed ans (formula is sum of total speed answer (tsa) divided by sum calls handled). I did an average query for asa but it gives me the avg for the rows(records) of the asa and not of the two columns. I need it for the two columns above. I was trying to change the formula in report design view, but I am not coming out with the correct answer. Are there any suggestions?
Please help!
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
I have a report that has multiple fields - 3 of which matter for this discussion -
1.) employee
2.) employer
3.) rateofpay
The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123
I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.
I am calculating this in a section footer.
Hi guys!!!!
I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(
I wan't to export Avg Of the fields like in the panel below:
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
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
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.
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
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.
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.
I try to make my second database in Access. I need to summary 5 numbers, and then minus the lowest and the highest of them. For example (1+2+3+4+5)-1-5=9
In excel it looks like this: =SUM(C2:G2)-MAX(C2:G2)-MIN(C2:G2)
In access (Expression builder) i made this code: [J1]+[J2]+[J3]+[J4]+[J5] - it's the summary of five marks, but how can i erase the highest and the lowest one?
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
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
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
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?
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<>
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
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...
Hi,
This is probably real easy to do, but ........ I have a field in Access that looks like this:
"12:12:01 PM 12/5/2005, 5:00:01 AM 12/6/2005, 7:00:25 AM 12/6/2005"
From this string, all I want is the last Date entry (mm/dd/yyyy). Thus in this result, it would be 12/6/2005. I know how to use the Right(), Left() and Mid() functions, but I do not want quotation marks included in my result.
Any help would be great ......
I have a statement which is driving me nuts...i'm a beginner with using SQL:
Private Sub Command45_Click()
Me.Graph0.RowSource = "SELECT (Format([Date],""" """mmm""" ''yy')) AS Expr1 FROM Chart_Unsafe_Daily GROUP BY (Format([Date],'mmm'' '''yy")), (Year([Date])*12+Month([Date])-1);
End Sub
The statement is RED for an error, i'm sure it is because the quotation marks within the statement are incorrect.
Can anybody help me out?
Hello everyone. I am a new member and need help with a database that I am working on. Let me first say that my knowledge of Access, SQL, and databases in general is very limited. I work in the Academic Success Center at my college and they use an access database for sign in/out. My job is to redesign this database so that there are two forms instead of one. The first form will just be the first and last name of the student along with the date and time that are generated automatically. Currently the student must type in there info and when they are done working in center they must sift back through the records on the form to find their sign in info and type in a sign out time. My task is to make two forms one that has just the students first and last name along with the current date and time. This will be the student sign in. Once the student is done they should be able to find there name in a list of currently signed in students, select themselves, and be taken to their sign in record. The student will then be required to click a button to be taken to the sign out form which will automatically fill in there name, date, and current time not sign in time. The will then be required to select the resources that they used and what courses they worked on and any comments if they wish. Once they have filled out everything they will need to click a button that will close the sign out form and update the student table with their info. The actual design of the forms is not the problem, the problem is getting all of this to function the way it is supposed to. When I fist agreed to this it really didnt seen like it would be that difficult but apperantly it is, I am becoming very frustrated and desperate. If anyone can give any help at all or point in a direction that will help I would greatly appreciate it. Thanks in advance, and sorry to be so long-winded.
View 5 Replies View RelatedI 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.
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