Require parameter value when running SQL

[davidman]

Hi



I have the query below, but whenever I try to run it Access wants me to enter a value for CurrentPoints, could someone have a look at my query please?

I'm trying to show all employees who have less than 1.5 points, are 3P Onsite Workers and have been here for 8 weeks or more.

SELECT WFR.EmployeeID,
WFR.EmployeeName,
WFR.Supervisor,
(SELECT SUM(Points)FROM Points WHERE Points.EmployeeID = WFR.EmployeeID GROUP BY Points.EmployeeId) AS CurrentPoints,
WFR.RehireDate,
WFR.EmployeeClass
FROM WFR
WHERE WFR.EmployeeID Not In (SELECT ID FROM Disciplinaries) AND WFR.EmployeeClass = '3P Onsite Worker' AND CurrentPoints < 1.5
AND DATEDIFF(d, Now(),WFR.RehireDate) > 7 * 8;

[ssanfu]

WHERE WFR.EmployeeID Not In (SELECT ID FROM Disciplinaries)

I don't think this will give you the results you expect.
This will result in a 1 to 1 relationship between WFR.EmployeeID and Disciplinaries.ID.
Shouldn't it be

WHERE WFR.EmployeeID Not In (SELECT EmpID FROM Disciplinaries)

As for the request for the parameter, you might create another query

SELECT EmpId, SUM(Points) AS CurrentPoints FROM Points GROUP BY Points.EmpId

Name it "qryPoints"

and join it with the your query.

This is air code. I do not have Access available to test this , but you should get the idea....

SELECT WFR.EmployeeID,
WFR.EmployeeName,
WFR.Supervisor,
WFR.RehireDate,
WFR.EmployeeClass,
qryPoints.CurrentPoints
FROM WFR INNER JOIN qryPoints ON qryPoints.EmpID = WFR.EmployeeID    ' << not sure if this is correct syntax
WHERE WFR.EmployeeID Not In (SELECT EmpID FROM Disciplinaries) 
AND WFR.EmployeeClass = '3P Onsite Worker' 
AND qryPoints.CurrentPoints < 1.5
AND DATEDIFF(d, Date(),WFR.RehireDate) > 7 * 8;

The blue is what I changed...

Also, I changed Now() to Date(). Now() is date and time, Date() is the date only (actually the time is 00:00:00)


My $0.02