Query to return values where Null exists in relationship

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beeawwb - 15 Nov 2007 01:33 GMT

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

Reply to this Message

Rob Parker - 15 Nov 2007 03:15 GMT

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 all,
>
[quoted text clipped - 20 lines]
>
> -Bob

Reply to this Message

Beeawwb - 15 Nov 2007 04:59 GMT

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

> Hi Bob,
>
[quoted text clipped - 37 lines]
> >
> > -Bob

Reply to this Message