Grouping Query

5 pts.
Tags:
Microsoft Access
Microsoft Access queries
I have a csv file which is exported from another system each day. The file contains various IP addresses with associated usage. ex Address Usage 10.15.22.198 1,200,000 10.15.22.198 1,100,000 10.22.15.194 950,000 10.52.18.187 911,000 10.26.12.194 901,000 10.57.83.187 850,000 I need to write a query to pull this data and put into usage bands and count how many are there. ex. Between 800,000 and 900,000 1 Between 900,000 and 1,000,000 3 >1,000,000 2

Answer Wiki

Thanks. We'll let you know when a new response is added.

In Excel, the COUNTIF function will give you the answer.

Assume your data above is in a block of cells from A5 to B102.

Create a block of “criteria” cells, say in H6 … H10 as follows:
H6 800000
H7 900000
H8 1000000
H9 1500000
H10 2000000
(extend as needed)

In the next column (I6 … I10), enter the formula:

=COUNTIF(A$5:B$102,”>=”&H6)-COUNTIF(A$5:B$102,”>=”&H7)

Enter that in I6, then copy and paste down to I10, which will change the references H6 and H7 to the appropriate cells.

Voila – the table in H6:I10 now contains your results with counts.

I am a little puzzled by your source data, which shows the same IP addresses with different counts. If you need to sum the counts for a given IP address first, that is a different question.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chippy088
    The only reason for this type of data manipulation I can think of is to monitor bandwidth, of traffic recieved by an ip. (We have done this to load balance pc's on subnets. Too many with a high usage rate in a subnet slows down the others.) If I'm right then you are trying to rate the traffic to an ip. So each ip's usage is evaluated and put into a usage class. Try something like this table cell 1 = usage figure table cell 2 = IP address sort highest to lowest on column 1 Then it is just a case of checking if the usage value falls into a defined group, and copying the IP address into that group. You could copy the usage figure as well, just for continuity. Hope this helps. Dave
    4,625 pointsBadges:
    report
  • msi77
    SELECT IIf([usage]>=850000 And [usage]<900000,'Between 850,000 and 900,000',IIf([usage]>=900000 And [usage]<1000000,'Between 900,000 and 1,000,000','>1,000,000')) AS txt, count(*) AS Qty FROM YourTable GROUP BY IIf([usage]>=850000 And [usage]<900000,'Between 850,000 and 900,000',IIf([usage]>=900000 And [usage]<1000000,'Between 900,000 and 1,000,000','>1,000,000')) ;
    1,670 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following