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 Replies
ADVERTISEMENT
Jun 19, 2005
Hi All,
I have been searching through the forum to see how I can do a query that does subtotals, but to much to my avail there has been nothing that specifically answers my question. So here it is. If I have two tables say one called A and the other B. Now in A there are two fields that I want to use and these are item and quantity (The quantity is the number of that item). In the other table I want to use the field rate. Both tables are related by the item. How do I do a subtotal query that gives me the subtotal for an item i.e. rate * quantity, and shows the information in the specific format:
item : quantity : rate : subtotal
Thanks
Greg
View 1 Replies
View Related
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 4 Replies
View Related
Nov 16, 2013
I have set up a basic database that has a customer table , orders table , join table and products table. I have created an invoice form that has customer details and then in a subform has the order ID and product ID , name , price and quantity.I have a text box on the form to which I am trying to output the sub total to when the form is launched(quantity X price for the current order).I was trying to create a query that will return the subtotal for an order.I had the query calculating the subtotal but I was then trying to get t the cost based on the order ID. I was then attempting to get the Order ID for the query from the open form by adding this to the criteria in the query:
[Forms]![Invoice]![invoicesubform].[Form]![Order ID]
I was then trying to use Dlookup to put the result of the query into a subtotal text box by using:
=DLookup("[cost colum from query entered here]",[the query name was entered here]")
View 1 Replies
View Related
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
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
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
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
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
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
Jun 28, 2006
I have a form with a control "TypeCode," it's set to:
=IIf(Year(Date())<=Year([date]),[qryVacation.TypeCode],"Other")
So that if the date on the form is less than the current year, the control will return "Other" in the box.
At the bottom of the form, I have a subtotal:
=Sum(IIf([Typecode]="Sick",1,IIf([Typecode]="Half Sick Day",0.5,0)))*8
This helps tally Sick day and Half Sick Days for me, but if they are a year old, then they should not be counted. Once I got the field to say "Other" I thought I was home free, but it is still counting the sick days.
I know it's probably still getting the value from the qryVacation, but how do I have it so it just subtotals off the form iteself?
View 1 Replies
View Related
Aug 15, 2006
I have a form were I got the corrent fields:
date, account, ammount; the account field is a numeric field. What I am trying to do is to have a extra field on the same form wich will give me the total ammount for the relative account as soon as I have inserted the 3 data. What I don't knowis if I need a query to extract all the ammount for the relative account shown on the field "Account" or it can be done in a easyer way!!!
View 4 Replies
View Related
Dec 2, 2004
Hello,
I have a code in my 97 Access db that exports a query in Access to Excel. The code also includes all the formatting needed for the Excel sheet including subtotal rows. What I would like it to do is automatically format the subtotal rows to be a different color so they stand out. This is part of my code:
xls.Cells.Select
xls.Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(13, 17), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Since the data changes continually I have no idea which rows will have the subtotals.
Is there some code I can insert in my export code to automatically color or highlight the subtotal rows?
Thanks in advance for any help.
Elizabeth
View 2 Replies
View Related
Nov 12, 2007
Hi
I haven't been on for a while. so hello everybody.
I am trying to keep a subtotal in a table. there is an index (actually its formatted text, but its a number and i can converted i.e. cdbl()). I was thinking that i should create one query with dmax in one column and and then use another one to show the value plus the previous value.
can anybody help me out please?
thanks,
Sam
View 6 Replies
View Related
Aug 2, 2005
Hi all,
I have a subform within my form for quotations (see attached).
A user will manually type in "Repair Cost" and "Accessory Cost". The subtotal field is calculated using this expression =Nz([Repair Cost])+Nz([Acc Cost]) in the Control Source box
This is all Ok.
I want to subtotal these on the main form then work out the VAT and final total.
My problem is, No matter what expression I seem to use on the main forms subtotal, it will not work.
Here is an example of what I have tried:
=Sum([SubTotal])
=Sum[Forms]![TblQuotation subform].[SubTotal]
=Sum[TblQuotation subform].[Form]![SubTotal]
Nothing seems to work. :confused: Any pointers would be greatly received. Many thanks.
View 7 Replies
View Related
Mar 7, 2008
Hi,
I am trying to work out away of subtotalling a field in access, similar to that of the subtotal function in excel,
Basically my table is structured so i have a store number column and a transaction number column so as an example my data looks like this:
Store noTransaction Num
1234522664499
1234522664499
1234533775599
1234533775599
1234544886600
12345611227755
12345622449955
12345677556699
The transaction number is made up of several other columns and basically represents an item sold by an assistant at a certain time - if the asst sold 2 items i will get the same transaction number appear twice, if they only sold 1 item it will only appear once.
I want to be able to count the transactions by store so i end up with a result like this (based on the above data)
Store no Count of Trans No
12345 3
123456 3
Can anyone help?
View 2 Replies
View Related
Oct 24, 2007
i was wondering if there is any easy to follow information anywhere or examples with how to do..Specifically on creating an invoice??
The form would need customer details then a subform with the qty, price per unit, extended price then a section on the form which works out the subtotal adds it to freight charges
then gst and finally the total.These all automatically then being placed in there appropriate tables..i Managed to figure this out once before but i cant for the life of me figure it out again..I have spent weeks trying any clues???
View 1 Replies
View Related
Oct 4, 2013
I have created some subtotals in some columns of data which for example are the dates, names, and amounts of invoices.
The code I used is:
Range(Range("C1"), Range("C1").End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3)
This is working of sorts but it is giving me subtotals based on the day in the date column. How can I change this to group by the month?
View 10 Replies
View Related
Apr 3, 2014
I have a set of records that contain the following attributes;R_Name, Cost, StartDate, EndDateI need to subtotal the cost for each R_Name by year. The start and end date attributes can span several years and I need the correct cost for partial years.
View 5 Replies
View Related
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
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
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
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
Jul 17, 2013
find the attached MS access DB for INVOICE printing . I am facing two issues there .
1. {form INVOICE } sub-total must be Calculated from line total on {INVOICE DATA Subform} , I know this can be accomplished by adding a new field at sub-form and calling that value to main form's sub-total field .
2. And the real issue is i want to save this value from subtotal on form INVOICE to table field name subtotal .
3.I wish to make a reporting format for each INVOICE NUMBER , whenever report is being generated , the report shows more than 1 invoice details .
View 2 Replies
View Related
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
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
