Help With Queries

[Jone]

Hi Everybody!
I have two tables: 1)Books 2)Trans , I want to make a query that will only
show fields that I don’t have in the table trans

For example in the table Trans I have a book called “Harry Potter†so in the
query for Books it should show all books besides the book “Harry Potterâ€

 

[BobT]

You need an outer join (well, outer join everywhere except Access). Do the
following:

1. Create a new query.
2. Add your two tables.
3. Create a join between the two tables.
4. Double-click to edit the join.
5. Choose the option to include all the records from BOOKS and only those
records from TRANS where the joined fields are equal.
6. Add the field(s) you want to see from the BOOKS table to your query.
7. Add the field you created the join on from the TRANS table.
8. Set its criteria to "Is Null" (without the quotes).
9. Remove the check mark for "Show" for this Null field.
10. Run your query.

Basically we are saying "Give me all the records from BOOKS where my joined
field in TRANS is NULL". It will only be NULL where we have records in BOOKS
that do not exist in TRANS.

 

[Jone]

How do I make a join?

You need an outer join (well, outer join everywhere except Access). Do the
following:

1. Create a new query.
2. Add your two tables.
3. Create a join between the two tables.
4. Double-click to edit the join.
5. Choose the option to include all the records from BOOKS and only those
records from TRANS where the joined fields are equal.
6. Add the field(s) you want to see from the BOOKS table to your query.
7. Add the field you created the join on from the TRANS table.
8. Set its criteria to "Is Null" (without the quotes).
9. Remove the check mark for "Show" for this Null field.
10. Run your query.

Basically we are saying "Give me all the records from BOOKS where my joined
field in TRANS is NULL". It will only be NULL where we have records in BOOKS
that do not exist in TRANS.

 

[Jone]

I don't know I'm not getting it BobT can you please explain it better, it's
not working

 

[Lord Kelvan]

you know what will be eaiser to just give him the sql because it needs
a sub query

select [booktitle]
from books
where [booktitle] not in (
select [booktitle]
from trans)

to use the above make a new query
click cancel on the pop up window
click view in the menu bar then click sql view
then paste the above in there replacing what is there

make sure you replace booktitle with what ever the field name is that
you use to store the name of the book

Regards
Kelvan

 

[John Spencer]

Try using the UNMATCHED Query Wizard.

In the Db design window on the Queries tab (page) , click on the new button.
Click on Find Unmatched Query Wizard
Select Books for the first screen
Select Trans for the second screen
Keep going until you have built the query.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County