How to create reports that include empty fields

[Werner Sieg]

I am working with a relational database and am creating a
report from several tables, using the design wizard. The
report looks something like this:

book title, author, publisher.


Each book title has a product id. The authors are listed
in a separate table but are linked by the product id, as
is the publisher. The problem is, if I want to print the
list, if a book title does not have an author or publisher
in the author or publisher table, it does not show up on
the report. How can I make a complete list of titles with
author and title, including those titles with no publisher
or author in theauthor and publisher table. Thank you for
any help.

 

[M Skabialka]

Try a left join, something like:

SELECT [Product table].[book title], [author or publisher table].author,
[author or publisher table].publisher
FROM [Product table] LEFT JOIN [author or publisher table] ON [Product
table].ProductID = [author or publisher table].ProductID;

HTH
Mich

 

[Duane Hookom]

You can set this up in your query. Double-click the join lines and select
the appropriate join option.

 

[Werner Sieg]

Duane,
I set up the query, went to design view, went to tools ->
relationships. Then I double clicked the join lines. I
want it to show all products (books), with their authors,
puublishers and categories. Some of the books don't have
categories so i clicked on the join line and told it I
wanted all products but only those categories that had a
product associated with it. I saved it. The only problem
is, it continued to only show those books that had a
category associated with it. The books form one table, the
categories another, and the key field is product_id.

Duane, any suggestions about how I am not correctly
applying your answer? Thank you.