Queries/Counts on one Column

[SBOSlayer]

Dear all,

First off, i'm new here and new to Access, so sorry for my noobishness I have worked with SQL, but years ago and Access is just not cooperating with me lol.

The issue I have is, that I have one table with one column that I am trying to extract data from.

What I would like to do with the Column is split it into a few counts. The Column has numbers in it with KB usage. What i'm trying to do is count the amount of users that use say 0. Then have a column next to it that count say 500 >= 2000 and so on. This is completely baffling me as to why I can't seem to do this. I've even redesigned the database to see if I could fix this problem, but no luck I can build a query for this to be done sepearetly, but can't seem to find a way to put it into one query.

SELECT Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID, [test Usage].[Nov-14], [test Usage].[Nov-14], Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID1
FROM [test Usage]
GROUP BY [test Usage].[Nov-14], [test Usage].[Nov-14]
HAVING ((([test Usage].[Nov-14])=0) AND (([test Usage].[Nov-14])>=500));

Thank you for any help that can be offered. I'm banging my head against a wall :'(

 

[Galaxiom]

Derive a field in the query to partition the useage into the ranges you require. The Partition() function may be useful for this if the ranges are equal width.

Then Group By that field and Count the users. This will give you a record for each range, including a Count.

If you want the results in columns then use a CrossTab query based on these results.

 

[SBOSlayer]

Cheers mate! Will have a bash at this later today

 

[SBOSlayer]

Derive a field in the query to partition the useage into the ranges you require. The Partition() function may be useful for this if the ranges are equal width.

Then Group By that field and Count the users. This will give you a record for each range, including a Count.

If you want the results in columns then use a CrossTab query based on these results.

MATE!!!!!!!!!!! I LOVEEEEEEEEEEE UUUUUUUUUUU!!!!!!! Not normally something i'd say to another dude. If your a lady marry me lol. Thank thank thank you!!!

 

[SBOSlayer]

Many thanks for your previous assistance. I do have one last question and I can be done with this

I'm attempting to put multiple column counts from one table. I'd like it to display, say the following:

Data Usage; Jan 2014; Feb 2014; March 2014;

0 - 500mb; 20; 10;10
1- 3 gb; 30; 20; 10

or something to that effect.

Effectively I need to count ID for each date (based on the partition) and under each of the row headings e.g. Jan - how many users have used this amount of data. Last question I promise ^^.

This is all in one table. So in one table I have everyones data usage, but I want to count each months usage based on the ID. It just seems to be giving me dups and not co-operating.

Any help would be greatly appreciated.

 

[Galaxiom]

Use a crosstab query. The query wizard takes you through it step by step.