Book Database

[meekychunky2004]

Book database query

hello

I am building a book rental database.

A typical order is: 10 French/ junior/ fiction books and 4 German/ adult/ non-fiction books.

When the order is entered I want to be able generate a list that consists of 14 appropriate book titles with all the details. How can i do this?

Thanks

 

[neileg]

+++Out of cheese error+++
+++Redo from start+++

Or if you're not a Terry Pratchet fan, there's insufficient information in your question to give a meaningful answer. Your question reads like you want the database to suggest 14 titles simply from the limited information the user inputs. That doesn't sound right, though.

Have another go at explaining what you want.

 

[meekychunky2004]

Thanks, and yes you're right, I do want to suggest book titles from limited information.

The client places a loan order for 10 French/ fiction/ junior books - it is then up to the system to select a list of books that the client has not had before. The clients being schools who loan for their own library.

Does that make things clearer??

+++Out of cheese error+++
+++Redo from start+++

Or if you're not a Terry Pratchet fan, there's insufficient information in your question to give a meaningful answer. Your question reads like you want the database to suggest 14 titles simply from the limited information the user inputs. That doesn't sound right, though.

Have another go at explaining what you want.

 

[neileg]

OK, understood. Trouble is, I don't know where you are starting from.

I assume you have a table of titles and a table of issues. So, you would need to have a query from the titles table returning those books that match the criterion, viz. French/ junior/ fiction.

Then a query that returns the books that match the criterion that your client has already borrowed from your issues table. Join these queries with a left join from titles to issues, and where the issues side of the join is null, you know the client has not had these ones.

You could make this a Top 10 query and this would return ten books in the right category that the client had not already borrowed. Note: you would probably have to build the Top * query in SQL so that you could use the number of books as a variable.

Does that help?

 

[meekychunky2004]

Hi, thanks for you help!

Im sooooo stuck with this

Please see attached RD. Im not sure how to go about doing this at all, Im not even sure if I have the right structure.

I need to be able to select a group of books from the given criteria, then store this information in past orders (or should i just keep the orders table and get rid of the past & current orders tables?).

This is what is needed:
1 - Order is entered (language/ type/ readership/ quantity)
2 - Order generated should not contain any previous titles (but what about new customers with no previous orders)
3 - Once order is generated, user goes to shelves and picks books
4 - If book is missing need to untick "Available" field and regenerate order for a replacement book title
5 - Once order is fulfilled - need to save/ store somewhere

Hope that makes sense

 

[neileg]

Try this.

I don't think you need past and current loans.
Author and publisher details should be separate tables.
If LanguageCode is unique, you don't need another ID.
You don't need OrderlineID in tblOrders.
You don't need Available in tblBooks. If the relevant DateReturned in tblLoans is null, you know the book is out.
A Fulfilled y/n on the order line should suffice.