Solved Query to find users not currently assigned to equipment

[RWilliams500]

Howdy. I'm trying to get a query (and ultimately a form or control) to display any active user that is not currently assigned to the piece of equipment being viewed in a form. I'll give (what I THINK is) the relevant table information. There are other fields in the table, but I didn't want to flood with irrelevant data. If I'm wrong in that choice, let me know and I'll add all table information.

tblUsers - PK UserID (autonumber), FK EmployeeID, Active (true/false field that should equal True for this purpose)

tblEmployees - PK EmployeeID, Active (true/false field that should equal True as well, this is if they are actively employed, vs an active user of the system)

tblEquipment - PK EquipmentID, CO# (string, a more user friendly identifier in the company for the equipment)

tblAssignedExaminers - PK (Compound key?) EquipmentID & UserID, AssignmentTypeID (this would either be 3, or Null. 3 indicates they used to be assigned to the equipment, but it's no longer theirs, and Null would mean they've never been assigned yet)

So, I'm trying to get active employees, who are active users, that are not currently assigned (so either 3 or Null as above) to a piece of equipment being viewed in a form. The purpose being to be able to select them and assign them to the equipment (which I'm sure I can get working through VBA if I can just get the proper list of users to work)

I was trying to do a subform with linked master/child fields, and then I tried something with parameters and nested queries and I'm just going in circles.

If any help or advice could be given, I'd greatly appreciate it!

 

[GPGeorge]

I'm not great at extracting relevant details from a narrative. Perhaps, though, if you provide some sample data for each of the tables described, it would help.

 

[MajP]

Because we do not see the real tables I will assume you can make the query
qryActiveUsers which returns something like UserID,EmployeeFirstName, EmployeeLastName, EmployeeID ....
Something like this if the main form is the equipment

Select * from qryActiveUsers where UserID Not in (Select UserID from TblAssignedExaminers where EquipmentID = [forms]![MainFormName]!EquipmentID)

 

[RWilliams500]

Because we do not see the real tables I will assume you can make the query
qryActiveUsers which returns something like UserID,EmployeeFirstName, EmployeeLastName, EmployeeID ....
Something like this if the main form is the equipment

Select * from qryActiveUsers where UserID Not in (Select UserID from TblAssignedExaminers where EquipmentID = [forms]![MainFormName]!EquipmentID)

Not in! Holy cow, I didn't even know that was a thing. I REALLY need to do a real read of some SQL book or something. I was trying to get it with the Access not matching query. Thank you for the point in the right direction. If I still can't get it, I'll post more information as GPGeorge requested.

Thank you both for responding!

 

[CJ_London]

An alternate to majp’s suggestion

Select qryActiveUsers.* from qryActiveUsers left join TblAssignedExaminers on
 on qryActiveUsers.userid=
TblAssignedExaminers.userid
where TblAssignedExaminers.UserID is null

 

[MajP]

So, I'm trying to get active employees, who are active users, that are not currently assigned (so either 3 or Null as above) to a piece of equipment being viewed in a form.

@CJ_London, I do not think that gives you anything. TblAssignedExaminers is a juntion table with UserID and EquipmentID. That will give you users without any equipment not users who are not assigned to a specific piece of equipment.

 

[CJ_London]

Good point

 

[RWilliams500]

Based off @MajP suggestion this is the query I went with. It SEEMS to work properly, based off my bit of poking around so far. This appears to be getting me people who used to be actively assigned (in tblAssignedExaminers with an assignment type of 3, and not in the table at all having never been assigned).

Thank you very much for the assistance.

SELECT *
FROM qryActiveUsers
WHERE (((qryActiveUsers.UserID) Not In (Select UserID from tblAssignedExaminers where EquipmentID = forms!frmEditEquipment!txtEquipmentID))) OR (((qryActiveUsers.UserID) In (Select UserID from tblAssignedExaminers where EquipmentID = forms!frmEditEquipment!txtEquipmentID AND AssignmentTypeID = 3)))
ORDER BY qryActiveUsers.DisplayName;

 

[MajP]

I think if you went with the left join suggestion it would need to add the inner join to equipment. Something like.

SELECT
    *
FROM
   qryActiveUsers  as t1
 LEFT JOIN
    (SELECT
        UserID
    FROM
        tblAssignedUsers
    WHERE
        equipmmentID = [forms]![mainform]![equipID) AS sub
ON
    t1.UserID = sub.UserID
 WHERE Sub.USERID = null
;