Inverting a query

[Thorben Grosser]

Hey folks,

it got to build a database for a company archive. It cotains some
queries. The last query I got to do shall show the unused racks.

If using the following query: SELECT zuordnungfa.archiv,
zuordnungfa.schrank AS zuordnungfa_schrank, zuordnungfa.seite,
zuordnungfa.fa, archiv.schrank AS archiv_schrank
FROM zuordnungfa INNER JOIN archiv ON zuordnungfa.schrank =
archiv.schrank;

the query returns every rack, containing one or more folders. This is
exactly the opposite of what I want. Is there any way to select the
inverse or should I write a new (better) query, and how?

Here are the tables

table zuordnungfa (shows the racks and the linked departments;
archiv=number of archive, schrank=racknumber, seite: 1=left, 2= right,
fa=department)
archiv schrank seite fa
1 620 1 ORG
1 5 1 FKA

table archive (has the entries for every folder, ordner=unique
folderid, lagerort=archive on zuordnungfa)
ordner lagerort schrank seite
2-2-124-0001 2 124 2
2-1-620-001 2 62 1
2-1-024-0001 2 24 1
2-1-254-0002 2 254 1

thanks in advance
Thorben Grosser

 

[Allen Browne]

Double-click the line joining the 2 tables in the upper pane of the query
design window. Access pops up a dialog with 3 options. Choose #2 or #3
(depending on what you want.)

In the Criteria row under the other table's primary key (i.e. the table that
may not have any match), enter:
Is Null

The query now returns only the records that have no match in the second
table.

More info about outer joins and nulls:
http://allenbrowne.com/casu-02.html