0

I have a simple query that shows books written by a particular author, and counts the total number of books held in stock. The result returned from the query shows five columns. BookID, AuthorID, AuthorName, BookName and the total books held in stock.

My problem is that if a particular book has no stock held, the book name is not displayed in the result. I want the bookname displayed with a zero shown.

i.e.

1 1 Lee Child Personal 5

1 2 Lee Child Make Me 4

1 3 Lee Child The Visitor 0

1 4 Lee child One Shot 8

The sql for the query is shown below:

SELECT tblSale.BookID, tblAuthor.AuthorID, tblAuthor.AuthorName,  
tblBook.BookName, Count(tblBook.BookName) AS [In Stock]
FROM (tblAuthor INNER JOIN tblBook ON tblAuthor.AuthorID = tblBook.AuthorID) 
INNER JOIN tblSale ON tblBook.BookID = tblSale.BookID
GROUP BY tblSale.BookID, tblAuthor.AuthorID, tblAuthor.AuthorName,   
tblBook.BookName, tblSale.BookInStock
HAVING (((tblAuthor.AuthorID)=[forms]![frmMain]![text12]) AND  
((tblSale.BookInStock)=Yes))
ORDER BY tblAuthor.AuthorName;

I'd be very grateful for a solution to this irritating problem! Many thanks...

Nev in Redwood
  • 15
  • 5

1 Answers1

0

It's had to say without more info but your query has (tblSale.BookInStock)=Yes)

Isn't that filtering out all the no s

Something like this (Just the Zero Stock Books) ?

SELECT tblSale.BookID, tblAuthor.AuthorID, tblAuthor.AuthorName,  
tblBook.BookName, Count(tblBook.BookName) AS [In Stock]
FROM (tblAuthor INNER JOIN tblBook ON tblAuthor.AuthorID = tblBook.AuthorID) 
INNER JOIN tblSale ON tblBook.BookID = tblSale.BookID
Where [In Stock] = 0    
GROUP BY tblSale.BookID, tblAuthor.AuthorID, tblAuthor.AuthorName,   
tblBook.BookName, tblSale.BookInStock
ORDER BY tblAuthor.AuthorName;

or (All Books Including Zero Stock)

SELECT tblSale.BookID, tblAuthor.AuthorID, tblAuthor.AuthorName,  
tblBook.BookName, Count(tblBook.BookName) AS [In Stock]
FROM (tblAuthor INNER JOIN tblBook ON tblAuthor.AuthorID = tblBook.AuthorID) 
INNER JOIN tblSale ON tblBook.BookID = tblSale.BookID
GROUP BY tblSale.BookID, tblAuthor.AuthorID, tblAuthor.AuthorName,   
tblBook.BookName, tblSale.BookInStock
ORDER BY tblAuthor.AuthorName;
Ahh ... It's a programming thi
  • 641
  • 1
  • 5
  • 12
  • Hi. Yes it is filtering the no's, and I know the way things stand, I won't get the result I'm looking for. But by posting what I'm working with, hopefully someone can suggest a change, or another way of doing things, to achieve what I am after, and that is to display all the book names, with a zero alongside the ones that have no stock.cheers... – Nev in Redwood Dec 31 '15 at 23:26
  • The first query has a problem with "in Stock" and is asking for a parameter value. The second query (All books including zero stock), is grouping on "book in stock" so I am getting two rows per book, one counting the books in stock, and one row counting the books sold. If we focus on just one book in the database, as an example, Lee Child's book Personal. I have sold plenty of copies, but have none currently in stock. I need to see one row for that particular book, that goes 1 1 Lee Child Personal 0 . If I have books in stock, I need to see the total books held instead of 0. – Nev in Redwood Jan 01 '16 at 02:16
  • Regarding my previous comment. I should have said Lee Child's book - The Visitor (not Personal). In case anyone is looking at the sample rows in my original post. – Nev in Redwood Jan 01 '16 at 02:25
  • What if you just remove the in Stock part of you having clause. – Ahh ... It's a programming thi Jan 01 '16 at 02:35
  • Can you post a copy of your database somewhere I can see the whole table layout – Ahh ... It's a programming thi Jan 01 '16 at 02:35