none
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 RRS feed

  • 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

    Saturday, December 15, 2018 10:40 AM

Answers

  • 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.

    • Marked as answer by PuzzledByWord Wednesday, December 19, 2018 10:15 AM
    Monday, December 17, 2018 6:56 PM

All replies

  • 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.

    • Marked as answer by PuzzledByWord Wednesday, December 19, 2018 10:15 AM
    Monday, December 17, 2018 6:56 PM
  • 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

    Wednesday, December 19, 2018 10:14 AM
  • 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

    Thursday, March 14, 2019 5:24 AM
  • 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

    Thursday, March 14, 2019 2:39 PM
  • 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

    Friday, March 15, 2019 1:09 AM