3 tables display data from 1 depending on value in another

attman

Registered User.
Local time
Today, 13:29
Joined
Jun 25, 2013
Messages
29
I'm building a database to record which books Pupils at my school have read.
I have 3 Tables:
Pupils - ID, Forename, Surname, ID
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut

I'd like to create a query that shows the BookTitle of the latest DateOut.

Can someone help me out here?
 
That is brilliant thank you very much! I (honestly) will enjoy going thru that later.

However, I have had a go and I am just SO lost! I cannot get my head around how to do this - I guess because of the 3 tables?

I believe in working things out myself - is there any kind soul that would help me on this syntax?
 
Last edited:
you only have to join 2: Books and Read. You were not asked about the Pupil ;)
 
Ultimately I want to display what BookTitle each child last added to the Read table so it must link to Pupil...

I can create a query that shows what time each Pupil last took out a book but I cant then get it to show what title that is and I did that using the Max and Group By options...
 
You have a Table called Pupils. It has two fields the same. "ID"

You are not allowed to duplicate a field in the same Table..
 
I can create a query that shows what time each Pupil last took out a book but I cant then get it to show what title that is and I did that using the Max and Group By options...
Great!
Now save this query, and join it to read tbl, on DateOut and PupilID, and Join Book tbl to Read tbl on BookID.

you now can display the book title each Pupil has read last.

read about inner join: http://www.w3schools.com/sql/sql_join.asp
You can also use the Access query builder: http://www.techonthenet.com/access/queries/joins1.php

Good luck!
 
The SQL generated from my query using READ & PUPILS is as follows:

SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut
FROM (Read) INNER JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id;


This shows 4 records (correct) and display ID and DateOut

If I then include Read.BookID like so:

SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut, Read BookID
FROM (Read) INNER JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Read.BookID;


It shows all records in my Read table.

How do I join a query to my Read table ? In my setup Pupil.ID has a relationship with Read.ID and Books.BookID has a relationship with Read.BookID
 
I followed the Access Query Builder link on 3 tables and I'm nearly there I think. The code below still displays all the entries in the Read table rather than just the biggest(latest) date). It does show the Book name now...

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

Users who are viewing this thread

Back
Top Bottom