Modifying the Partition() Function

[raskew]

Hi -

This thread http://www.access-programmers.co.uk/forums/showthread.php?t=159712 sought to categorize ages, e.g. 16 - 20, 21 - 25, etc.. It's received a number of responses, most of which are code-intensive using either multiple Iif() statements or Case statements.

Nowhere did anyone hit on the Partition() function, which is designed to do just what the OP was looking for.

The only problem, not resolved with strictly the Partition() function, was that the OP needed to show the initial category as <16 and the last category as >50. I've never seen this issue addressed and it would seem to be a major drawback of the Partition() function.

After some searching, found this MSDN / SQL Server site http://msdn.microsoft.com/en-us/library/ms186307.aspx that addresses this issue with an Alter_Partition_Function, which allows the user to either:

a. Consolidate categories, e.g. 0-4, 5-9, 10-15 into 0-15 (or <16), or

b. Divide an existing category into multiple categories.

Problem being, I'm having extreme problems trying to translate this SQL Server solution to VBA.

If you're looking for an interesting problem, I ask that you take a look at the referenced threads.

Thanks and Best Wishes - Bob

 

[redneckgeek]

That's a new one on me. Never came across that before - in 15 years of working in Access.

 

[raskew]

Hi -

It (Partition() function) has been there all along, but for some reason it's never gotten any attention. It works just

ducky (and eliminates a whole bunch of code), provided that there's no deviation in your categories.

Care to take a shot at it? (If you haven't tried already, reproduce the help file example.)

Best wishes - Bob

 

[redneckgeek]

Hi -

It (Partition() function) has been there all along, but for some reason it's never gotten any attention. It works just

ducky (and eliminates a whole bunch of code), provided that there's no deviation in your categories.

Care to take a shot at it? (If you haven't tried already, reproduce the help file example.)

Best wishes - Bob

Tried it on one of my tables as soon as I saw your post this morning.
Worked perfectly. The only problem I have with it is the way it displays -
if I showed "7:15" in a report, my users would think of TIME. But I guess that's easy enough to work around.

 

[starrcruise]

Good morning. I could truly use the answer to this. I LOVE Partition in Access query for histograms I am creating, but quite correctly, if there are no "records" falling into the partition, Partition leaves out the range. Has anyone figured out a solution other than in T-SQL? thank you!