Query with linked tables

[Guest]

I am designing a storage database that tracks books. My books can either be
on shelves, or in boxes which are on shelves. My database consists of the
following tables with the following fields and data types:

Table - Shelves
Shelf Number: AutoNumber
Shelf description: Text

Table – Boxes
Box Number: AutoNumber
Box Name: Text
Shelf Number: Number

Table – Books
Book Number: AutoNumber
Author: Text
Title: Text
Box Number: Number (linked to Box Number in the Boxes table)
Shelf Number: Number (linked to Shelf Number in the Shelves table)

The books have either a Box Number or a Shelf Number in the Books Table,
depending on whether they are in boxes or loose on shelves. Access has
automatically filled the empty field with a zero. Since the Box Number and
Shelf Number fields are AutoNumber, they don’t accept zero as an entry.

I have created a query to list all the books which includes all the fields
in the Books table, as well as the Box Name and Shelf Description. The
problem is that the query does not list any books because each book has
either a Box Number or a Shelf Number that is zero, and therefore does not
have an entry in the Shelves Table or Boxes Table. How do I get the query to
list items that don’t have any entries in the linked tables?

Thanks,
--Andrew

 

[John Vinson]

On Tue, 1 Mar 2005 18:57:01 -0800, "Andrew M" <Andrew

I am designing a storage database that tracks books. My books can either be
on shelves, or in boxes which are on shelves. My database consists of the
following tables with the following fields and data types:

Table - Shelves
Shelf Number: AutoNumber
Shelf description: Text

Table – Boxes
Box Number: AutoNumber
Box Name: Text
Shelf Number: Number

Table – Books
Book Number: AutoNumber
Author: Text
Title: Text
Box Number: Number (linked to Box Number in the Boxes table)
Shelf Number: Number (linked to Shelf Number in the Shelves table)

The books have either a Box Number or a Shelf Number in the Books Table,
depending on whether they are in boxes or loose on shelves. Access has
automatically filled the empty field with a zero. Since the Box Number and
Shelf Number fields are AutoNumber, they don’t accept zero as an entry.

Then turn of the "default default": open [Books] in design view, and
erase the 0 that is on the Default property of the Box Number and
Shelf Number fields.

I have created a query to list all the books which includes all the fields
in the Books table, as well as the Box Name and Shelf Description. The
problem is that the query does not list any books because each book has
either a Box Number or a Shelf Number that is zero, and therefore does not
have an entry in the Shelves Table or Boxes Table. How do I get the query to
list items that don’t have any entries in the linked tables?

Select the join line between Books and Boxes, and between Books and
Shelves, and change the default Inner Join to a Left Outer Join. To do
so, choose option 2 on the join type window - "Show all records in
Books and matching records in Boxes".

John W. Vinson[MVP]