Hello!
I have a table wich contains product number and different time date attachet pr product number.
Today we have used to calculate MEAN to get an average time consumed pr product. We have now learned that MEDIAN would give us more quality data since we sometimes have abnormalities on time consumed.
However. Access 2007 does not seem to have a MEDIAN function, and when i google it i only find code to do this for an entire dataset.
I have 10000 different products in the table, all with 10-50 time consumed rows. I also need to update our MEDIAN time consumed data pr product a couple of times a day. If i should use the code that M$ provides i have to close and open a connection to the table 10000 to get the results that i want
http://support.microso ft.com/kb/q95918/
Anybody have seen another solution for this?
4 3943 ADezii 8,834
Recognized Expert Expert
Subscribing, will return later.
nico5038 3,080
Recognized Expert Specialist
I found various routines when searching using "access median function".
The only additional thing for you is the filtering of the data for one product, thus you'll have to add the productID to the parameters of the function and the WHERE clause in the strSQL.
Getting the idea ?
Nic;o)
ADezii 8,834
Recognized Expert Expert
Here is a little Function that I created that is used within the context of a Calculated Field in a DISTINCT Query. In this particular case, it accepts two Arguments: Distinct Field and Field to calculate the actual Median Value. In this specific instance, I calculated the Median Value for properties by Area. I'll post the SQL, Function Definition, Sample Data, and Query Execution results for clarification. - SQL Statement
- SELECT DISTINCT tblValues.Area, fCalculateMedian([Area],[Price]) AS Median
-
FROM tblValues
-
ORDER BY tblValues.Area;
- Function Definition
- Public Function fCalculateMedian(strArea As String, curPrice As Currency)
-
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
-
Dim intNumOfRecords As Integer, curPriceValue As Currency
-
-
MySQL = "SELECT tblValues.Area, tblValues.Price FROM tblValues "
-
MySQL = MySQL & "WHERE tblValues.Area='" & strArea & "' ORDER BY tblValues.Area, tblValues.Price;"
-
-
Set MyDB = CurrentDb()
-
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
-
-
MyRS.MoveLast: MyRS.MoveFirst
-
-
intNumOfRecords = MyRS.RecordCount
-
-
If intNumOfRecords = 0 Then
-
fCalculateMedian = Null
-
Exit Function
-
End If
-
-
If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
-
MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
-
curPriceValue = MyRS![Price] '1st value to average
-
MyRS.MoveNext
-
curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value
-
fCalculateMedian = Format$(curPriceValue / 2, "Currency") 'Average them out
-
Else 'Odd number of Records
-
MyRS.Move (intNumOfRecords \ 2)
-
fCalculateMedian = Format$(MyRS![Price], "Currency")
-
End If
-
-
MyRS.Close
-
End Function
- Values in tblValues
- Area Price
-
California $100,000.00
-
California $150,000.00
-
California $120,000.00
-
New York $110,000.00
-
New York $20,000.00
-
New York $150,000.00
-
New York $120,000.00
-
Philadelphia $300,000.00
-
Philadelphia $100,000.00
-
Philadelphia $200,000.00
-
Philadelphia $116,000.00
-
Philadelphia $90,000.00
-
California $38,000.00
-
Trenton $0.00
- Query results
- Area Median
-
California $110,000.00
-
New York $115,000.00
-
Philadelphia $116,000.00
-
Trenton $0.00
P.S. - There is another alternative if you are interested and that is to let Excel do the work by passing an Array to its MEDIAN() Function.
Thank you! That did the trick.
You have solved the challenge :=)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ross Contino |
last post by:
Hello to all:
I have been searching the web for examples on how to determine a median
value in a mySQL table. I have reviewed the article at
http://mysql.progen.com.tr/doc/en/Group_by_functions.html. I am an
experienced VB programmer that has recently moved to PHP/mySQL. My employer
has a text file outputted from a vendor specific software with data.
However it cannot be manipulated because it is text. I created a web that
reads the...
|
by: Hugo L. |
last post by:
I really don't know how to calculate the median. Can anybody help me?
|
by: uspensky |
last post by:
I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
|
by: Jaime Leivers |
last post by:
Here's a median function in access that you can call in any query. You
could change this to any excel function you wanted. Most people can
find the windows help file that says how to call an excel function but
don't know how to pass an array of the recordset they made into that
function. This uses GetRows that nicely creates an array that can be
passed into excel.
I have this data table for testing. The table name is TestData
...
|
by: Sebastien.LICHTHERTE |
last post by:
Hello,
I need to calculate the median and percentile of values in a group by
query satisfying several criteria the user is asked to fill in when
opening the query.
A have a table called RX with this kind of data :
HOSP TYPE SUB DAP
A THO F 1
| |
by: brady |
last post by:
I have a function called int calcMedian(int ar, int numElements) How do i code the function to calculate the median in the array? Please help
|
by: CrostonScottish |
last post by:
Has anybody got any ideas or nifty code for calculating the median value in a form.
I currently have a database which we use for post-course evaluations. Part of the evaluation asks the attendees for scores 1 to 10 on the course. Our education assistant inputs data from the evaluation forms into the database form. They manually add the number of 10's, number of 9's etc and input these values into fields on the form , and so on.
What i...
|
by: mehwishobaid |
last post by:
i dont know wat is wrong with my code. when i compile. i get the error
saying line 29: error: expression must have pointer-to-object type
#include <iostream>
using namespace std;
#include <vector>
double* median(double a, int a_size) // Function Median
|
by: rrstudio2 |
last post by:
I am using the following vba code to calculate the median of a table
in MS Access:
Public Function MedianOfRst(RstName As String, fldName As String) As
Double
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
|
by: dmorand |
last post by:
Does anyone have any experience with retrieving a median value using SQL server? I'm trying to retrieve a median value from a list of various numerical values.
Any help would be appreciated.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |