query to sort data

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
 
A

Allen Browne

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.)
 
C

cliff

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
 
A

Allen Browne

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top