Query to return values where Null exists in relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Probably a simple question, it's just been so long since I've worked in
Access.

I have a list of computers in Tbl_Computer. The KeyID for these is
PC_Asset_Num, which is a unique ID for each PC. I then have a list of staff
members in Tbl_Staff. One of the records in Tbl_Staff is Computer_Assigned.
It has a 1-to-1 relationship with PC_Asset_Num. This is so 1 computer can
only be assigned once.

Now, looking at my records, I can see I have 135 staff entries, all with
values for Computer. However, I have 143 computers. I need to design a query
that shows me which Computers have NOT been assigned.

That is... A value exists in Tbl_Computer which does not have a
corresponding match in Tbl_Staff!Computer.

Thanks in advance for your insight,

-Bob
 
Hi Bob,

SELECT tbl_Computer.PC_Asset_Num
FROM tbl_Staff RIGHT JOIN tbl_Computer ON tbl_Staff.Computer_Assigned =
tbl_Computer.PC_Asset_Num
WHERE tbl_Staff.Computer_Assigned Is Null;

The key to this is the right join; in the query design grid, after you join
the fields from both tables, right-click the join line and select Join
Properties. Choose the options which says "Include ALL records from
tbl_Computer ...". Set the criteria for the Computer_Assigned field to null
to return only those PC_Asset_Num records which are not assigned.

HTH,

Rob
 
Hi Rob,

Thanks very much, that worked perfectly. That's going to make a big
difference for me going forward as well, so your assistance is greatly
appreciated.

Thanks again.

-Bob
 
Back
Top