Frequency Distribution in access

Question

I have a table in Access where I want to do a frequency distribution on it.  I can do this in Excel but I have over 60000 records and it is too slow.

Basically,

I want to know the number of people that fall into certain categories of sales.

Say between

14000 - 14500

15000-15500 and so forth

I want the count on these people in these categories and then I know how to split them out from there.  What is the easiest way to do this?

Answer 1

That sounds like a group/total query. 

Define a table which will give the limits, like:

LowMark   HighMark   GroupName   'fields

0               1500           0-1500

1500          2500          1500-2500

0               2500          0-2500

...   data sample.

Note that the groups may overlap, if is what you really want (such as having a count for each state, then for the whol USA, as example).

Next, define a query which will include your initial data AND the previous table (assume it is called Ranges). Be sure it is changed to a TOTAL query. Have the field "value"  from the original table in the grid, change change the GROUP BY to COUNT. Bring it a second time, this tiime, change the GROUP BY to WHERE and add the criteria:

>= [ranges].[LowMark]

Add the field "value" a third time, change the GROUP BY to WHERE, with the criteria:

<  [ranges].[highmark]

(note the use of a strict inequality, not  <=  but only   < )

Finally, add the Rnages.GroupName in the grid, and keep the proposed GROUP BY.

That's all.

Answer 2

The WHERE clause implying TWO tables  does the join (inner join)  IMPLICITLY. In general, an explicit join is prefered, but here, with inequality, you would lose the graphical capabilities, so, for marginal performance penality, it may be better to keep the implicit join AND graphical capability.

Answer 3

Never mind.  Got it to work.  THANK YOU!

Answer 4

Will Partition work for you?   Syntax -  Partition([YourField], Start, End, Segment)

SELECT Partition([Ck_B],14000,75000,500) AS Expr1, Count(Data.Ck_B) AS CountOfCk_B

FROM Data

GROUP BY Partition([Ck_B],14000,75000,500);

---

Build a little, test a little.

Answer 5

Am I supposed to join the tables by anything?