O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Determining the Probability Mass Function for a Set of Data

Problem

I'd like to compute a probability for each item in a table.

Solution

You can use a SELECT statement like this to determine the probability of each data item:

	SELECTRound(DisneyClose,0) AS StockValue, Count(*) AS Days,
	   (Select Count(*) From Stocks Where Date Between #1-1-2000# And #12-31-2005#)
	   AS Total, Days/Total AS Probability
	FROM Stocks
	WHERE Date Between #1/1/2000# And #12/31/2005#
	GROUP BY Round(DisneyClose,0)
	ORDER BY Round(DisneyClose,0);

The result of this query is displayed in Figure 10-12.

In this statement, each data item is rounded to the nearest dollar using the Round function. You can instead round to one or more decimal places by changing the 0 in the function to the number of decimal places you want to keep.

While the nested Select statement may seem expensive, it really isn't. Jet only computes the value once because it doesn't depend on anything from the current row. It can then return the value for each row it returns.

Discussion

Figure 10-13 shows the probability distribution curve for the probability mass function. This was created by viewing the query results as a PivotChart, using StockValue as a category field and Sum of Probability as a series field, and dropping Probability onto the chart.

Computing the probability mass function for a set of data

Figure 10-12. Computing the probability mass function for a set of data

While this doesn't fit the traditional ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required