Conditional conditions?

[Carrie]

Here is my query:

SELECT [Tbl - TS Backlog SR's].Day, Count([Tbl - TS Backlog SR's].[SR #]) AS [Aging Count]
FROM [Tbl - TS Backlog SR's]
WHERE ((([Tbl - TS Backlog SR's].[Backlog Aging]) Like "0-3 Days") AND (([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Intergy - Tech Support" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "TMM - Tech Support" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Health Network" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MENDS" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MDX" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Source" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MacHealth" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MedMaster" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MicroEdge" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Probity" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Smart Practice" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "CMEDS") AND (([Tbl - TS Backlog SR's].Queue) Like "Second Tier" Or ([Tbl - TS Backlog SR's].Queue) Like "Intergy - Format" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS ESG" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS Internal Review" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS MGR" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS TouchPoint" Or ([Tbl - TS Backlog SR's].Queue) Like "ESG 2" Or ([Tbl - TS Backlog SR's].Queue) Like "MDX" Or ([Tbl - TS Backlog SR's].Queue) Like "Software Update" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - HN" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - MENDS" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Source" Or ([Tbl - TS Backlog SR's].Queue) Like "Base" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MedMaster" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MicroEdge" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Probity" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Smart Practice" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - CMEDS - Tech Support") AND (([Tbl - TS Backlog SR's].[SR Sub Status]) Not Like "Project"))
GROUP BY [Tbl - TS Backlog SR's].Day
HAVING ((([Tbl - TS Backlog SR's].Day)>=[Start Date] And ([Tbl - TS Backlog SR's].Day)<=[End Date]));

Under the Functional Sub Area MDX and MENDS there is a queue Software Update. I need to have in this query the Software Update from MDX and not from MENDS.

Is this possible with out doing multiple queries?

Please help!

 

[Andrew Fergus]

Hi Carrie
Try this for your SQL - it's very clunky bit it works. By "clunky" it doubles up the criteria in the query and introduces the exception of MENDS / Software Update in the 2nd line.

Is there any way to group the criteria for the functional sub area and queue to simplify the query and make your life easier later on when the query / report requirements change? If these were held in another table and flagged as being 'included' on the report, then this query could pick up the 'inclusions' and any changes to the items queried / reported would require amending the data in that table (possibly via a form) rather than the queries.

Also, is the "Tbl - TS Backlog SR's" table a double-up of the data held in the table "Tbl - TS Created SR's"?

Here is the revised (and very long) SQL :

SELECT [Tbl - TS Backlog SR's].Day, Count([Tbl - TS Backlog SR's].[SR #]) AS [Aging Count]
FROM [Tbl - TS Backlog SR's]
WHERE ((([Tbl - TS Backlog SR's].[BackLog Aging]) Like "0-3 Days") AND (([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Intergy - Tech Support" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "TMM - Tech Support" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Health Network" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MDX" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Source" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MacHealth" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MedMaster" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MicroEdge" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Probity" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Smart Practice" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "CMEDS") AND (([Tbl - TS Backlog SR's].Queue) Like "Second Tier" Or ([Tbl - TS Backlog SR's].Queue) Like "Intergy - Format" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS ESG" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS Internal Review" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS MGR" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS TouchPoint" Or ([Tbl - TS Backlog SR's].Queue) Like "ESG 2" Or ([Tbl - TS Backlog SR's].Queue) Like "MDX" Or ([Tbl - TS Backlog SR's].Queue) Like "Software Update" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - HN" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - MENDS" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Source" Or ([Tbl - TS Backlog SR's].Queue) Like "Base" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MedMaster" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MicroEdge" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Probity" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Smart Practice" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - CMEDS - Tech Support") AND (([Tbl - TS Backlog SR's].[SR Sub Status]) Not Like "Project")) OR ((([Tbl - TS Backlog SR's].[BackLog Aging]) Like "0-3 Days") AND (([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MENDS") AND (([Tbl - TS Backlog SR's].Queue) Like "Second Tier" Or ([Tbl - TS Backlog SR's].Queue) Like "Intergy - Format" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS ESG" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS Internal Review" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS MGR" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS TouchPoint" Or ([Tbl - TS Backlog SR's].Queue) Like "ESG 2" Or ([Tbl - TS Backlog SR's].Queue) Like "MDX" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - HN" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - MENDS" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Source" Or ([Tbl - TS Backlog SR's].Queue) Like "Base" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MedMaster" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MicroEdge" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Probity" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Smart Practice" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - CMEDS - Tech Support" And ([Tbl - TS Backlog SR's].Queue) Not Like "Software Update") AND (([Tbl - TS Backlog SR's].[SR Sub Status]) Not Like "Project"))
GROUP BY [Tbl - TS Backlog SR's].Day
HAVING ((([Tbl - TS Backlog SR's].Day)>=[Start Date] And ([Tbl - TS Backlog SR's].Day)<=[End Date])) OR ((([Tbl - TS Backlog SR's].Day)>=[Start Date] And ([Tbl - TS Backlog SR's].Day)<=[End Date]));

HTH, Andrew

 

[Carrie]

So the first criteria line has everything and the second criteria line you put what Mends should look at and then on the third you put what it should not look at?

Is this what you are doing?

That's what I gathered from what you did.

Answer to your question...The Created table should not be a part of this query. I must not have deleted it yet when I copied the SQL.

Thanks for the help.

 

[Andrew Fergus]

Hi Carrie
The SQL shouldn't have 3 lines of criteria - only 2. When I looked at my sample query, that I copied the SQL from, it had 2 lines. But if I create a new query and add the SQL that was posted, it has three lines and is completely wrong. I'm not sure what happened there - sorry about that, try this SQL instead :

SELECT [Tbl - TS Backlog SR's].Day, Count([Tbl - TS Backlog SR's].[SR #]) AS [Aging Count]
FROM [Tbl - TS Backlog SR's]
WHERE ((([Tbl - TS Backlog SR's].[BackLog Aging]) Like "0-3 Days") AND (([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Intergy - Tech Support" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "TMM - Tech Support" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Health Network" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MDX" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Source" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MacHealth" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MedMaster" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MicroEdge" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Probity" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "Smart Practice" Or ([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "CMEDS") AND (([Tbl - TS Backlog SR's].Queue) Like "Second Tier" Or ([Tbl - TS Backlog SR's].Queue) Like "Intergy - Format" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS ESG" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS Internal Review" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS MGR" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS TouchPoint" Or ([Tbl - TS Backlog SR's].Queue) Like "ESG 2" Or ([Tbl - TS Backlog SR's].Queue) Like "MDX" Or ([Tbl - TS Backlog SR's].Queue) Like "Software Update" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - HN" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - MENDS" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Source" Or ([Tbl - TS Backlog SR's].Queue) Like "Base" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MedMaster" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MicroEdge" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Probity" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Smart Practice" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - CMEDS - Tech Support") AND (([Tbl - TS Backlog SR's].[SR Sub Status]) Not Like "Project")) OR ((([Tbl - TS Backlog SR's].[BackLog Aging]) Like "0-3 Days") AND (([Tbl - TS Backlog SR's].[Functional Sub Area]) Like "MENDS") AND (([Tbl - TS Backlog SR's].Queue) Like "Second Tier" Or ([Tbl - TS Backlog SR's].Queue) Like "Intergy - Format" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS ESG" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS Internal Review" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS MGR" Or ([Tbl - TS Backlog SR's].Queue) Like "ATS TouchPoint" Or ([Tbl - TS Backlog SR's].Queue) Like "ESG 2" Or ([Tbl - TS Backlog SR's].Queue) Like "MDX" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - HN" Or ([Tbl - TS Backlog SR's].Queue) Like "L2 - MENDS" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Source" Or ([Tbl - TS Backlog SR's].Queue) Like "Base" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MedMaster" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - MicroEdge" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Probity" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - Smart Practice" Or ([Tbl - TS Backlog SR's].Queue) Like "Base - CMEDS - Tech Support") AND (([Tbl - TS Backlog SR's].[SR Sub Status]) Not Like "Project"))
GROUP BY [Tbl - TS Backlog SR's].Day
HAVING ((([Tbl - TS Backlog SR's].Day)>=[Start Date] And ([Tbl - TS Backlog SR's].Day)<=[End Date])) OR ((([Tbl - TS Backlog SR's].Day)>=[Start Date] And ([Tbl - TS Backlog SR's].Day)<=[End Date]));

HTH, Andrew