Grouping data into defined categories

[trojan69]

I need help in determining the best way to count how many IP’s fall into categories based on usage value. I am using Access 2000 to accomplish.

Groupings – Put into a table? Best method?

0 to 10,000
10.000 to 50,000
50,000 to 90,000
Over 90,000

Data Output from external system that needs to be linked to Access.
IP Usage
192.23.64.128 102,250
192.23.64.129 101,150
192.23.64.130 99,775
192.23.64.131 92,375
192.23.64.132 70,100
192.23.64.133 9,875


Desired result would be to look at the data output and count based on the value. The desired result would be as follows:

Usage Categories Count
0 to 10,000 1
10.000 to 50,000 0
50,000 to 90,000 1
Over 90,000 4

 

[PWise]

I Would put into A table and
inner join
on Usage between lowRange and UpperRange

note: range should be

0 10,000
10,001 50,000
50,001 90,000
90,00, 999999999

 

[PWise]

sorry sb


0 10,000
10,001 50,000
50,001 90,000
90,001 999999999

 

[trojan69]

pwise

Looking to count generated usage value based on table. I understand that the table needs to be created. The part of relating the file to the table and generating the counts is where I would request more guidance. This is probably really easy but just not sure how to accomplish. Thanks!

 

[PWise]

try

Select ip, fromrange,torange count(*)
from iptable
inner join range 
on Usage between rangestar and rangeend
group by ip, fromrange,torange

 

[PWise]

sorry sb the other way

Select fromrange,torange count(*)
from range 
left join iptable
on Usage between rangestar and rangeend
group by  fromrange,torange

 

[trojan69]

Ok so I have the following tables

Table - range

fields- fromrange torange
0 10,000
10,001 50,000
50,001 90,000
90,001 999999999


Table -iptable

fields - IP Usage
192.1.1.1 5,000
192.1.1.2 10,500
192.1.1.3 12,000
192.1.1.4 83,000
192.1.1.5 85,000
192.1.1.6 100,000
192.1.1.7 200,000
192.1.1.8 500,000

now I can't join these tables on anything such as usage which the SQL statement suggests.Maybe I am missing something?

 

[PWise]

Create a new query
view menu --> Sql View and paste my sql statment and run the query

 

[trojan69]

that is what I did yesterday and received error.

Syntax error (missing operator) in query expression 'torange count(*)'

 

[PWise]

missing comma

Select fromrange,torange[COLOR=red],[/color] count(*)
from range 
left join iptable
on Usage between rangestar and rangeend
group by  fromrange,torange

 

[trojan69]

I caught the missing comma when looking at it more. Now getting another error

Between operator without And in query expression 'Usage between rangestar'

 

[PWise]

Typing to quick
Try
Select fromrange,torange, count(*)
from range
left join iptable
on Usage between fromrange And torange
group by fromrange,torange

 

[trojan69]

Now getting following error

Between operator without And in query expression 'Usage between fromrange'

 

[PWise]

Select fromrange,torange, count(*) as countinrange
from range
left join iptable
on Usage between fromrange And torange
group by fromrange,torange

 

[trojan69]

still generates the same error....darn thing!

 

[PWise]

please post your full SQl

 

[trojan69]

putting in exactly what you suggested

Select fromrange,torange, count(*) as countinrange
from range
left join iptable
on Usage between fromrange And torange
group by fromrange,torange

 

[trojan69]

My database currently has two tables

Range (fromrange,torange) – range of possible values – manually built into table in Access
0 10,000
10,001 50,000
50,001 90,000
90,001 999999999

iptable (IP,Usage) – Generated from ODBC connection to Oracle DB
192.1.1.1 5,000
192.1.1.2 10,500
192.1.1.3 12,000
192.1.1.4 83,000
192.1.1.5 85,000
192.1.1.6 100,000
192.1.1.7 200,000
192.1.1.8 500,000

I need to take the iptable and reference to the range table and count for each.
Output would be as follows:
From Range To Range Number of IPs in each range
0 10,000 1
10,001 50,000 2
50,001 90,000 2
90,001 999999999 3

Example of what needs to happen
Take 192.1.1.1 ? look at value which is 5,000? count that in the table in range 0 to 10,000
Take 192.1.1.2 look at value which is 10,500? count that in the table in range 10,001 to 50,000

I have tried the SQL command suggested here in Access and continue to receive error.
I'm so struggling on this part, can someone give me some suggestions? Thanks in advance.

 

[PWise]

try

Select range.fromrange,range.torange, count(*) as countinrange
from range 
left join iptable
on iptable.Usage between range.fromrange And range.torange
group by  range.fromrange,range.torange

or

Select range.fromrange,range.torange, count(*) as countinrange
from range 
left join iptable
on iptable.Usage <= range.torange
and iptable.Usage >= range.fromrange
group by  range.fromrange,range.torange

 

[trojan69]

pwise,

The latter code works! THANKS!!!!!!

Now I need to work on the next piece....this analysis is something new we are doing and trying to get the desired results for my vp.

I need to take the total number of IP entries ( 8 in my example) and provide a percentage of IPs for each category of total.

example:

From Range To Range Number of IPs in each range
0 10,000 1 1/8 = 12.5%
10,001 50,000 2 2/8 = 25%
50,001 90,000 2 2/8=25%
90,001 999999999 3 3/8 = 37.5%