Query to select multiple values if ONLY they exceed the target

[abhipdesh]

Hi,



I'm trying to create a query which will select the employees who exceed the target in all 3 months. I wrote following code--I'm not getting any error message but the query is showing no results. Please help.

SELECT Progress_Reporting_List.[Employee Name], Progress_Reporting_List.[Fiscal Year], Progress_Reporting_List.Month, Progress_Reporting_List.[Calls Offered], Progress_Reporting_List.[Requeued Calls], [Requeued Calls]/[Calls Offered] AS [Requeued Call %]
FROM Progress_Reporting_List
WHERE (((Progress_Reporting_List.[Fiscal Year])="FY13") AND ((Progress_Reporting_List.Month)="April" And (Progress_Reporting_List.Month)="May") AND (([Requeued Calls]/[Calls Offered])>=0.007))
ORDER BY Progress_Reporting_List.[Employee Name], Progress_Reporting_List.[1st Date of Month];

[JoeM]

How is your data structured? If each employee has a separate record for each month, there is no way any one record can satisfy all three criteria at once!
Does each employee only have one record per month?

[abhipdesh]

How is your data structured? If each employee has a separate record for each month, there is no way any one record can satisfy all three criteria at once!
Does each employee only have one record per month?

Yes, each employee only have one record per month. I want to create a query which will show if an employee exceed target for all 3 months. e.g. if he/she does exceed for April, May, and June, then he/she will show on the report. If employee exceed only for 2 months, then he/she will not show up on the report. Is there a way around to do this?

Many thanks for your inputs.

[JoeM]

Can you post a small sample of your data, so we can see the layout and some sample records, and show us what you want your output report to look like (what exactly you want it to show)?
There are a few different ways of getting the data you want, but the best way may be determined by exactly what you need to show on that report.

[June7]

This is tricky because the criteria depends on values in other records of same table. Will involve a sequence of queries (or nested subqueries) or domain aggregate function. Something like:

SELECT [Employee Name], [Fiscal Year], [Month], [Calls Offered], [Requeued Calls], [Requeued Calls]/[Calls Offered] AS [Requeued Call Pct]
FROM Progress_Reporting_List
WHERE DCount("[Employee Name]", "Progress_Reporting_List", "[Fiscal Year]='FY13' AND [Employee Name]='" & [Employee Name] & "' AND [Requeued Calls]/[Calls Offered]>=0.007 AND ([Month]='April' OR [Month]='May' OR [Month]='June'))=3
ORDER BY [Employee Name], [1st Date of Month];

Your query shows only 2 months.

Instead of using 3 months as criteria, suggest creating a field with expression to calculate the quarter and use that value as parameter. All of these parameters can be dynamic by using reference to controls on form as inputs for FY, Quarter, Target.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in names, nor reserved words as names - Month is a reserved word. Also, beginning names with a number can be an issue. Otherwise, must remember to enclose in [].

Also, grouping and sorting on name values can be unreliable. What if you have two Joan Smith? Better to use an ID number as the PK/FK in related tables and don't save the name in the Progress_Report_List table. Retrieve name from Employees table by joining tables in query.

[JoeM]

I was thinking of using Calculated Fields with Aggregate Functions myself, but wanted to see what fields they need to show on the report (to determine if the fields contained in the Aggregate Query are sufficient, or if we will need to link that back to the original table to get more fields not used in the Aggregate Query).

[abhipdesh]

Hi,

I'm getting error message with this syntax.

[June7]

What is the exact error message? Post your exact SQL statement.

I made an edit to my post to fix a typo. Maybe after you originally read. Look at the expression again.

[abhipdesh]

What is the exact error message? Post your exact SQL statement.

I made an edit to my post to fix a typo. Maybe after you originally read. Look at the expression again.

I used the exact same code (copy and paste) that gave earlier. I'm getting an error message for the below part--

"' AND [Requeued Calls]/[Calls Offered]>=0.007 AND ([Month]='April' OR [Month]='May' OR [Month]='June'))=3
ORDER BY [Employee Name], [1st Date of Month];

[June7]

Sorry, another typo. Missing quote mark:

([Month]='April' OR [Month]='May' OR [Month]='June')")=3


However, the filter is still not right. I built a simple table and tested.

SELECT Progress_Reporting_List.[Employee Name], Progress_Reporting_List.[Fiscal Year], Progress_Reporting_List.[Month], Progress_Reporting_List.[Calls Offered], Progress_Reporting_List.[Requeued Calls], [Requeued Calls]/[Calls Offered] AS [Requeued Call Pct], DCount("[Employee Name]","Progress_Reporting_List","[Fiscal Year]='FY13' AND [Employee Name]='" & [Employee Name] & "' AND [Requeued Calls]/[Calls Offered]>=0.007 AND ([Month]='April' OR [Month]='May' OR [Month]='June')") AS Expr1, IIf([Month]="April" Or [Month]="May" Or [Month]="June","Yes","No") AS IsMon
FROM Progress_Reporting_List
WHERE (((Progress_Reporting_List.[Fiscal Year])="FY13") AND ((DCount("[Employee Name]","Progress_Reporting_List","[Fiscal Year]='FY13' AND [Employee Name]='" & [Employee Name] & "' AND [Requeued Calls]/[Calls Offered]>=0.007 AND ([Month]='April' OR [Month]='May' OR [Month]='June')"))=3) AND ((IIf([Month]="April" Or [Month]="May" Or [Month]="June","Yes","No"))="Yes"))
ORDER BY Progress_Reporting_List.[Employee Name], [1st Date of Month];