Trying to find how to create a final qry that lists ALL tblItems.ID and tblItems.DescpOfItems in QryTblItems THAT ARE NOT IN QryBookItems using a value of tblBook.ID supplied by frmAddItemsToBook . Final Qry will used in a form that supplies tblBook.ID

Question

QryTblItems      ( has all possible rows of TblItems ) used a a record source for controls and forms.

Has SQL of

 SELECT tblItems.DescpOfItems, tblItems.ID

FROM tblItems

ORDER BY tblItems.DescpOfItems;

 

 

SECOND QRY IS BASED ON A JOIN TABLE    

QryBookItems has SQL of

SELECT tblBook.FileName, tblBook.ID, tblItems.ID, tblItems.DescpOfItems

FROM (tblBook INNER JOIN tblItemsInBook ON tblBook.ID = tblItemsInBook.BookID) INNER JOIN tblItems ON tblItemsInBook.ItemsID = tblItems.ID

WHERE (((tblBook.ID)=[book id]))

ORDER BY tblBook.FileName, tblItems.DescpOfItems;

 

Will be using the Final Qry as a recordSource for a ListBox

All   “ID” values are Long Integer. 

I have not figured out how to make final qry result show all in first qry EXCEPT what is in 2nd qry.

Mark J

---

Mark J

Answer 1

Hi Mark,

If I understand you, you are looking to find a list of all available items that are not currently associated with a given Book.

This can be found by performing an outer join. The catch is, you need to filter the list on one side of the join.

A solution for this is to use a subquery.

In your case: you want to find a list of items already associated with a given book.

SELECT tblItemsInBook.ItemsID
FROM tblItemsInBook
WHERE (((tblItemsInBook.BookID)=[BookID]));

You can then perform an outer join by using the above select statement like it was a another table or query.

SELECT tblItems.ID, tblItems.DescpOfItems
FROM tblItems LEFT JOIN 

(SELECT tblItemsInBook.ItemsID
FROM tblItemsInBook
WHERE (((tblItemsInBook.BookID)=[BookID]))) As selExistingItems

ON tblItems.ID = selExistingItems.ItemsID
WHERE (((selExistingItems.ItemsID) Is Null));

I hope that makes sense and works for you.

Answer 2

William,

Thank you.    That is much easier to understand.

I was having some problems with getting the other Subquery to work.  

I was not able to find why a " = True" was added to the subquery, or if hidden characters were copied from the document  where I stored the example of the subquery or what some of the other error msgs that popped up referred to. 

I had not been able to tell if problem was in setting(s) for current database or settings in the table(s) or something else.

Mark J

---

Mark J

Answer 3

Hi William

I do not know how to fix this issue. please help.

I realized why something weird was happing when I got your code running. 

The number of Possible Items available to tie to a book kept getting fewer.

If I had 61 items before I added item to a book then afterwards I had 60 to chose from.

IT WAS MY FAULT, I WAS NOT AS CLEAR AS I SHOULD HAVE BEEN.

I left out the same item could be used for many books. Like many books by the same author.

I LEFT OUT that many of the Books could have the same Item tied to it.  

thank you,

Mark J

---

Mark J

Answer 4

This is a different situation.

We were working with a situation where a book could have one or more items.

This is called a one-to-many relationship.

What you are describing now is a book could have one or more items; and, an item could be related to one or more books.

This is called a many-to-many relationship.

Typically to represent this type of relationship requires a third table. This third table, sometimes called a junction table, contains pairs of keys from both other tables. In your case you will have a table that includes two fields: BookID and ItemID.

I suggest you read this: Guide to table relationships.

There is a nice description of the many-to-many relationship type.

From there you can investigate how to work with many-to-many relationships.

Good luck and I hope that helps

Answer 5

William, you are right it is a different situation. And I was NOT very clear. I am truly sorry for that. It seems I often either give too much info and confuse what the question is, or not enough info to get help. to avoid that, and try to use the correct terms. I read these books before I posted my question(s). SQL Primer and accelerated introduction to SQL basics, SQL hack tips & tools for digging into your data, Getting started with SQL a beginner's guide, Sam's teach yourself SQL in 24 hours SQL Queries for mere mortals All were good, most were geared to Oracle or Microsoft SQL server. or other SQL products, a few pointed out the differences between the products where their examples would work on and wouldn't work for Microsoft Access. Along with many of the MSDN sites about Access and SQL, yes their was a spot of "Microsoft Access SQL" I also studied. I am very new to using subqueries to affect the rows returned by the SQL statements used. you are right it is a many-to-many relationship. That is why I had from the start the table tblItemsInBooks. That table had one column that was for the BookID, one for ItemID, and to avoid a problem I had before with some queries one column was an Primary key for tblItemsInBooks. An AutoID number to make sure each record in tblItemsInBooks was considered as a unique record. I was trying to do several things. When I open the form based on the table tblBooks, I want to see along with the info in tblBooks, I also want to see what I have already tied to the book, and what I haven't tied to the book. 1. For a "new" book, that had a new AutoId number as the BookID, I wanted my list box to look at tblItems AND tblItemsInBooks and see no items were tied to the book, (using BookID and ItemID in tblBooksInItems) so list box had all (at this time 61 items and still growing) available items that could be tied to a book. 2. When I revisited a book that had several "items" already tied to the book (using tblItemsInBooks) I wanted the list box to only show the "items" I had not already tied to the book. This is long, but I hope this time I was much clearer as to what I had done and was trying to do. Sorry again for not being clear. I am trying hard to make sure I post ONLY the needed info, not more or less. Mark J

---

Mark J