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?
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:
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:
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)
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?
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?
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.
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.
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?
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,
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.
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.
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;
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]));
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.
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?
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?
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.
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
