Queries :: Add Percentage Column To Crosstab Query
Apr 12, 2013
I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:
TRANSFORM Count(CASEDATA.CASESTATUS) AS CountOfCASESTATUS
SELECT IUIDCODES.CODEDESCRIPTION, Count(CASEDATA.CASETYPECODE) AS CountOfCASETYPECODE
FROM (CASEDATA INNER JOIN CASESTATUSCODES ON CASEDATA.CASESTATUS = CASESTATUSCODES.CODEID) INNER JOIN IUIDCODES ON CASEDATA.IUID = IUIDCODES.CODEID
WHERE (((CASEDATA.CASETYPECODE)=63) AND ((CASEDATA.FYSTATUS)=6 Or (CASEDATA.FYSTATUS)=7 Or (CASEDATA.FYSTATUS)=8))
GROUP BY IUIDCODES.CODEDESCRIPTION
PIVOT CASESTATUSCODES.CODEDESCRIPTION;
View Replies
ADVERTISEMENT
Apr 9, 2014
I obtained data for earnings by industry for men and women over time (5 years). I developed crosstab query that showed the average earnings for men and women for the 5 years.
Now I want within this query to calculate the differential between men and womens average earnings and calculate a percentage within this crosstab query. I tried to use Expressionbuilder with little luck.
View 6 Replies
View Related
Feb 18, 2014
I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:
PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");
This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.
View 3 Replies
View Related
Mar 26, 2013
I have a simple crosstab query:
FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).
These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).
This is the report the crosstab query generates:
What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.
Can I do this?
View 3 Replies
View Related
Jun 6, 2013
I have about 20 columns in a query and each row is a customer.
The columns are either pass/fail but are text fields and not a yes/no set up. I want to get the percentage of a pass in the column.
View 9 Replies
View Related
Nov 12, 2004
Hey..
If there was a way I could get the crosstab query in Access to allow more than one column I would solve a major problem. Is that possible?...help anyone?
View 1 Replies
View Related
Aug 17, 2015
I have a Graph that uses a crosstab query to generate the data. Its only a simple query. One of the fields is project type, but in the query this changes to a number instead of text? I would like to have this as text but don't know how to go about it. I have searched forums and tried Allen Browne's suggestion on specifying column headings but no luck.
I have attached the initial query and the crosstab query in the screen shot as well as the graph. My aim is to get the project names in the legend, instead of 1, 2 as shown.
View 6 Replies
View Related
Aug 22, 2006
Not sure if there is a quick answer but I am trying to complete a crosstab query that references a lookup table. I cannot remove the lookup tables because the database was designed by a consultant. The lookup table is referenced as the column heading. The query works fine until I change the column headings in the properties box - it returns the column headings but there are no values. Am I doing something simple wrong or is it having troubles because it is a lookup table for column headings.
View 3 Replies
View Related
Nov 29, 2007
I have constructed a crosstab query in order to populate a stacked column chart.
Everything is fine except the crosstab query seems to contain an extra blank column with the header <>. This is therefore appearing in the chart when I don't want it to.
As the crosstab query is itself based on a select query I think the <> column is a reference to a blank row in the select query.
The blank row in the select query is itself the "new record" row from the underlying table.
Whether significant or not one of the data types is an autonumber.
To solve my problem I think I need to find a criteria to use in the select query that would exclude the new record row. I've tried to use the autonumber field but various "is null" "is not null" <>"" statements don't seem to work.
I basically want to include all populated rows but exclude the blank "new record" row.
View 1 Replies
View Related
Jan 18, 2006
Hi,
Is it possible to choose a runnig number as a column header for a crosstab query header as at the moment I am using the date as below. I would like the column headers to be for example col1 ,col2 ,col3 ,col4 etc is it possible to give access an array or varible to use?
Example output
ProjIDT-Hours13/06/200514/06/200515/06/2005
22130 8 5
243257810
Code
TRANSFORM Sum(LaborDB.Hours) AS SumOfHours
SELECT LaborDB.ProjID, Sum(LaborDB.Hours) AS T-Hours
FROM LaborDB
WHERE (((LaborDB.LogID)=166) AND ((LaborDB.Date) Between #13/06/2005# And #15/06/2005#))
GROUP BY LaborDB.ProjID
PIVOT Format([Date],"Short Date");
Thanks for your help
Joolz
View 1 Replies
View Related
Jan 21, 2008
Hello to everyone!!!i need to ask somethingI CREATE I DYNAMIC QUERY .....HOW CAN I CHANGE THE NAME OF THE COLUMN HEADINGS?THANX IN ADVANCE
View 1 Replies
View Related
Jun 18, 2006
Hi :
I have a question,
Table1:
Company, Start Month, End Month, Fees, Calc
ABC_________Mar-05___Mar-05____7 ____1
ABC_________Mar-05___Apr-05____7_____2
ABC_________Apr-05___Apr-05____5_____4
I used crosstab to make this format
Company, Start Month, Fees, Mar-05 , Apr-05
ABC_______Mar-05 _____7_____1_______2
ABC_______Apr-05______5 ____________4
I want to change column position as following order:
Company, Start Month, Mar-05 , Apr-05, Fees
ABC_________Mar-05____1_______2_____7
ABC_________Apr-05____________4 _____5
I tried to use this code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("report_crosstab")
qd.fields("Fees").OrdinalPosition = 4
It doesn't work for crosstab query.
Can anybody tell me how to change the column position of the query?
Please let me know, thanks.
View 2 Replies
View Related
Apr 16, 2013
How to calculate the percentage of kilometers per order compared to the total number of kilometers per carrier.
In the attached screenshot send queries where I need formula.
Mileage on the orders of the field called "Korkma" . The formula should look like: # [Korkm] / [total kilometers per carrier] #.
I do not know how to get the total mileage driven by the carrier (the carrier is in the query in the Field Name Plate) for the month.
For example:
date 1.1.2013.,Plate:Pribus Korkm= 37
date 2.1.2013.,Plate: Pribus,Korkm=56
date 10.1.2013.,Plate: Pribus, Korkm= 70
Formula: [37]/[163] = 0,23
[56]/[163]= 0,34
[70]/[163]= 0,43
View 1 Replies
View Related
Aug 5, 2014
I am trying to set up a calculation between two values to show the percentage difference. In Excel, for example, I would have two values, £905,175 and £891,563, and I would enter =A1-G2)/ABS(A1), which would then return a plus or minus percentage value. how to do this in a query using Access 2010?
View 3 Replies
View Related
Oct 26, 2006
Does anyone know how to add a column to an existing table that would calculate percentage?
Thank you very much in advance!!!
View 3 Replies
View Related
Apr 25, 2014
I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.
So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.
My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:
The amount sent on time / the total amount * 100
But is there anyway to add this into a 4th column displayed next to these figures?
Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?
View 1 Replies
View Related
Mar 28, 2015
I need to do a update query with prompt to input a percentage.
View 11 Replies
View Related
Apr 19, 2013
To calculate the percentage of the number of passengers in the total number of passengers per single order using query:
Code:
SELECT
Code:
A.Date, A.Plate, A.Pax, A.Agency, A.Code, IIf ([pax]> 0, Round ([Pax] / (SELECT Sum (Pax) FROM Sheet2 as B WHERE B.Date = A.Date and B.code = A.code), 2), 1) AS KOR
FROM Sheet2 AS A
ORDER BY A.Date;
Query works fine in all cases except one, and that is when the encounter a same order in one day
01/04/2013. D_1 0pax
01/04/2013. D_1 5pax
in this case the formula gives a coefficient of 1 to 0 passengers and coefficient of 1 to 5 passengers, should be given a score of 0 0 of passengers and 1 to 5 passengers
View 1 Replies
View Related
Nov 6, 2005
Here's a sample of the query code for survey I made, for example
Question 1 will be
1) Male or Female?
(radio button answer_id 111) Male
(radio button answer_id 112) Female
etc etc...
My question is how do I set my query to show a graph / percentage value of total unique answer_id's in relation to the question_id.
e.g. Question 1: 66.67% chose 111, 33.3% chose 112
question_idanswer_id
1111
1111
1112
2116
2117
3119
3121
4126
4126
4126
I hope I was clear enough am quite new to Access and I really thought I could do this with pressing a few filter/sort buttons but alas I haven't found a way yet.
View 1 Replies
View Related
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
Sep 24, 2013
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies
View Related
Mar 24, 2014
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
View 1 Replies
View Related
Nov 23, 2013
I am stuck at setting a validation rule for my percentage column. The entries in such column must be less than 20 and greater than 10.
View 9 Replies
View Related
May 21, 2015
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
View 3 Replies
View Related
Jun 19, 2014
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable
SELECT QryTable01.productName
FROM QryTable01
GROUP BY QryTable01.productName
PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable]
t
Then the report row with the values:
Viable Not viable
14 12
displays as 1412
So how do I add the values in the rows???
View 1 Replies
View Related
Sep 17, 2014
Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?
Code:
PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime;
TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM
SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM]
FROM Query1
WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null))
GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp]
PIVOT Format([Date Stamp],"Short Date");
View 4 Replies
View Related