 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / November 2006Median in group
I would like to find the medians in each group rather than in total. Category Leadtime Overallleadtime Record1 Record2 . . . . Record20 Group footer Median("qryName","Leadtime") Median('qryName","Overallleadtime") It just returns the medians of all records, not the records of each group. Your advice is appreciated. Thanks, Scott To my knowledge, Access does not have a built-in Median function. Where did you get this? What version of Access? On my website (www.rogersaccesslibrary.com), is a small Access database sample called "Median.mdb" which illustrates how to create your own Domain Aggregate function called DMedian, which will allow you to specify a Where condition so you can aggregate on groups.  Signature--Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L > I would like to find the medians in each group rather than in total. > [quoted text clipped - 20 lines] > > Scott Roger, I got the code from Microsoft on article ID 210581. It works correctly and I tried it without grouping. I have no idea how to apply it in grouping on a report. I believe my issue is similar to how to sum a field of records in a group instead of whole recordset. Scott > To my knowledge, Access does not have a built-in Median function. Where > did [quoted text clipped - 30 lines] >> >> Scott The median function in the article does not make any provision for grouping. It finds the median of the entire dataset. My function has a Where clause argument. If you put the field you are grouping by in that argument, then you will get a Median for that group. Signature--Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L > Roger, > [quoted text clipped - 39 lines] > >> > >> Scott Roger, Thanks for your advice. I tried your program and have two issues in my application. 1. If any record is null, the median calculation fails to work. Is there any way to get round it under this situation. 2. I put it =DMedian97("qryLeadtime","Leadtime","Category") in the control source of a text box under the group footer of a report and returned incorrect result plus error message saying "Item not found in this collection.". If I change the where clause to [Category]=ABC, it works correctly. I cannot use it like that since I group Category in the report and would like to get the median in each group. Your further advice is appreciated. Thanks, Scott > The median function in the article does not make any provision for > grouping. [quoted text clipped - 52 lines] >> >> >> >> Scott 1. How do you mean it fails to work? Do you get an error? 2. You need to supply the category as in the query examples: =DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] & "'") This MAY solve both of your problems, depending on what the problem with the Null is: =DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] & "' and [Leadtime] Is Not Null") Signature--Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L > Roger, > [quoted text clipped - 73 lines] > >> >> > >> >> Scott Roger, Many thanks for your further advice. Access did not accept your suggestion statements and returned an error message saying "The expression you entered contains invalid syntax. You may have entered an operand without an operator.". I looked at your 2k Median example database again. There are two modules: one is ADO and function name = DMedian(FieldName,TableName,Criteria) and another one is DAO and function name =DMedian97(TableName,FieldName,Criteria). I imported both into my Access 2003 application. Is there any error I have made? Which function should I use? My report is based on a query that linked with a table. The report is grouped by category and the Median function is applied to the footer of category group. You further advice is highly appreciated. Thanks, Scott > 1. How do you mean it fails to work? Do you get an error? > [quoted text clipped - 95 lines] >> >> >> >> >> >> Scott OK, I wasn't reading your post closely enough. You have the order of arguments wrong. It should be: =DMedian("Leadtime","qryLeadtime","[Category] = '" & Category & "'") This assumes that 1) qryLeadtime is the query being used as the Record Source for the Report. 2) Leadtime is the field that you want to find the median for, and 3) Category is the field you are grouping on and it is a text field. If Category is a numeric field, it would be this: =DMedian("Leadtime","qryLeadtime","[Category] = " & Category ) The only difference between DMedian and DMedian97 is that DMedian97 used DAO. DMedian uses ADO. There is no need to import both of them. If this doesn't help, I'm stuck too. Signature--Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L > Roger, > [quoted text clipped - 118 lines] > >> >> >> > >> >> >> Scott |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.