Queries :: How To Get Total Numbers Of Groups And Express Them As A Percentage
Aug 12, 2013
I have built a query that lists all the members in a group aged 15 to 18. Now I need to build another query that counts a subsection of that group (the number who gained employment in the quarter) and express this as a percentage of the whole of the 15 - 18 group.
I presume I can use the original query to get the number of 15 - 18 year olds. I can write the query to calculate the subset of 15 - 18 year olds who gained employment, but how do I get the total numbers of the groups and express them as a percentage?
View Replies
ADVERTISEMENT
Apr 14, 2015
I've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
View 4 Replies
View Related
Jul 9, 2013
I am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
View 14 Replies
View Related
Jan 2, 2008
I have a database with around 5083 records and I am trying to find the three most common numbers called in any one record such as 5 22 and 39 has been called together 50 times
Here is the structure # 1 through 5 is a number between 1 and 39 but never duplicated in same record
Record 1 |draw#|Date|#1|#2|#3|#4|#5|
Record 2 |draw#|Date|#1|#2|#3|#4|#5|
Record 3 |draw#|Date|#1|#2|#3|#4|#5|
Record 4 |draw#|Date|#1|#2|#3|#4|#5|
Record 5 |draw#|Date|#1|#2|#3|#4|#5|
Record 6 |draw#|Date|#1|#2|#3|#4|#5|
Record 7 |draw#|Date|#1|#2|#3|#4|#5|
and so on for 5083 records
Thanks
Randy
View 12 Replies
View Related
Feb 26, 2007
Hi guys
I am making a query that calculates how much costs i have per job. I'd like to create a function in the query that can calculate how big a percentage each job is. (need total for every job/ total for all jobs) but so far i haven't been succesful in this.
Anyone who could help me?
Cheers,
Takstein
View 3 Replies
View Related
Oct 11, 2005
Hi, All:
I have been struggling for this question for a long time.
I have a total query coming from two tables. This query has following field:
productline
device
component
The table has more field. One of them is status for component field.
My application is that there are many productline. Under each productline, there are many devices. Under each device, there are many components. Each component has one of 4 statuses. The status is text value, 'Yes', 'No', 'UR', and blank.
In the form, I need to use continuous form to display each device with totalcomponent (I use Count of component), percentage of status1 based on totalcomponent, percentage of status2, etc.
My question is:
I tried to use Count(IIF([status] = 'Yes', 1, 0)) to get percentage. But [status] = 'Yes' seems not right because I got a count of all statuses, the same result of CountOfComponent
Really need help
Thank you much
awu10
View 1 Replies
View Related
Dec 18, 2014
I have two numbers, i need to calculate how much percentage of one is the other. E.g
num_1 = 100
num_2 = 10
percent = 10%
Users enter a dollar amount (retainer) and my code should calculate the percent of the total proposal amount.
Actual Code
Private Sub cmdSubmit_Click()
Dim intProposalTotal As Double 'if i use interger i get "overflow" error
intProposalTotal = Nz(DLookup("ProposalTotal", "qryPropsalTotalForRetainer", "proposal_id=" & Me.proposal_id), 0)
[Code] .....
billing_retainer_percent is formatted to Percent with 0 decimals. In the actual table Type - Number, Format - Percent, Field Size - Single. What i get is 100% instead of 10%.
View 4 Replies
View Related
Apr 19, 2013
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
=IIf([FTE_Percentage]="76-100%",1,IIf([FTE_Percentage]="51-75%",0.75,IIf([FTE_Percentage]
="26-50%",0.5,IIf([FTE_Percentage]="11-25%",0.25,IIf([FTE_Percentage]="1-10%",0.1,0))))).
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
View 1 Replies
View Related
Mar 6, 2006
I have Datein and Dateout fields with many rows of dates beneath each. I used DatePart in a query to convert each date to a week number; but now need to count, and total, how many times each week number occurs under each field.
I'm sure this can be done easily, but I'm new to expressions, functions, etc, and would greatly appreciate some help!
View 6 Replies
View Related
Apr 7, 2014
I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
View 5 Replies
View Related
Nov 27, 2013
This code shows current record number:
Code:
=[CurrentRecord]
This code shows total record number:
Code:
=DCount("[Category]","tblCategory")
How do I combine them in a textbox to say something like "8 of 10"?
View 1 Replies
View Related
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
Dec 7, 2006
Hello, I need some advice.
I am currently trying to rework and enhance a database where I work.
It was set up for printing work orders only.
I have enhanced it to keep a database of Customers and will soon get a tracking form up and working.
My question is; should I put all this effort into access or should I just go ahead
and do it in VB EXpress?
Thanks for the insight.
View 2 Replies
View Related
Mar 8, 2008
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
View 2 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
May 17, 2007
In the outbox, there is an email i tried to send many months ago, and now everytime i try to go back into the outbox where this message is, it freezes the computer. My inbox is still functioning fine, but the outbox problem means i have to crtl/alt/delete to exit from my outbox everytime.
hope you can help, would be most grateful,
Colin
View 1 Replies
View Related
Oct 14, 2013
I have a query that will sort groups of zip codes using the following expression: Between [T_ZipRange]![StartZip] And [T_ZipRange]![EndZip]. The first and last zip codes are entered and the expression returns the zip codes between the "start" and "end" range. The problem is that if a zip code is only 5 digits: xxxxx- instead of xxxxx-xxxxx, the zip code will show up in other sorts. The zip code field is a text field. How can I limit the sort, based on the number of digits in the zip code?
View 2 Replies
View Related
Mar 16, 2014
creating a query with a running sum (cumulative total) across two categories. I need to accumulate Wages by employee, by calendar year for every day/every job worked. I have a table containing over 33,000 records, the years span from 2009 to 2014 with multiple employees.
The Dsum option I have found on the internet is too time-consuming and locks up my query for the over 33,000 records I need to generate results for. It needs to be a SQL statement. Although all the SQL statement I have tried simply total all Wages for every entry ever made in the column, instead of per calendar year, per employee.
Here are my fields:
Calyear = Ascending
Employee Name = Ascending
WorkDate = Ascending
ID
[code]...
There may be more than one entry per day per employee in a given year. This is so I can calculate certain payroll taxes which are based on cumulative wages amounts.
View 4 Replies
View Related
May 18, 2013
I've a club database the records score for juniors on age groups that are set as of the 1st January each year. I would like to set this automatically from the date of birth. I have found some code that is very close to what I need apart from the fact it groups on the exact DOB resulting in changes to age group mid year.
'----------------------------Code Start--------------------------------
Public Function AgeGroup(dtmBirthDate As Date) As String
Dim intAge As Integer
'Age Calculation
intAge = DateDiff("yyyy", [dtmBirthDate], Now()) + _
Int(Format(Now(), "yyyy") < Format([dtmBirthDate], "yyyy"))
Select Case intAge
[code].....
View 4 Replies
View Related
Dec 29, 2014
I have a number of groups in a particular database. These are based on the month that they relate to, but they are also split into four separate groups for each month. So for example, I will have four groups for November 2014:
November 2014 Raw Data (which contains linked tables in another database)
November 2014 (which contains the bulk of the queries that are used for processing)
November 2014 Reporting (which are the queries that are used for month end reporting - obviously)
November 2014 Misc (anything that doesn't fit into the above, although there are always queries in these groups)
Now, I have been using this database since 2012, so as you can imagine there are quite a significant number of groups. Adding queries to new groups for each new month is therefore starting to take a while by scrolling down to the bottom... Is there a way to do this by using VBA? It would save a bit of time each month.
View 6 Replies
View Related
May 8, 2014
I have a query that lists the total number of parts received by month. I would like to be able to compare those monthly totals with a rolling 12 month total. I've been able to get the rolling 12 month total, but I can only get it to work for one 12 month period.
Is there a way to get a 12 month total for each month? In other words for April, I would like a 12 month total of May 2013 through April 2014. For May, I would like a 12 month total of June 2013 through May 2014. Is that possible? If so, how would you go about doing it?
I have a field in my table titled REL_MONTH that shows a negative number for how many months ago something is (i.e. 00 = May, -1 = April, -2 = March).
View 2 Replies
View Related
Aug 15, 2007
I have about 40 queries based on employed trainees from different groups(MMF, MKO, CNP,...). Now, what I'm trying to do is find the percentage of trainees employed from the total trainees, % employed in community from total trainees, % employed at wuskwatim from total trainees,... and so on.
If you take a look at my DB, you will find I have have MANY queries made. Is there an easier way to find this kind of information?
Any suggestions would be appreciated.
18448
View 3 Replies
View Related
Feb 17, 2015
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code:
For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
"KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
[Code] ....
View 3 Replies
View Related
Jun 20, 2005
I just recently figured out how to count how many jobs a CSR has submitted and how many errors a CSR has made. What I need to do now is combine these two queries and figure the percentage of jobs correct that a CSR has submitted. I tried to combine these to queries in a report and figure it out that way, but that didn't work. When I combined the two queries I lost the individual information in the queries. I tried to combine the two queries but it wouldn't let me. It said that I was trying to combine the fields of a table and a query and that I should use one or the other.
Can anyone help me?
learnasugo
The excel file is how they would like the report to look
View 9 Replies
View Related
Jan 3, 2014
I have a query with about 7500 records that are grouped as follows and sorted by Mtr_Reading
ID Name Event_Start_Time _Hour, Mtr_Reading
2210 XYZ 7/15/2013 13:00 17 150
2210 XYZ 7/15/2013 13:00 14 143
2210 XYZ 7/15/2013 13:00 16 115
2210 XYZ 7/15/2013 13:00 15 110
2210 XYZ 7/15/2013 13:00 13 100
2210 XYZ 7/16/2013 12:00 12 100
2210 XYZ 7/16/2013 12:00 17 150
2210 XYZ 7/16/2013 12:00 14 147
2210 XYZ 7/16/2013 12:00 13 113
2210 XYZ 7/16/2013 12:00 18 110
2210 XYZ 7/16/2013 12:00 15 100
There are about 75 distinct sets of ID's and Names not shown here
The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.
I did try nested select statements and kept getting "invalid argument to function" exceptions.
View 14 Replies
View Related
Nov 7, 2013
Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View 3 Replies
View Related
