Combining Access generated SQL code

[sts023]

Hi guys….

I’m using Access 2003 to develop a Book lending system for a local Charity.
I’ve been using Access for about six months now, so I guess the “newbie” tag is still applicable!

Relevant to my question, I have the following Tables and Fields
Book Table – Book Id
Loan Table – Book Id, Borrower Id, Date Borrowed, Date Returned

I’m trying to develop SQL to enable me to determine the Books which a Borrower has already borrowed, and the books which are currently out on loan (to anyone). If I can then combine these lists into one query, I can generate a list of all Books which are currently on loan, or have previously been read by this Borrower. If I then run this as an “exclusion” query against the Book Table, I have a list of available Books.

I was hoping to generate Queries, then use the SQL view to get at and manipulate (slightly) the “specific” SQL to make it “general” SQL by using VBA to modify the Borrower Id.

In Access I have got the queries working for a specific Borrower id (71).

My problem is converting the working “Final” query to native SQL, because Access uses the name of the Union Query in the “Final” query.

I was hoping to replace the name of the Union Query with the actual SQL of the Union Query, but when I do that, I get a message “Syntax error in JOIN operation”.

On pressing OK, the first UNION operand is highlighted.

I’ve tried very hard to ensure I’ve got the “Union query name replacement” code correct (see below), but is what I’m trying to do illegal / immoral / just plain old silly?

The failing SQL is the final code below. Can anyone out there help me?

UNION query (“Combined subqueries for 71”)
(SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Date Returned] Is Null)
UNION
(SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Borrower Number]=71)
ORDER BY Loan.[Library Number];

UNION query as a single bracketed clause without the ORDER
((SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Date Returned] Is Null)
UNION
(SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Borrower Number]=71))

Access generated FINAL query with Union query exclusions
SELECT Book.[Book No], Book.[Library Number], Book.Title
FROM Book
LEFT JOIN [Combined subqueries for 71] ON Book.[Library Number] = [Combined subqueries for 71].[Library Number]
WHERE ((([Combined subqueries for 71].[Library Number]) Is Null));

Simplified Access generated BOOK query with union query exclusions
SELECT Book.[Book No], Book.[Library Number], Book.Title
FROM Book
LEFT JOIN [Combined subqueries for 71] ON Book.[Library Number] = [Combined subqueries for 71].[Library Number]
WHERE [Combined subqueries for 71].[Library Number] Is Null;

Simplified BOOK query with union query inserted
SELECT Book.[Book No], Book.[Library Number], Book.Title
FROM Book
LEFT JOIN ((SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Date Returned] Is Null)
UNION
(SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Borrower Number]=71))
ON Book.[Library Number] = ((SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Date Returned] Is Null)
UNION
(SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Borrower Number]=71)).[Library Number]
WHERE ((SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Date Returned] Is Null)
UNION
(SELECT DISTINCT Loan.[Library Number] FROM Loan
WHERE Loan.[Borrower Number]=71)).[Library Number] Is Null;

 

[jdraw]

Steve,

Just a few questions to help familiarize with your set up.
You say

I have the following Tables and Fields
Book Table – Book Id
Loan Table – Book Id, Borrower Id, Date Borrowed, Date Returned

and mention BorrowerId. Could you show us a jpg of your Tables and Relationships?

Have you seen the free data models at http://www.databaseanswers.org ?

Specifically this one, which may be overkill, but may help with your tables.
http://www.databaseanswers.org/data_models/libraries_and_books/index.htm

I just see, what seems to be, too many UNION queries.

 

[sts023]

Hi jdraw - How do I get jpeg diagrams 1) out of Access, and 2) on to this site?
Meanwhile, I'll have a look at the site you recommended....

Steve

 

[jdraw]

On the Quick Reply Menu, Select Go Advanced.
Then go to Manage Attachments and follow the prompts.

As for saving a jpg:

If you have a graphic/photo utility ( if not you can get free irfanview from internet),
go to your Access Tools --> Relationships. Do shift PrintScreen to copy the current window/screen to clipboard; then open your photo utility, open a new image/file and Paste the clipboard image; save the image file as jpg

 

[sts023]

I decided I was going about this whole thing the wrong way - the generated SQL just looked SO unwieldy.
Anyway, Ii had another go, and now, having got some expert help, I've got some much simpler code which WORKS.
I'm therefore abandoning this thread, but not without offering my sincerest thanks to those who replied, or thought about replying.

If anyone's interested, the replacement code is

SELECT Book.[Book No], Book.[Library Number], Book.Title, Author.Forenames, Author.Surname
 
FROM Author INNER JOIN Book ON Author.pkAuthor = Book.fkAuthor
 
WHERE Book.[Library Number] Not In
                (SELECT DISTINCT Loan.[Library Number] FROM Loan 
                 WHERE Loan.[Date Returned] Is Null)
                And
                Book.[Library Number] Not In
                (SELECT DISTINCT Loan.[Library Number] FROM Loan
                 WHERE Loan.[Borrower Number]=71)
ORDER BY Book.[Book No];