I’ve writen this up more fully at https://trixiesgems.com/2022/10/01/database-about-books-in-a-library/… and you can fetch a small .odb from there that has the database I’m talking about in the following.
I have a database tracking books in a library. Each copy of every book has a unique BookID. One table gives the Author, etc, associated with each BookID. A second table (“BookWhere”) has a compound primary key… The BookID used in the first table, with which a relationship is in place, and a date. The date is for when that book was seen on that shelf. (New records can only be added to “BookWhere” once a day… to prevent it from having non-unique values in the fields making up the primary key.)
“BookWhere”, also has a field to say what shelf the book is on.
BookWhere can have several entries for any particular book. This will happen when where the book is shelved changed. Instead of finding the book’s BookWhere entry, and changing the value in the “Shelf” field, I want to keep the history of the different places a book has been over the years.
HELP SOUGHT: How do I put together a query that will generate a table with columns for…
The book ID
The book title
What shelf the book was on to generate the record behind the line in the query result
When the book was seen on that shelf
If I’ve explained it in a way that gets my question across, you will see that there may be MULTIPLE entries for one book in the table produced by the query, reflecting the multiple records like them in BookWhere. They will share a BookID, but the “When” will be different for each.
Remember- you can fetch the database in question from the link above.