Still having problems w/ Select Query

[melodyF]

My query sums up 3 months of data (quarterly) Employee data. My problem is I have a field called Disqualifier. The Disqualifer can be null or can be a numeric 1-7. If I do not enter a disqualifier, for example in January and February, but enter one for March I get two entries for the employee in my query. One that sums Jan and Feb and one for March with the disqualifier. What I want is only one row in query with the disqualifier if one was entered. I have code in the form that only allows one disqualifier to be entered during a quarter.


SELECT DISTINCT qryEmployee.EmployeeID, qryEmployee.Expr1 AS Name, qryEmployee.CostCenter, qryEmployee.Salary, qryEmployee.Section, Avg(qrySummary.IndPoints) AS AvgOfIndPoints, Avg(qrySummary.TeamPoints) AS AvgOfTeamPoints, [AvgOfIndPoints]+[AvgOfTeamPoints] AS [Total Points], IIf([Disqualifier] Is Not Null,0,([Total Points]-50)/(200-50)*0.12) AS [Actual Percent], IIf([Disqualifier] Is Not Null,0,([Potential Percent]/4*1)*[Salary]) AS [Actual Payout], ([Total Points]-50)/(200-50)*0.12 AS [Potential Percent], ([Potential Percent]/4*1)*[Salary] AS [Potential Payout], qrySummary.Disqualifier, qrySummary.Year
FROM qryEmployee INNER JOIN qrySummary ON qryEmployee.EmployeeID = qrySummary.EmployeeID
WHERE (((qrySummary.Period)="january" Or (qrySummary.Period)="march" Or (qrySummary.Period)="February"))
GROUP BY qryEmployee.EmployeeID, qryEmployee.Expr1, qryEmployee.CostCenter, qryEmployee.Salary, qryEmployee.Section, qrySummary.Disqualifier, qrySummary.Year
HAVING (((qrySummary.Year)="2002"));

 

[shacket]

I think I am following your issue correctly. See if this helps:

You need two separate queries: one to do the calculations and one to do the disqualifier. Then union them together. The first should sum up employee information for the quarter regardless of qualifier. The second simply retrieves the qualifyer on a per-quarter basis. You then have your list of employee info in one query and their disqualifier in another. Union the two together and I think you'll have what you want.

 

[David R]

Did you read my answer to your post on this subject yesterday?

Go to Search, enter your user name, and search last 5 days.