query to sort data

  • Thread starter Thread starter cliff
  • Start date Start date
C

cliff

i have the following data

cdate nos
24/7/2008 12
24/7/2008 16
24/7/2008 23
24/7/2008 35
24/7/2008 42
25/7/2008 16
25/7/2008 25
25/7/2008 32
25/7/2008 43
26/7/2008 04
26/7/2008 13

Now I want to count nos in range 1 to 10,11 to 20, 21 to 30, 31 to 40, 41 to
50
on datewise. Result should be something like this :-
cdate 1-10 11 -20 21-30 31-40 41-50
24/7/2008 0 2 1 1 1
25/7/2008 0 1 1 1 1
26/7/2008 1 1 0 0 0

suppose I want to change range 1 to 5, 6 to 10, 11 to 15,16 to 20, 21 to
25, 26 to 30, 31 to 35,36 to 40 and 41 to 45 etc how to change it?

can u help me out to solve this both problems

thnaks
cliffs
 
Since your ranges are discrete mulitples, you could just perform integer
math on the numbers, and then count the values. Then use a crosstab to
display the format you want.

In the Field row in query design, type:
([nos]-1) \ 10
This will give you 0 for the numbers between 1 and 10, 1 for 11 to 20, and
so on.

Now change it to a crosstab query (Crosstab on Query menu.)
- the expression above = Column Heading (group by)
- cdate = Row Heading (group by)
- nos = the Value (count.)
 
Hi allan,

thanks for your help, It worked well.

Now I have new problem

suppose I want count the nos in different ranges like 1 to 5, 6 to 12, 13
to 20, 21 to 28, 29 to 37, 38 to 49. how to do that?. I want to display
result something like this :-
cdate 1to 5, 6 to 12, 13 to 20, 21 to 28, 29 to 37, 38 to 49
24/7/2008 0 1 1 1 1 1
25/7/2008 0 0 1 1 1 1

26/7/2008 1 0 1

Please help me out to solve this problem

cliff


Allen Browne said:
Since your ranges are discrete mulitples, you could just perform integer
math on the numbers, and then count the values. Then use a crosstab to
dis7play the format you want.

In the Field row in query design, type:
([nos]-1) \ 10
This will give you 0 for the numbers between 1 and 10, 1 for 11 to 20, and
so on.

Now change it to a crosstab query (Crosstab on Query menu.)
- the expression above = Column Heading (group by)
- cdate = Row Heading (group by)
- nos = the Value (count.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

cliff said:
i have the following data

cdate nos
24/7/2008 12
24/7/2008 16
24/7/2008 23
24/7/2008 35
24/7/2008 42
25/7/2008 16
25/7/2008 25
25/7/2008 32
25/7/2008 43
26/7/2008 04
26/7/2008 13

Now I want to count nos in range 1 to 10,11 to 20, 21 to 30, 31 to 40, 41
to
50
on datewise. Result should be something like this :-
cdate 1-10 11 -20 21-30 31-40 41-50
24/7/2008 0 2 1 1 1
25/7/2008 0 1 1 1 1
26/7/2008 1 1 0 0 0

suppose I want to change range 1 to 5, 6 to 10, 11 to 15,16 to 20, 21 to
25, 26 to 30, 31 to 35,36 to 40 and 41 to 45 etc how to change it?

can u help me out to solve this both problems

thnaks
cliffs
Click to expand...
Click to expand...
 
For custom ranges (not merely mulitples of some value), I suggest you create
a table to hold the ranges, e.g.:
AgeGroup MinAge
Toddler 0
Child 5
Teen 13
Adult 18

You can then use this table in your query to assign your records to the
appropriate range, and create a crosstab based on the age groups.

It does take a bit of work to do this well. Tom Ellison explains how:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

cliff said:
Hi allan,

thanks for your help, It worked well.

Now I have new problem

suppose I want count the nos in different ranges like 1 to 5, 6 to 12, 13
to 20, 21 to 28, 29 to 37, 38 to 49. how to do that?. I want to display
result something like this :-
cdate 1to 5, 6 to 12, 13 to 20, 21 to 28, 29 to 37, 38 to 49
24/7/2008 0 1 1 1 1
1
25/7/2008 0 0 1 1 1
1

26/7/2008 1 0 1

Please help me out to solve this problem

cliff


Allen Browne said:
Since your ranges are discrete mulitples, you could just perform integer
math on the numbers, and then count the values. Then use a crosstab to
dis7play the format you want.

In the Field row in query design, type:
([nos]-1) \ 10
This will give you 0 for the numbers between 1 and 10, 1 for 11 to 20,
and
so on.

Now change it to a crosstab query (Crosstab on Query menu.)
- the expression above = Column Heading (group by)
- cdate = Row Heading (group by)
- nos = the Value (count.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

cliff said:
i have the following data

cdate nos
24/7/2008 12
24/7/2008 16
24/7/2008 23
24/7/2008 35
24/7/2008 42
25/7/2008 16
25/7/2008 25
25/7/2008 32
25/7/2008 43
26/7/2008 04
26/7/2008 13

Now I want to count nos in range 1 to 10,11 to 20, 21 to 30, 31 to 40,
41
to
50
on datewise. Result should be something like this :-
cdate 1-10 11 -20 21-30 31-40 41-50
24/7/2008 0 2 1 1 1
25/7/2008 0 1 1 1 1
26/7/2008 1 1 0 0 0

suppose I want to change range 1 to 5, 6 to 10, 11 to 15,16 to 20, 21
to
25, 26 to 30, 31 to 35,36 to 40 and 41 to 45 etc how to change it?

can u help me out to solve this both problems

thnaks
cliffs
Click to expand...
Click to expand...
Click to expand...
 
Back
Top