Group By In A Median Query
Apr 24, 2007
I've gotten the medianf fuction that is pasted on these boards working in the attached db example. The problem I am running into now is the medianf function (correctly) takes the median for an entire table at once. My dataset example looks like:
typenum
t11
t12
t13
t16
t16
t1100
t21
t22
t2200
I'm trying to end up with median t1 = 4.5, t2 = 2. With the way that function works, it sets them both to 3 which is the median of the entire set. I have the query saved in here already setup so far. Any ideas on getting the grouping to work properly?
View Replies
ADVERTISEMENT
Oct 15, 2013
I am using the code below to evaluate values for each transaction to determine the median:
Function MedianF(pTable As String, pfield As String, pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Inputs: ? medianF("Values", "Industry") <enter>
'Output: results are currently incorrect
'*******************************************
Dim rs As Recordset
Dim strSQL As String
[code]....
The code generates median results based on the value but not the correct ones, as I check results in Excel using the Median function. I need to see the median for the transactions within the same industry and I do not know how to do that . Below are some sample transactions including the Industry, Value, mymedian (which the current results based on my code) and the expected median result (which I calculated in Excel based solely on the transactions listed here). Note that mymedian results listed in the 3rd column are determined using the complete data set (over 2000 transactions). I only included a few sample transactions below to show that I am currently getting 2 different median results: 44.62 and 50.82. Also be aware that I do not want to evaluate any zero value transactions, which I believe I am handling in the strSQL line above where I indicate <>0 . I need to see a Median result for each industry and the result should be the same for all transactions within the same industry.
Industry Value mymedian Expected Median
Aerospace 18.01 50.82 40.45
Aerospace 53.02 50.82 40.45
Aerospace 109.18 50.82 40.45
Aerospace 61.54 44.62 40.45
[code]....
This is how I am passing the values from the query to the module, passing the value of Period2 if Period 1 = 0, else passing Period1:
mymedian: IIf([Period1]=0, medianF("qry_My_Query","Period2","Classification_I ndustry"), medianF("qry_My_Query","Period1","Classification_I ndustry"))
View 14 Replies
View Related
Jan 3, 2007
Hi,
I am trying to run queries to determine the median values for different age groups of peoples each with different illnesses. At the moment the database asigns an age group for each person whos age falls into the module determined age brackets, under 16, 16 - 40, 41-50 etc.
For each age group and diagnosis (bronchiectasis, sarcoidosis or pulmonary fibrosis), I need an age distribution chart including the min, max, mean and median.. min max mean are all simple enough and using MS VB median module I can individually calculate medians for each of these. The problem comes when trying to combine these all in one data set.
One solution I found was to combine two reports so I have queries with min, max, mean and then the medians set up beside them. The problem tho with this is that when a recordset returns 0 results (i.e there are no people under 16 with sarcoidosis), the query linked to the report does not return the value of 0 but as they dont exist it does not include it at all. The median fields however return blanks and cos of this the line spacing becomes messed up and the report becomes incoherrant and incorrect.
I think the solution will have something to do with querying, though so far I have not been able to work it out :confused:
Please help!
View 4 Replies
View Related
May 3, 2006
Hello:
I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.
Example (fields in the query)
Expr1
Expr2:Median(Expr1)
Thanks in advance.
View 14 Replies
View Related
Jun 22, 2006
Can I do an Median Function in Access? I do not see in the the built in functions?
Thanks
View 3 Replies
View Related
Dec 21, 2005
Newto Access, new to the board. Have searched for median queries and have not found what I am looking for.
I want to incorporate a MEDIAN calculation into a querey. I have 6400+ records, which I need to be grouped by 61 industries. I have suceessuflly grouped and got the MAX, MIN, AVG, etc... and I would now like to add into this grouped query the MEDIAN calculation. Can someone here help a brother out?
View 6 Replies
View Related
Jan 4, 2006
OK try this:
http://www.playfm.orcon.net.nz/Chart3.GIF
I want to get the Median number from the "This Week" column for Cher. The output should hopefully tell me that Cher has a score of 7.
In addition to this.. I want to be able to create a report which tells me the medians for all the artists in the sheet.
Any ideas??
Cheers, Alex.
View 14 Replies
View Related
Mar 16, 2007
What is the easiest way to calcuate a median value for a group of values?
View 4 Replies
View Related
Apr 19, 2007
I am attempting to calculate the Average & Median on a particular field in a query.
=Median("qry2007_KPI_AvgJan_A","ContractSignedToReceived")
query name is qry2007_KPI_AvgJan_A
field name is ContractSignedToReceived
I am getting #Name? error.
Any ideas why? Thanks for any help troubleshooting this.
View 1 Replies
View Related
Jul 6, 2013
I have a report that calculates a average value from a field. Is there a way to figure out the median value? I did not see a function for this.
View 14 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
Mar 26, 2006
Hello,
I am using Access 97 and I am getting very stuck when I am making a group by query.
Basically I have a table which has for engineering jobs (and a few other collums):
[id], [Site Code], [Ref Number], [Job Size], [Date Origionated] and [Date Completed]
I then have made a query (called [MG - CWD SLA]) which works out for all jobs between 2 dates, how long they have been running for and if they have met the Service Level Agreeement:
SELECT Sheet1.[SITE CODE], Sheet1.[Ref Number], Sheet1.[Job Size], Sheet1.[DATE ORIGINATED], Sheet1.[JOB COMPLETE], (CalcWorkdays([DATE ORIGINATED],[JOB COMPLETE])) AS Expr1, IIf(IsNull(Sheet1.[JOB COMPLETE]),"Not finished",IIf([Job Size] Like "M*",IIf(Expr1<=10,"Y","N"),IIf([Job Size] Like "S*",IIf(Expr1<=5,"Y","N"),iif([Ref Number] LIKE "ID*",iif(Expr1 <= 1,"Y","N"),"N/A")))) AS Expr2
FROM Sheet1
WHERE (((Sheet1.[Help Desk Type])="CWD") AND ((Sheet1.[DATE ORIGINATED]) Between [Type the beginning date:] And [Type the ending date:]))
ORDER BY [DATE ORIGINATED];
I then have made a summary query based on the above query which gives me for every site, between 2 dates; the number of jobs who have met the SLA or not as the case may be.
SELECT [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2, count(*)
FROM [MG - CWD SLA]
GROUP BY [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2;
What I would like to do is for every like outputted by this query, give the number of jobs for this site between those 2 dates, so then for every line of the query give the percent of jobs which met the sla/not met the sla or not finished.
I have been messing around with distinct and I cannot get this to work! Also been trying to do a join or a subquery, but not sure how to integrate this with working with a query based on a query.
Any help would be great!
Thanks,
Steve
View 2 Replies
View Related
Feb 8, 2007
Hi everyone. I'm having some issues creating a query i need for work. I'm able to do this on the report level with a group and footer to get this total. however due to further complexity and Queries that will call upon this one, it needs to be in the first level query i create.
What i have is a database. There are Employees, Dates Paid, timesheet dates, Pay TYPE and hours. here is an Example of a few lines of data. this is how the database is created. it creates a new record for every different pay type paid that week.
EMP#------TRXDATE------TS-DATE----TYPE-----HRS
1----------01/28/07-------01/28/07----100------44.00
1----------01/28/07-------01/21/07----100------44.00
1----------01/28/07-------01/28/07----300-------7.00
3----------01/28/07-------01/28/07----100-------1.00
what i need the query to show for this data would be
EMP#-----TRX DATE----TS-DATE-----REG(100)-OT(300)--TOTAL
1---------1/28/07------1/28/07------44--------7---------51.00
1---------1/28/07------1/21/07------44--------0---------44.00
3---------1/28/07------1/28/07-------1--------0----------1.00
what it basically is would be merging everything so that each TS-DATE would get it's own line in the Query,
any and all ideas and recommendations would be more than greatly appreciated!
View 1 Replies
View Related
May 30, 2005
I am working on a project for work. I need to display in a report the age groups and how many injuries they have had in each age group. I have a table tblAccidents with a field "Date of Birth".
I would like to display in the report how many accidents were within each age group such as;
Age 10-20 23 injuries
Age 20-30 10 injuries
Age 30-40 50 injuries
Age 40-50 8 injuries
Age 50-60 1 injuries
I have been able to get the age from the "Date of Birth" field but how do I split them to the correct age groups?
Any help would be greatly appreciated.
John
View 14 Replies
View Related
Sep 6, 2005
Hi,
i am new using SQL statements,
i have a simple Table something like this from a test station:
--------------------------------
Serial_Number | Status |
--------------------------------
A_____________Pass
B_____________Fail
C_____________Pass
A_____________Fail
B_____________Fail
A_____________Pass
and i need to get a query that separate 2 colums from the Status and count the records for each Serial_Number, some thing like this,
----------------------------------------
Serial_Number | Pass | Fail |
---------------------------------------
A_____________2______1
B_____________0______ 2
C_____________1______ 0
----------------------------------------
does anyone has an idea how to do this in Access?
View 1 Replies
View Related
Apr 20, 2006
Hi everyone,
I'm having problems with a query.
I've got a table with the following fields in my Employee table:
EmployeeID, FirstName, LastName, SupervisorID
I'm trying to create a query that will pull the following information about all the employees:
FirstName, LastName, {Supervisor LastName}, {Supervisor FirstName}
The supervisor first and last name comes from finding the correct EmployeeId that matches the SupervisorID in the employee table.
Table Data:
EmpID, FirstName, LastName, SupervisorID
111, John, Doe, 222
222, Jane, Doe, 444
333, Carl, Smith, 444
444, Sally, Gates, 555
Desired Query Results:
John Doe, Jane Doe
Jane Doe, Sally Gates
Carl Smith, Sally Gates
Sally Gates, etc.....
Would I need to use a GROUP BY in my statement?
Thanks,
CRhodus
View 4 Replies
View Related
Aug 7, 2006
Hello
I have a text box in my database that i would like to be about to search and group the text field if is had similar. for Example if I had a record like" called in puking" and "Up all night puking", I'd like the query to recognize "puking" (without me specifically telling it to look for "puking") and group them.
is this possible?
View 1 Replies
View Related
Aug 16, 2006
Ok, here's what I've got.
I have an access database that links to some SQL Server tables. These tables hold time tickets which keep track of the time that an employee is logged into a job and records the associated product code and work code. All jobs have a work code, and some have a product code. The product coce is the most accurate way to track time, so if a product code is present I need to use that. If it's not present I use the work code.
I currently have a query called 'main' that gets all records in a specified date range and creates a 'code' column. This is a hybrid of the product code and work code using the method described above.
IIf([ProdCode],[ProdCode],[WorkCode])
Everything looks fine there, but when I go to a new query to get total hours by code ('TotalHoursPerCode') by grouping code and getting the sum of ManHour, it doesn't work.
Instead, it lumps all lines that have a product code together but then combines all lines that have no product code into a single line at the top. Instead of using the 'code' column to group.
Here's the SQL from the 'TotalHoursPerCode' query.
SELECT Main.Code, Sum(Main.ManHrs) AS SumOfManHrs
FROM Main
GROUP BY Main.Code;
Any ideas??
Thanks!
http://i45.photobucket.com/albums/f73/VolcomGrrrl79/access.jpg
View 7 Replies
View Related
Aug 26, 2007
I have been struggling with this for a while now and cant seem to get this update to work.
I have a table that holds a project id, a line ID and then the percent allocation to each service line. I am trying to calculate the percent allocation based on the number of repeat project id's IE: 1 project id appears in the table you get 1 for the percent allocation, 2 you get .5, 3 you get .3333333 and so on.
Here is my current query:
UPDATE
rlnAPOppServLine AS A,
(SELECT rlnAPOppServLine.SAPOMID, 1/Count(rlnAPOppServLine.SAPOMID) AS AutoPercent
FROM rlnAPOppServLine
GROUP BY rlnAPOppServLine.SAPOMID) AS B
SET A.PercentAlloc = [B].[AutoPercent]
WHERE (((A.SAPOMID)=[B].[SAPOMID]));
Thanks
View 4 Replies
View Related
Sep 6, 2007
hi everyone,
I have a table with persons age and location and I want to generate a report of all persons by location and age group. The age groups are 0-9, 10 - 19, 20 - 29..., 90-99.
The location codes are 10,20,30,...90,99.
Any suggestions?
Many thanks
Will.
View 2 Replies
View Related
Dec 20, 2007
ex
columns
Name Day1 Day2 Day3 Day4 Day5 Day6
Jackie s s
Jackie v v
Jackie p p
I would like to be combined into
Jackie s s p v v p
all in one row.
View 1 Replies
View Related
Dec 29, 2004
The query below return this data in it's rows:
Rank # Name Div Sex Score
1 8 Bird Twitty 52Kg Female 684.69
2 12 du Toit Carin 52Kg Female 608.77
3 13 Colbert Jackie 52Kg Female 607.62
4 11 Wade Jenna 52Kg Female 595.41
1 7 DeDee Jenna 67.5Kg Female 702.27
2 10 Diva Meisie 67.5Kg Female 550.46
1 5 Man Super 70Kg Male 655.09
1 9 Doe John 82.5Kg Male 480.3
1 3 Man Spider 90Kg Male 537.63
1 6 America Mr 100Kg Male 406.42
1 4 Hulk Incred 110Kg Male 645.8
SELECT (SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE L.uidClassRef = Lifter.uidClassRef AND
H.ScoreTotal > History.ScoreTotal) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
Class.Description AS CDescription,
Activity.Description AS GDescription,
History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND
History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code
GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname,
Lifter.Firstname, Class.Description, History.WeightScoreC,
Lifter.Gender, Activity.Description, Activity.Activity,
Lifter.LifterStatus, History.ScoreTotal
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC,
History.ScoreTotal DESC
I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division. I know that I need a select in the WHERE part but how?
View 7 Replies
View Related
Apr 25, 2005
I’m having the simplest query problem but for the life of me I cannot not figure it out. I have a table it looks like this:
Name DatePriceLocation
Smith03/03/0512.00Dallas
Smith03/04/0513.00Dallas
Smith03/05/0512.00Dallas
Smith03/06/0514.00Dallas
Jones03/03/0512.00Ft. Worth
Jones03/04/0514.00Ft. Worth
Jones03/05/0512.00Ft. Worth
Jones03/06/0512.00Ft. Worth
I want to do a Group by Name, Date, Max(Price), Location. The results of the Query I want would look like this:
Smith03/06/0514.00Dallas
Jones03/04/0514.00Ft. Worth
What keeps happening to me is: I can do a query that will Group by Name and Max(Price) and I get:
Smith 14.00
Jones 14.00
But if I add any other field – say – Date then I get an output that looks like this:
Smith03/03/0512.00
Smith03/04/0513.00
Smith03/05/0512.00
Smith03/06/0514.00
Jones03/03/0512.00
Jones03/04/0514.00
Jones03/05/0512.00
Jones03/06/0512.00
…meaning everything. I’m at the point I want to scream! I cannot not believe I can’t figure this out. Can anyone help?
Marty
View 4 Replies
View Related
Dec 8, 2005
I have a query I need to do but I'm not sure how. I have a DB that has Latitude, Longitude, Signal. What I need is a query to Group By Latitude and Longitude and outputs the Max Signal level for that Lat/Lon group and all records within 4 of Max Signal. I can do the group by lat/lon and max(sig) but I'm having trouble getting the records within 4 (or -4 of the max). I know I can run this in two queries well enough but I'd like to do it in one. If you need the DB please let me know.
Thank you for your consideration and time,
Marty
View 1 Replies
View Related
Oct 24, 2003
I received an error: "You tried to execute a query that
does not include the specified expression 'Field1' as part
of an aggregate function". This is the query I wrote.
SELECT Max(Table1.field3) AS Maxof,
Table1.Field1, Table1.Field2,
Table1.Field4, Table1.Field5,
Table1.Field6
FROM Table1
GROUP BY Table1.Field5, Table1.Field6;
I saved query as q1 then created another query:
SELECT Table1.*
FROM Table1 INNER JOIN q1
ON Table1.field5 =q1.field5
AND Table1.field6 = q1.field6
AND Table1.field3 = q1.maxOf
Any suggestions? Query q1 is the problem but I do not understand what the problem is.
TIA Marty
View 3 Replies
View Related
