query to find missing links

A

aft3rgl0w

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!
 
J

John Spencer

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
..
 
Top