hi.
I have an id column and a value column in a table
eg
1 - 5
1 - 10
1 - 20
1 - 50
2 - 8
2 - 20
2 - 30
2 - 21
How do i do a a query that says
id column- 1-5, 5-10, 10-50, 50+ (range)
1 - 1, 1, 2, 0
2 - 0, 1, 3, 0
hi.
I have an id column and a value column in a table
eg
1 - 5
1 - 10
1 - 20
1 - 50
2 - 8
2 - 20
2 - 30
2 - 21
How do i do a a query that says
id column- 1-5, 5-10, 10-50, 50+ (range)
1 - 1, 1, 2, 0
2 - 0, 1, 3, 0
you can use the partition function
https://docs.microsoft.com/en-us/off...ition-function
then change to a crosstab query with the partition field as the column heade
edit: just realised your grouping are not all the same size so having used the partition function, you'll need to add 10-20,20-30 etc for 10-50
Also, your ranges need to be clearer - where should
1 - 10
be counted? in 5-10 or 10-50?
Select fld1,count(fld1) from table,
Select fld2, count(fld2) from table,
Etc.
Can you elaborate please. I am new to access
in first column of query i add the id field.
In the second column i add the field that has all the values
Where do i put >1 and <=5, is it in the criteria?
Where do i put count?
Willl that then say in that id there is 1 count of values in the value range between 1 and 5
That is what i am trying and its not working for me.
Give a try to this:
Code:SELECT id, Sum(-([num]>0 And [num]<6)) AS [1-5], Sum(-([num]>5 And [num]<11)) AS [6-10], Sum(-([num]>10 And [num]<51)) AS [11-50], Sum(-([num]>50)) AS [50+] FROM tblNums GROUP BY id;
Last edited by accesstos; 01-24-2021 at 07:30 AM.