query to find missing links

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

Guest

Hello all, kinda stumped on this one:
I have 3 tables, Media, Software and Links
Media is the main CD, and contains an ID number (d4)
Software is various software components we use, also has a serial #
Links is a ilnk table that links together which software is on which media
I want to design a query which will display all Media numbers that do NOT
have a corresponding link in the Links table (ie which Media do not have any
software linked to them)
Any idea how to go about this? Thanx in advance!
 
The unmatched query wizard should be able to do this for you.

That should construct a query that looks something like the following in the
SQL view

SELECT Media.*
FROM Media LEFT JOIN Links
ON MEDIA.MediaNumber = Links.MediaNumber
WHERE Links.MediaNumber is Null

You can build this yourself in the query grid if you cannot use the wizard
or build it in SQL view
-- Open new query
-- Add Media and Links tables
-- Link the two tables on the proper field (MediaNumber)
-- Double click on the join line and select ALL media and only ... links
-- Add the fields from Media you want to see; add Links.MediaNumber
-- enter IS NULL for the criteria for Links.MediaNumber field
-- run the query

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top