You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Raymond Starkey, Director of ACCESSible IT Ltd., provided us with an example that he has used to teach Access users about using the Partition function to create a frequency distribution. As he says, “no one ever mentions these but they are so powerful they need a mention.”
Note:Raymond’s example uses the Northwind sample database that was provided with Access 2000-2003. See below for an example that uses data from the Northwind 2007 database.
Query Design view:
The SQL for this query is:
SELECT Partition([Shipping Fee],0,200,10) AS Range, Count(Orders.[Shipping Fee]) AS [CountOfShipping Fee] FROM Orders GROUP BY Partition([Shipping Fee],0,200,10);
Here’s how it looks in Datasheet view:
Thanks Raymond!
Comments: (8) Collapse
I included this as part of an Access Training Course I wrote/delivery for a client (Australia). This is one of those 'if only I knew that' or 'Will never look at it again' topics - but very useful to know! Another use - accounts that fall into 0-29 Days, 30-59 Days, 60-89 Days, 90-120 Days etc...
Wow! This is so simple, yet so powerful. I like it! I have been working with Access for 12 years and this is the first time I've been exposed to this. This is very cool to know. Thanks for providing this.
I noticed that its going beyond the stop parameter, does this mean if you don't know the top number that it will continue and not exclude records?
bryan: You are correct--any values that fall outside of the range that you specify with the 'start' and 'stop' arguments will still be included in the output, they'll just be lumped together. For example, in the Access 2007 example, if I change the arguments to: Range: Partition([Shipping Fee],20,60,10) the output is Range CountOfShipping Fee :19 32
40:49 2
50:59 4
60:60 2
61: 8 So, 32 records have shipping fees of $19.00 or less, and 8 have shipping fees of $61.00 or more. Also, I forgot to mention in the original post that partition queries work really well as the data source for charts.
bryan: if you really cared it I don't see why you couldn't use dmin() and dmax() (or grouped queries) to get the outer ranges to feed into the partition function. msdn.microsoft.com/.../aa172181(office.11).aspx
Thanks Raymond ! Never cane accross this in all the years I've worked with MS Access ! Love the simplicty of it ! Thank you for sharing !
Never seen this function before, now that I'm aware of it, I'm sure it will come in handy. Thanks for posting it.
Very handy. Makes you wonder what else is buried in Access?
Comments: (loading) Collapse