Solved: SQL SYNTAX ERROR

[mercmannick]

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;

Can anyone tell me where i have gone wrong here ?

error is

syntax error in query.expression 'MRP_CODE.CELL'
SELECT MRP_CODE.CELL

thanks Merc

[OBP]

I am not that good with SQL, but is .CELL a corect item in Access?
The quickest way to find the correct syntax is to use the Query Wizard to crate a simple select query using the MRP_Code and then switch to SQL view.

[mercmannick]

OBP

yes cell is from MRP_CODE tbl

Regards

Merc

[mercmannick]

SELECT MRP_CODE.CELL, Count(IMF_NoMove.DaysSLM) AS 14
FROM IMF_NoMove INNER JOIN MRP_CODE ON IMF_NoMove.[MRP Cont] = MRP_CODE.MRP_CODE
WHERE (((IMF_NoMove.DaysSLM)>=14 And (IMF_NoMove.DaysSLM)<=24))
GROUP BY MRP_CODE.CELL;

works for the single count

how can i have the next

25-42
43-70
71+

Merc

[OBP]

I think you have to have the word Union or Union All to append the second and subsequent Select(s) to the first.

[mercmannick]

YES

but i cant seem to get SYNTAX right at moment :(

Merc

[geekgirlau]

The structure of your first attempt looked almost correct:



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

[mercmannick]

thanks geekgirl

Merc