At most one record can be returned by this subquery. (Error 3354)

[Falcon88]

Hiiiiiiii All Dears:

i have three tables as in the attached Picture ,

How to Know (query )for all employees that is not in this shift.
i try :

SELECT tblEmploees.EmploeeID, tblShiftEmploDetils.EmploeeNo, tblShiftEmploDetils.ShiftNo, tblEmploees.EmploeeName, 
(SELECT Dupe.EmploeeID
FROM tblEmploees AS Dupe LEFT JOIN tblShiftEmploDetils AS Dupe1 ON Dupe.EmploeeID = Dupe1.EmploeeNo
WHERE (((Dupe1.ShiftNo)=[Forms]![frmOrders]![subfrmShifts].[Form]![ShiftID]))) AS bff
FROM tblEmploees LEFT JOIN tblShiftEmploDetils ON tblEmploees.EmploeeID = tblShiftEmploDetils.EmploeeNo
GROUP BY tblEmploees.EmploeeID, tblShiftEmploDetils.EmploeeNo, tblShiftEmploDetils.ShiftNo, tblEmploees.EmploeeName;

but get "At most one record can be returned by this subquery. (Error 3354)"

 

[Falcon88]

this is my db.

i use Dupe and Dupe1 as alias names.

 

[sneuberg]

Edit: I deleted some posts. So this is not what you got in your email.

If I understand what you want this query would show the employees on the shift selected in [Forms]![frmOrders]![subfrmShifts].[Form]![ShiftID]

SELECT tblShiftEmploDetils.EmploeeNo
FROM tblShiftEmploDetils
WHERE tblShiftEmploDetils.ShiftNo=1;

then you could use the in a WHERE NOT In clause like

SELECT DISTINCT tblEmploees.EmploeeID, tblEmploees.EmploeeName
FROM tblEmploees
WHERE  tblEmploees.EmploeeID Not In (
SELECT tblShiftEmploDetils.EmploeeNo
FROM tblShiftEmploDetils
WHERE tblShiftEmploDetils.ShiftNo= [Forms]![frmOrders]![subfrmShifts].[Form]![ShiftID] );

to show the employee not on that shift.

I think you can do this without the subquery. I'll see if I can come up with that version.

 

[sneuberg]

The way to do this that avoids the potentially slow subquery is to create a query that shows the employees on shift, e.g.

SELECT tblShiftEmploDetils.EmploeeNo
FROM tblShiftEmploDetils
WHERE (((tblShiftEmploDetils.ShiftNo)=[Forms]![frmOrders]![subfrmShifts].[Form]![ShiftID]));

Save this query as for example I named it qryEmployeesOnShift. Then you can use this in the query wizard to create an unmatched query. It will produce a query like:

SELECT tblEmploees.EmploeeID, tblEmploees.EmploeeName
FROM tblEmploees LEFT JOIN qryEmployeesOnShift ON tblEmploees.EmploeeID = qryEmployeesOnShift.EmploeeNo
WHERE (((qryEmployeesOnShift.EmploeeNo) Is Null));

 

[Falcon88]

The way to do this that avoids the potentially slow subquery is to create a query that shows the employees on shift, e.g.

SELECT tblShiftEmploDetils.EmploeeNo
FROM tblShiftEmploDetils
WHERE (((tblShiftEmploDetils.ShiftNo)=[Forms]![frmOrders]![subfrmShifts].[Form]![ShiftID]));

Save this query as for example I named it qryEmployeesOnShift. Then you can use this in the query wizard to create an unmatched query. It will produce a query like:

SELECT tblEmploees.EmploeeID, tblEmploees.EmploeeName
FROM tblEmploees LEFT JOIN qryEmployeesOnShift ON tblEmploees.EmploeeID = qryEmployeesOnShift.EmploeeNo
WHERE (((qryEmployeesOnShift.EmploeeNo) Is Null));

Very thanks , this is exactly what i want.