Max not showing only the biggest date

[attman]

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, Book, Author, Level, Genre
Read - ID, BookID, DateOut

On another thread in the forum 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.

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"));

 

[namliam]

Why would you open a new thread instead of continuing your existing one?
The person(s) that helped you there are already "into" your problem, and should be able to help you

Why do you only want to see the last book? Isnt it intresting to see all books read by a pupil?
The reason for your problem is that you are using "Right join" instead of "Inner join", I suggest you research the difference between Left, Right and Inner join a bit and/or try to change the query to suite your needs using the designer.


Please
1) Format your SQL when you post on the forum
2) Use the code tags when you post any code on the forum (see my signature for a link)

 

[attman]

I was in a quandry because it looked like my original post had changed substantially and as I hadnt had a further response I assumed that people thought it was solved - book title was being displayed (I apologise).

With Inner join I couldnt get the book title. Believe me Ive tried left, right and inner!

I want to see the last book read as thats the book the child is currently reading. I then want to test the dateout to see that if the child hasnt changed the book in the last week.

 

[namliam]

Ah I see what you are getting at now...

Let me keep it simple, you want to make a query to get the max date read per pupil first.
i.e.

Select ID, max(dateout) BeingRead
from read
group by ID

Now save the query as qryBeingRead

now take your final result and adjust it

SELECT Pupils.ID, qrybeingread.beingread, Books.Book
FROM       Books
Inner JOIN Read         ON Books.BookID = Read.BookID
inner join qrybeingread on read.id = qrybeingread.id
                       and read.beingread = read.dateout
inner JOIN Pupils       ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));

you can even add something like

where qrybeingread.beingread < date - 7

To select all that are older than a week

 

[CJ_London]

duplicate thread here

http://www.accessforums.net/queries/max-not-showing-only-biggest-date-52383.html#post275508

please read this link

http://www.excelguru.ca/content.php?184

 

[attman]

Im obviously missing something. It looks like this has to be done with 2 queries? I am not an SQL programmer so this is my problem.

I took the first lot of code and pasted it in and I get "Syntax error (missing operator) in query expression 'max(dateout) BeingRead" - I dont understand where BeingRead comes from...

If I try to make a 2nd query with your next lot of code I get "Syntax error (missing operator) in query expression 'Books.Bookid = Read.BookID inner join qrybeingread on read.id = qrybeingread.i' "- maybe the 1st query has to be present before this one is created?

 

[namliam]

It can be done with only one query, however if you are not "into sql" using subselect queries really messes up your head. Hence me trying to keep it simple by splitting it into two query / query objects

syntax error

Too much oracle on the brain, missing an "AS"

Select ID, max(dateout) [B][U]AS[/U][/B] BeingRead
from read
group by ID

I dont understand where BeingRead comes from

BeingRead is an alias for the max function, much like you have the default alias of MaxOfDateOut in your query.

maybe the 1st query has to be present before this one is created?

Since we are saving the first query as qryBeingRead, yes that needs to exist before it can be used in the second query.

 

[attman]

Thank you so much for your ongoing help but I'm getting Syntax error on the 2nd lot of code too... strange that the last character (d) is missing in the message? (thats not a typo)

 

[namliam]

Could be access's overneedy brackets thing... dont see any other problem with the query...

Lets try another way.

Take the query you already have, add the qryBeingRead as a table to the query.
Join the ID of the query and Read table
As well as the beingread from the query with the dateout from the read table.

That should work

 

[attman]

Im afraid not - is there a possibility of uploading the DB anywhere or is that a request too far?

 

[attman]

Attached is the database Im trying to get my query to work with. The Pupil table is held in a file called PACRdata which will need to be linked.

Thanks to all..

 

[attman]

Is there anyone that can help me on this? I feel its close

 

[namliam]

Try this query:

SELECT Pupils.Forename, Pupils.Surname, Books.Book, Read.DateOut
FROM (Books 
INNER JOIN ([Read] 
INNER JOIN qryBeingread ON (qryBeingread.BeingRead = Read.DateOut) 
                       AND (Read.ID = qryBeingread.ID)) 
                        ON Books.BookID = Read.BookID) 
INNER JOIN Pupils ON Read.ID = Pupils.Id;

 

[attman]

Im sorry I can only click the Thanks button once! Brilliant, fantastic, Thank you!

Now comes the task of understanding the code.

 

[namliam]

The magic is quite visable in design view, simply switch to it and find out the "magic"

Coincidentaly check out how close my earlier attempt was...

SELECT Pupils.ID, qrybeingread.beingread, Books.Book
FROM       Books
Inner JOIN Read         ON Books.BookID = Read.BookID
inner join qrybeingread on read.id = qrybeingread.id
                       and read.beingread = read.dateout
inner JOIN Pupils       ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));

Not sure why I left the group by, which will be part of the problem
Also the joins are slightly different, and some brackets for access to work with... but basicaly its the same.