SQL works in one query and not in another

[netrusher]

The below SQL works in one of my queries. I am mainly referring to the Where Clause:

SELECT WorkUnitsFaultsMainTBL.WorkUnit, WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.FaultCategory, WorkUnitsFaultsMainTBL.TodaysDate
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults" And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Start Date] And [End Date]));

When I put the same SQL in another query it does not work. I get the following error:

"The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect"

Select SystemGroup, Count(*) As [SystemGroups]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults" And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Start Date] And [End Date]));
Group By SystemGroup

Can anyone help me to see what I am doing wrong? Thanks in advance.

UNION ALL Select 'Total Work Units', Count(*)
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Start Date] And [End Date];

 

[lespaul]

Well, I don't see anything wrong with it, but I would suggest using the NOT IN instead of <> AND <> to easy readability (I also used an alias A for your table name to make it easier to read):

SELECT A.WorkUnit, A.SystemGroup, A.FaultCategory, A.TodaysDate
FROM WorkUnitsFaultsMainTBL A
WHERE A.FaultCategory NOT IN( "No Faults", "Cosmetic") AND A.TodaysDate) Between [Start Date] And [End Date]));

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[randy700]

Change UNION ALL Select 'Total Work Units'
To UNION ALL Select [Total Work Units]


Randy

 

[PHV]

Perhaps this ?
SELECT D.SystemGroup, Count(*) AS [SystemGroups]
FROM WorkUnitsFaultsMainTBL AS D
WHERE D.FaultCategory NOT IN ('No Faults','Cosmetic')
AND D.TodaysDate Between [Start Date] And [End Date]
GROUP BY D.SystemGroup
UNION ALL SELECT 'Total Work Units', Count(*)
FROM WorkUnitsFaultsMainTBL AS T
WHERE T.TodaysDate Between [Start Date] And [End Date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886