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
;

 

[Ken Sheridan]

Not in! Holy cow, I didn't even know that was a thing.

Now that you've discovered the NOT IN operator one thing you need to be aware of is that, should the column in question in the subquery be NULL in any row, the query will fail. You can see why if you expand it algebraically:

a NOT IN (x,y,NULL,z)
expands to:
a <>z AND a <> y AND a <> NULL AND a <> z
which, because NULL, as the absence of a value, can't be equal to or not equal to anything (the result will be NULL) so the above will result in:
TRUE AND TRUE AND NULL AND TRUE
which, because every part of a Boolean AND operation must be TRUE for it to evaluate to TRUE, will evaluate to NULL, neither TRUE nor FALSE. Consequently in such a query no rows will be returned.

In your case this won't be a problem because UserID in tblAssignedExaminers, as part of a candidate key, can never be NULL, but if that were not the case then an alternative query would be:

SELECT * from qryActiveUsers
WHERE NOT EXISTS
    (SELECT * FROM TblAssignedExaminers
     WHERE TblAssignedExaminers.UserID = qryActiveUsers.UserID
     AND EquipmentID = [Forms]![MainFormName]!EquipmentID);

 

[RWilliams500]

Now that you've discovered the NOT IN operator one thing you need to be aware of is that, should the column in question in the subquery be NULL in any row, the query will fail. You can see why if you expand it algebraically:

a NOT IN (x,y,NULL,z)
expands to:
a <>z AND a <> y AND a <> NULL AND a <> z
which, because NULL, as the absence of a value, can't be equal to or not equal to anything (the result will be NULL) so the above will result in:
TRUE AND TRUE AND NULL AND TRUE
which, because every part of a Boolean AND operation must be TRUE for it to evaluate to TRUE, will evaluate to NULL, neither TRUE nor FALSE. Consequently in such a query no rows will be returned.

In your case this won't be a problem because UserID in tblAssignedExaminers, as part of a candidate key, can never be NULL, but if that were not the case then an alternative query would be:

SELECT * from qryActiveUsers
WHERE NOT EXISTS
    (SELECT * FROM TblAssignedExaminers
     WHERE TblAssignedExaminers.UserID = qryActiveUsers.UserID
     AND EquipmentID = [Forms]![MainFormName]!EquipmentID);

Thank you very much for the tip. I can definitely picture myself running around in circles in a scenario like this trying to figure out why my new knowledge isn't working in a situation like this.

 

[isladogs]

Some time ago I did some tests comparing the efficiency (speed) using 3 different methods of finding unmatched records: NOT IN / NOT EXISTS and an unmatched query using IS NULL.
By far the slowest method is NOT IN as it cannot make use of any indexing. I would strongly advise using one of the other two methods if possible.

For more details, see my article:

Speed Comparison Tests 12

This article compares three methods of finding differences in records: the unmatched query created with the wizard compared to two methods using subqueries. It is the twelfth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding...

isladogs.co.uk

 

[Ken Sheridan]

By far the slowest method is NOT IN as it cannot make use of any indexing. I would strongly advise using one of the other two methods if possible.

The NOT EXISTS predicate is very efficient provided that SELECT * is used in the subquery as this allows optimal use of the indexes. Mathematically the underlying difference between the EXISTS and IN predicates is that the former is an operation of the relational calculus, whereas the latter is an operation of the less efficient relational algebra.

 

[isladogs]

In my limited tests, the fastest approach was the simple unmatched query, significantly so for non indexed fields and marginally so for indexed fields

It would need more tests to make a definitive statement about the efficiency of NOT EXISTS vs an unmatched query. However NOT EXISTS is definitely more complex to do

 

[Ken Sheridan]

In my limited tests, the fastest approach was the simple unmatched query, significantly so for non indexed fields and marginally so for indexed fields

I agree that a JOIN should be the first choice. I was really comparing the (NOT) EXISTS and (NOT) IN predicates.

 

[isladogs]

I agree that a JOIN should be the first choice. I was really comparing the (NOT) EXISTS and (NOT) IN predicates.

I wrote the article partly to provide evidence that using a subquery isn't always the optimal solution to every problem.
Sometimes the simplest solution is the best.

Subqueries can be very useful and are often the only way to solve particular problems.
However, using subqueries as the first choice to a problem can be a mistake.
Its a real shame that JETSHOWPLAN cannot be used with subqueries.

 

[DaveMD]

@Ken Sheridan 's point regarding unexpected results with NOT IN when there may be NULLs is very important and something that has bit me in the past. As one other consideration along those same lines, I would suggest avoiding using NOT IN (SELECT .....) altogether because inevitably there will be some rare instance where there is even just one NULL value being returned by the subquery which will give very unexpected query results (the opposite of what you might expect). Instead, prefer either using NOT EXISTS or the LEFT JOIN approaches that posters have already described in this thread.

 

[arnelgp]

I would suggest avoiding using NOT IN (SELECT .....) altogether because inevitably there will be some rare instance where there is even just one NULL value being returned by the subquery

No it will not happen, return a Null value.
The subquery will return a Value or No record at all, but not Null value.
Unless the OP has Null values on UserID field.

 

[Ken Sheridan]

No it will not happen, return a Null value.

I made clear that that is the the case in the context of the OP's question, because the column returned by the subquery is part of a candidate key, and therefore cannot be Null. Because the OP was unaware of the NOT IN predicate until now, I was alerting them to the possibility of it failing in other cases where the column definition does not include a NOT NULL constraint.

 

[DaveMD]

@arnelgp I was speaking as more of a rule of thumb to avoid "NOT IN (SELECT*..." in the context of the broader discussion about unmatched query approaches. I understand in the OP's specific instance that it wouldn't happen because the field is part of a PK and can't be NULL. From much experience from working in data warehouse environments with data from many disparate systems, this has become a rule of thumb for me as you can never count on expected or proper constraints being present on the source systems, or extracts themselves may be based on a vendor reporting system that is performing various joins that may result in Nulls in the output fields. This is just one simple way to avoid a pitfall as non-matched queries can always be done just as (if not more) efficiently with a NOT EXISTS or LEFT JOIN approach.