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:
I am working on a database to keep track of meter readings from several meters. Am at the table design stage and have come up with two seperate table designs but am wondering what would be best.
Each individual 'Meter' tracks more than one 'Fuel'. All of the regular meter readings should occur on the last day of the the month. The difference between the current month's reading and the reading from the previous month is the usage for the current month.
Though rare, there are occasions when there could be adjustments to the meter. A meter may be reset to 0 or to any other number due to a number of issues (replacement, maintenance).
The usage for a Meter-Fuel combination calculated by the current meter reading & previuos meter reading is assigned to current readings' date, As readings should onlt be taken at the end of the month, all other readings should be adjustments ONLY!
I think that the second version is the way to go, since I would still have to create a query to join data from both the reading and adjustment tables in the first design that would look like the data above.
Here what I consider to be the drawbacks to either table structure
Version 1 The first version requires more complicated queries to calculate the difference between meter readings.
Version 2 The second version will require some coding/querying to make sure that there are always two adjustment records for one date and determining which is the pre-adjustment and post-adjustment meter reading since it may not be true that the meter is reset to zero; it may even be set at number higher than the pre-adjectment reading.
Any thoughts on which design is better? Or any problems I could run into later with designing forms/queries/reports that perhaps I have not thought through?
I know how to setup a Replica's Priority, but where do I go if I want to change it afterwards? I have searched everywhere, or am I just supposed to create another replica and set it then?
I have been asked if I can add a priority list to my database and I am trying to figure out how to do it.
The database already exists and this would be added code/forms. Basically this database has a number of users, all identified by a login name. Each of these users have several projects assigned to them. The projects are identified by a unique project number.
What I need to do is to be able to list all the projects that a user has, which is easy to do by simply creating a query, but then the user needs to be able to type in a priority number (1 to infinity) for each of these projects with no number repetitions. If a project priority is updated, say from 6 to 2, then the projects that had priorities 2 to 5, get a +1 added to their priority number so they go 3 to 6. Likewise if a project gets "demoted" (priority is lowered) then the ones below go up one.
I have a very basic application to manage the tasks. In the main form "Tasks" one of the field is the priority (Order by) and I have this example:
Task Priority
B 1 A 2 C 3 E 4 D 5
If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.
I have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.
Tables tblItem tblFormula tblFormula_Item_JNT
There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".
So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once.
I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.
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?
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.
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??
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.
- 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 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!!
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...
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..
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.
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?
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).
