SQL Help

  • Thread starter Thread starter merc
  • Start date Start date
M

merc

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick

--
 
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE..MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE..MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 .....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick

--
 
Pieter

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL;



works but it puts all info into one Field only UPTO_24


CELL UPTO_24
Cell D 4
Cell E 53
Cell E 109
Cell E 118
Cell E 196
Cell F 67
Cell F 72


thanks Nick
















Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
you have to use my seccond version to get them accross or use the below code if you want them on seperate rows
Note that the column headings are determined by the first select

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CNT, "14 To 24" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS SomeThing , "25 To 42" AS Disregarded
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70, "43 To 70" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71, "71=>" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM >=71
GROUP BY MRP_CODE.CELL;

Pieter

Pieter

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL;



works but it puts all info into one Field only UPTO_24


CELL UPTO_24
Cell D 4
Cell E 53
Cell E 109
Cell E 118
Cell E 196
Cell F 67
Cell F 72


thanks Nick
















Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
pieter
SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

im trying to learn

but how do i just finish off this part then i can see how to add rest

Nick

you have to use my seccond version to get them accross or use the below code if you want them on seperate rows
Note that the column headings are determined by the first select

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CNT, "14 To 24" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS SomeThing , "25 To 42" AS Disregarded
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70, "43 To 70" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71, "71=>" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM >=71
GROUP BY MRP_CODE.CELL;

Pieter

Pieter

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL;



works but it puts all info into one Field only UPTO_24


CELL UPTO_24
Cell D 4
Cell E 53
Cell E 109
Cell E 118
Cell E 196
Cell F 67
Cell F 72


thanks Nick
















Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24,
-Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42,
-Sum(IMF_NoMove.DaysSLM >= 43 And IMF_NoMove.DaysSLM <= 70) AS CNT70,
-Sum(IMF_NoMove.DaysSLM >= 71 And IMF_NoMove.DaysSLM <= 4200) AS CNT70+;

is this something like what you mean

Thanks

Nick
you have to use my seccond version to get them accross or use the below code if you want them on seperate rows
Note that the column headings are determined by the first select

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CNT, "14 To 24" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS SomeThing , "25 To 42" AS Disregarded
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70, "43 To 70" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71, "71=>" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM >=71
GROUP BY MRP_CODE.CELL;

Pieter

Pieter

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL;



works but it puts all info into one Field only UPTO_24


CELL UPTO_24
Cell D 4
Cell E 53
Cell E 109
Cell E 118
Cell E 196
Cell F 67
Cell F 72


thanks Nick
















Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
You must add the from clause etc (and don't use + etc for Aliasing)

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24,
-Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42,
-Sum(IMF_NoMove.DaysSLM >= 43 And IMF_NoMove.DaysSLM <= 70) AS CNT70,
-Sum(IMF_NoMove.DaysSLM >= 71) AS CNT71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

Pieter
SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24,
-Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42,
-Sum(IMF_NoMove.DaysSLM >= 43 And IMF_NoMove.DaysSLM <= 70) AS CNT70,
-Sum(IMF_NoMove.DaysSLM >= 71 And IMF_NoMove.DaysSLM <= 4200) AS CNT70+;

is this something like what you mean

Thanks

Nick
you have to use my seccond version to get them accross or use the below code if you want them on seperate rows
Note that the column headings are determined by the first select

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CNT, "14 To 24" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS SomeThing , "25 To 42" AS Disregarded
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70, "43 To 70" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71, "71=>" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM >=71
GROUP BY MRP_CODE.CELL;

Pieter

Pieter

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL;



works but it puts all info into one Field only UPTO_24


CELL UPTO_24
Cell D 4
Cell E 53
Cell E 109
Cell E 118
Cell E 196
Cell F 67
Cell F 72


thanks Nick
















Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 
Brilliant thanks M8
You must add the from clause etc (and don't use + etc for Aliasing)

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24,
-Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42,
-Sum(IMF_NoMove.DaysSLM >= 43 And IMF_NoMove.DaysSLM <= 70) AS CNT70,
-Sum(IMF_NoMove.DaysSLM >= 71) AS CNT71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

Pieter
SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24,
-Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42,
-Sum(IMF_NoMove.DaysSLM >= 43 And IMF_NoMove.DaysSLM <= 70) AS CNT70,
-Sum(IMF_NoMove.DaysSLM >= 71 And IMF_NoMove.DaysSLM <= 4200) AS CNT70+;

is this something like what you mean

Thanks

Nick
you have to use my seccond version to get them accross or use the below code if you want them on seperate rows
Note that the column headings are determined by the first select

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CNT, "14 To 24" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS SomeThing , "25 To 42" AS Disregarded
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70, "43 To 70" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71, "71=>" AS DAYSSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM >=71
GROUP BY MRP_CODE.CELL;

Pieter

Pieter

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
union SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL;



works but it puts all info into one Field only UPTO_24


CELL UPTO_24
Cell D 4
Cell E 53
Cell E 109
Cell E 118
Cell E 196
Cell F 67
Cell F 72


thanks Nick
















Extra UNION ALL at the end

Pieter
HI PIETER I TRYED THIS BUT GET SYNTAX ERROR

ANY SUGGESTIONS WHERE I AM GOING WRONG


SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_24
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_42
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS UPTO_70
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 43 AND 70
GROUP BY MRP_CODE.CELL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS OVER_71
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 71 AND 4200
GROUP BY MRP_CODE.CELL
UNION ALL;

NICK

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 14 AND 24
GROUP BY MRP_CODE.CELL
UNION ALL
SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE IMF_NoMove.DaysSLM BetWeen 25 AND 42
GROUP BY MRP_CODE.CELL

etc will give four rows

another trick

SELECT -Sum(IMF_NoMove.DaysSLM >= 14 And IMF_NoMove.DaysSLM <= 24) AS CNT24, -Sum(IMF_NoMove.DaysSLM >= 25 And IMF_NoMove.DaysSLM <= 42) AS CNT42 ....

will give the four in one row

HTH

Pieter

hi i have a query

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS CountOfDaysSLM
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
GROUP BY MRP_CODE.CELL;

is it possible to create 4 sets of the count by IMF_NoMove.DaysSLM

1. count between >=14<=24
2. count between >=25<=42
3. count between >=43<=70
4. count between >=71

rather than just one count

Many Thanks

Nick
 

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

Similar Threads

SIMPLE QUESTION MAYBE? need to count records BUT only records that 2
Help with an SQL query 4
Access and SQL query Issue 4
Changing Query's SQL using VBA 1
retain entry from parameter query (2 entries) for report display 3
Complex SQL Statement 1
DateValue 5
Rank a Rank? 1

Back
Top