Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Access Team Blog

Get product announcements, tips and tricks, and news directly from the team @ Microsoft.
Create partition queries

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.

Partition Query Example

 

Example from Northwind 2007

Query Design view:

PartitionQueryExample2007a

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:

PartitionQueryExample2007b

Thanks Raymond!

Send your Power Tips to Mike and Chris at accpower@microsoft.com.
Posted: Wednesday, July 29, 2009 3:16 PM by cdowns
Filed under: ,

Comments

Tim Lucas said:

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...

# July 29, 2009 10:41 PM

Bob Larson [Access MVP] said:

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.

# July 30, 2009 12:03 AM

bryan said:

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?

# July 30, 2009 12:30 PM

Chris Downs said:

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.

# July 30, 2009 3:44 PM

Clint Covington said:

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.

http://msdn.microsoft.com/en-us/library/aa172181(office.11).aspx

# July 31, 2009 11:27 AM

Ruud Jeursen [ DeltaBase Support, The Netherlands] said:

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 !

# August 1, 2009 7:57 AM

Dulia Rus said:

Never seen this function before, now that I'm aware of it, I'm sure it will come in handy. Thanks for posting it.

# August 7, 2009 12:13 PM

craig said:

Very handy. Makes you wonder what else is buried in Access?

# August 11, 2009 10:48 PM
New Comments to this post are disabled
Page view tracker