Max not showing only the biggest date

[selgarattman]

I'm building a database to record which books Pupils at my school have read.


I have 3 Tables with relationships between Pupils to Read and Books to Read:

Pupils – ID, Forename, Surname
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut

Through a lot of reading I got to the position of the code below. However this code does not select only the read book with the biggest(latest) date, the Max, it displays all Read books for all pupils.

SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut Books.Book
FROM (Books) RIGHT JOIN (Read) ON Books.BookID = Read.BookID) RIGHT JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));

Can someone tell me why?

[CJ_London]

It is not clear from your description what you are trying to achieve.

'However this code does not select only the read book with the biggest(latest) date' are you looking for the last book read by each pupil?

Your query refers to Books.Book, do you mean Book.BookTitle?

Also, it does not look like you need the pupils table in your query since you can just select the read.ID instead of Pupil.ID which has the same value

On the basis you want the latest book read by each pupil the code would be

SELECT Read.ID, BookTitle
FROM Read INNER JOIN Pupils ON Read.ID=Pupils.ID
WHERE DateOut=(SELECT Max(DateOut) FROM Read AS T WHERE ID=Read.ID)

[selgarattman]

I apologise but I wrote my table description wrong - Books.BookTitle IS Books.Book.

Ultimately I want to list out the last Book.Book (the book name) read by a pupil.

Pupil links to the Read table with ID, Read links to the Books table with BookID. I'm not an SQL person but in your code I got a Syntax error on the From (On is highlighted when I try to run). I think the difficulty here is that I'm using 3 tables.

[CJ_London]

can you post the sql you are actually using - other than the book/booktitle issue I can't see anything wrong with the code

[selgarattman]

The code i'm actually using is in my initial post. Originally I had a problem where I couldnt get Books.book to display (I could get an ID and last DateOut) and the Right Join I think solves that. Whats happening now though is that basically all records in the Read table are listed, not just the Max dateout.

[CJ_London]

Sorry, I'm not making myself clear - your code does not do what you want it to do, I provided a solution. You say that solution generates a syntax error - I need to see that code to see if I can spot why you are getting the error

[orange]

Your setup might be clearer to others with a few changes in naming convention and a picture of tables and relationships. ID is a poor identifier for each and every table.

My interpretation of this

Pupils – ID, Forename, Surname
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut

is

tblPupils
PupilId PK
PupilForename
PupilSurname

tblBooks
BookID PK
BookTitle
Author
Level
Genre

jncBooksReadByPupil
BooksReadID PK
PupilID FK These 2 fields(green) would be used to create a unique composite index (to prevent duplicates)
BookID FK
DateOut <--- The meaning and use of this field is unclear (could be DateRead, but if the same Pupil reads the same book multiple times, then DateOut would have to be part of the unique composite index)


tblPupils------>jncBooksReadByPupil<---tblBooks

Good luck with your project.

[selgarattman]

Sorry, I'm not making myself clear - your code does not do what you want it to do, I provided a solution. You say that solution generates a syntax error - I need to see that code to see if I can spot why you are getting the error

SELECT Read.ID, Books.book
FROM Read INNER JOIN Pupils ON Read.ID=Pupils.ID
WHERE DateOut=(SELECT Max(DateOut) FROM Read AS T WHERE ID=Read.ID)

[CJ_London]

duplicate thread here

http://www.access-programmers.co.uk/...=1#post1427483

good luck with your project

[selgarattman]

Your setup might be clearer to others with a few changes in naming convention and a picture of tables and relationships. ID is a poor identifier for each and every table.

My interpretation of this

Pupils – ID, Forename, Surname
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut

is

tblPupils
PupilId PK
PupilForename
PupilSurname

tblBooks
BookID PK
BookTitle
Author
Level
Genre

jncBooksReadByPupil
BooksReadID PK
PupilID FK These 2 fields(green) would be used to create a unique composite index (to prevent duplicates)
BookID FK
DateOut <--- The meaning and use of this field is unclear (could be DateRead, but if the same Pupil reads the same book multiple times, then DateOut would have to be part of the unique composite index)


tblPupils------>jncBooksReadByPupil<---tblBooks

Good luck with your project.

A child will only read a book once with this system which is fine, so DateOut is the date they took the book from the library.

Bad bad bad programning! Yes my naming conventions arent great, sorry.

ID is a unique identifier for the pupil, BookID is a unique identifier for the book. When a child takes a book from the library they dbl-click in a list window of available books which creates a record in the read table point to both the pupil and the book indicating its "out" of the library (and in fact is the book that they are currently reading). The Read table does NOT have a unique identifier. The Read table has 3 fields, ID (Pupil), BookID (Book) and DateOut.

[orange]

In relational database, every table should have a unique identifier (Primary key) to uniquely identify each and every record in the table. The Primary key may be a combination of fields such that that combo uniquely identifies each record in the table.