Excel countif based on size of numbers in one column in Access
 

I have 230.000 lines in MS Acces file and would like to count numbers in one
column which are in certain range. Number can vary from -999.999 to +999.999
and need to count number of those in range of -999.999 to -10.000 next will
be -9.999 to -1000 then -999 to -100 and so on up to +999.999

If anyone can give hints of how to do this, either using Excel on top of
Access or brake the file down to several excel sheets in order to count range
there I would be most grateful.

Regards
Kristjan_Thor

I would say, considering the large number of lines of data, that
creating queries in Access would be more appropriate than trying to
break this down in Excel. Please respond back if you can rationalize
the use of Excel in this particular case.

Create a new table (called Bounds, for instance) in Access with three
columns: Range (a text description of your range), Upper and Lower. Put your
values for the upper & lower bounds of each range in the table, and a
description in the Range column

Then you'll need a query, something along the lines of

SELECT b.Range, Count(b.Range) AS CountOfRange
FROM Bounds AS b INNER JOIN
(
SELECT d.DValue, b.Range
FROM Bounds AS b, DataTable AS d
WHERE ([lower]<=[dvalue] And [upper]=[dvalue])
) as q
ON b.Range = q.Range
GROUP BY b.Range;

Replace "DataTable" with the name of your table and "dvalue" the name of the
column you want to summarize

Duke



"Kristjan_Thor" wrote:

I have 230.000 lines in MS Acces file and would like to count numbers in one
column which are in certain range. Number can vary from -999.999 to +999.999
and need to count number of those in range of -999.999 to -10.000 next will
be -9.999 to -1000 then -999 to -100 and so on up to +999.999

If anyone can give hints of how to do this, either using Excel on top of
Access or brake the file down to several excel sheets in order to count range
there I would be most grateful.

Regards
Kristjan_Thor

Even easier query is

SELECT b.Range, count(b.range) as Freq
FROM Bounds AS b, DataTable AS d
WHERE ([lower]<=[dvalue] And [upper]=[dvalue])
group by b.range

again - replace "DataTable" with the name of your table and "dvalue" with
the name of the column that contains the data you want to test

Duke

"Duke Carey" wrote:

Create a new table (called Bounds, for instance) in Access with three
columns: Range (a text description of your range), Upper and Lower. Put your
values for the upper & lower bounds of each range in the table, and a
description in the Range column

Then you'll need a query, something along the lines of

SELECT b.Range, Count(b.Range) AS CountOfRange
FROM Bounds AS b INNER JOIN
(
SELECT d.DValue, b.Range
FROM Bounds AS b, DataTable AS d
WHERE ([lower]<=[dvalue] And [upper]=[dvalue])
) as q
ON b.Range = q.Range
GROUP BY b.Range;

Replace "DataTable" with the name of your table and "dvalue" the name of the
column you want to summarize

Duke



"Kristjan_Thor" wrote:

I have 230.000 lines in MS Acces file and would like to count numbers in one
column which are in certain range. Number can vary from -999.999 to +999.999
and need to count number of those in range of -999.999 to -10.000 next will
be -9.999 to -1000 then -999 to -100 and so on up to +999.999

If anyone can give hints of how to do this, either using Excel on top of
Access or brake the file down to several excel sheets in order to count range
there I would be most grateful.

Regards
Kristjan_Thor