If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query help
I have run a query, but it took me multiple steps to get the answer I wanted.
I was wondering if there was a way I could of gotten the answer in one simple query. Say you have a database of cataloged books, with all the book names etc in a table. Then in another table you have a list of different libraries, that have these books. Is there a way to find out which books are present in library A AND library B. The book must exist in both places for it to give me that book title. I would appreciate any ideas to simplify this query, which I am sure is VERY common. |
#2
|
|||
|
|||
Query help
You could help us to help you by giving the actual table and field names.
Also the SQL for the queries that you have so far. Open the queries in design view. Next go to View, SQL View and copy and past it here. Information on primary keys and relationships would be a nice touch too. If you are only looking for A or B, something like below might work. You may need to link the Books table. SELECT libraries.library FROM libraries WHERE libraries.Book="B" AND libraries.library IN (SELECT libraries.library FROM libraries WHERE libraries.Book="A") ORDER BY 1 ; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Bec_FS" wrote: I have run a query, but it took me multiple steps to get the answer I wanted. I was wondering if there was a way I could of gotten the answer in one simple query. Say you have a database of cataloged books, with all the book names etc in a table. Then in another table you have a list of different libraries, that have these books. Is there a way to find out which books are present in library A AND library B. The book must exist in both places for it to give me that book title. I would appreciate any ideas to simplify this query, which I am sure is VERY common. |
#3
|
|||
|
|||
Query help
I assume that you have the correct Table Structure to handle the
Many-to-Many relationship between entities Library and Book , i.e. you have a Link / Resolver Table besides the entity Tables. In this case, you can use an INNER JOIN to a SubQuery like: ======== SELECT B.BookID, B.Title FROM ( tblBook AS B INNER JOIN tblLibraryBook AS LB_Copy1 ON B.BookID = LB_Copy1.frg_BookID ) INNER JOIN ( SELECT LB_Copy2.frg_BookID FROM tblLibraryBook AS LB_Copy2 WHERE (LB_Copy2.frg_LibraryID = {IDForLibraryB}) ) AS BooksInLibraryB ON B.BookID = BooksInLibraryB.frg_BookID WHERE (LB_Copy1.frg_LibraryID = {IDForLibraryA}) ======== -- HTH Van T. Dinh MVP (Access) "Bec_FS" wrote in message ... I have run a query, but it took me multiple steps to get the answer I wanted. I was wondering if there was a way I could of gotten the answer in one simple query. Say you have a database of cataloged books, with all the book names etc in a table. Then in another table you have a list of different libraries, that have these books. Is there a way to find out which books are present in library A AND library B. The book must exist in both places for it to give me that book title. I would appreciate any ideas to simplify this query, which I am sure is VERY common. |
#4
|
|||
|
|||
Query help
Assuming you have a LibraryBooks table (or something like it) that contains
both the Library and the Book, then this is the easiest solution to date. SELECT L.Book From LibraryBooks L WHERE L.Library = "A" or L.Library = "B" GROUP BY L.Book HAVING COUNT(L.Library) = 2 HTH Dale "Bec_FS" wrote in message ... I have run a query, but it took me multiple steps to get the answer I wanted. I was wondering if there was a way I could of gotten the answer in one simple query. Say you have a database of cataloged books, with all the book names etc in a table. Then in another table you have a list of different libraries, that have these books. Is there a way to find out which books are present in library A AND library B. The book must exist in both places for it to give me that book title. I would appreciate any ideas to simplify this query, which I am sure is VERY common. |
Thread Tools | |
Display Modes | |
|
|