Cannot add criteria to an existing query in SQL

[aldeb]

I currently have a query (see code below) showing me a total count of WorkUnits. I would like to exclude WorkUnits if the PossibleCause field is Out of Stock. When I add criteria to Where in the code I keep getting an invalid bracketing issue and I cannot solve it no matter what I have tried. Any advice on how to add the critieria above to this query correctly?

SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM [Select Distinct [WorkUnit] 
        FROM WorkUnitsFaultsMainTBL 
           WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
           [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                                       AND 
                                       [Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;

 

[Dennisk]

use brackets seperating each And/OR

WHERE (BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174")) AND
( [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] )


then you can add AND/OR surrounded by their own bracket sets

 

[The_Doc_Man]

Naw, make a second query that uses the first query as input. Put your new WHERE clause elements in the second query, expressed in a way to remove the records that don't qualify. I.e. do NOT select the disqualified records.

This way, you preserve the work in the 1st query and simplify the problem by only adding a little extra work for the overhead of layered queries. Trust me, your computer is in all probability fast enough that you would never know the difference between this approach and a re-write of the original. It is always the disks that cause the bottlenecks. Computers have increased in speed over 1000-fold in the last 10 years or less. Disks - maybe 5-fold at best, and I'm not even going to guarantee that number. So don't worry about making the CPU work a little more.

 

[Moniker]

You could just fix the bracketing issue...

SELECT 
    [Total Work Units] AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM 
    (SELECT DISTINCT 
        [WorkUnit] 
    FROM 
        WorkUnitsFaultsMainTBL 
    WHERE 
        BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") 
            AND ([TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] 
            AND [Forms]![Queries_ReportsFRM]![EndDateTxt])
        AS vTbl)
;

 

[The_Doc_Man]

Fixing the bracketing issue is entirely too straightforward for me...

Besides, this way preserves the original query without trying to edit the darned thing, which is obviously a bit touchy.

 

[Moniker]

Fixing the bracketing issue is entirely too straightforward for me...

One of these days, we're going to have to talk Doc. lol...

I'll private you an email or something (assuming you turn that on at some point).

 

[The_Doc_Man]

Well, moniker, if you ever decide you want to visit N'Awlins, let me know. From Dallas you are a mere 600 miles away by car. I-20 to Shreveport, I-49 to Lafayette, I-10 to N'Awlins. We can talk all you want. I was in Fort Worth for a while after Katrina but I'm home again now and wifey doesn't want me to leave again. Just 'cause the aftermath of the storm kept us apart for 7 months. Can't imagine why she's so clingy after that.

More on topic, I'm semi-serious. If there is trouble editing the original query, layer it and leave the touchy thing alone. Multi-layer queries aren't so bad and they leave your original untouched. Being untouched is important for touchy queries. Simplifies things, you know. Less added work in many cases and minimizes the options for where the error is located. Not that I wouldn't just copy the query and start mucking about in the query myself, you understand, but for persons not confident in their skills, this is a quick and dirty approach that will more rapidly get the job done without breaking anything irrevocably.

 

[Moniker]

I agree with that approach. Keep breaking it down until all the parts make sense and all the parts still keep adding up to the sum you desire.

It does seem, though, that people get a taste of how Access works, and then they want to make a DB to track NASA launch statistics in real-time through a website with drill-down capabilities and per second reporting, and all this after writing a successful SELECT query. I'm being facetious of course, but I think you catch my drift.

Back on topic, did the solution work aldeb? If not, what happened now?