Searching on author for books with multiple authors

[Fred Boer]

I need help designing a search process which locates library books that have
multiple authors. Recently, I reworked my table structures and this has
thrown my current search process out of whack.. (but I *know* this is all
for the good in the end! <g>).

A book can have two or more authors. One author (for shelving purposes) is
designated the "Primary" author. I (with thanks to Tom Ellison for his kind
help!) have worked out the processes involved in assigning primary/secondary
authorship. (See the table structures below...). The search process I am
working on uses a combobox to locate an author and a subform lists all the
books by that author.

What I am having trouble with, is that I want - somehow - to include the
call number used to shelve the book (and possibly list other authors....)

For example: I have a book "Rick Hansen: man in motion". Primary author is
Rick Hansen. Secondary author is Jim Taylor. The call number for this book
is 617.428 / HAN. If a student looks up the book using the secondary author,
I need to indicate that the call number is actually derived from the primary
author's last name. So I need to display the actual call number. My subform
lists the "book" information, but I don't know how to connect the "call
number" information to each book. I assume that this should be a calculated
field...

I've been floundering about for a while, and would love to have any
suggestions of how to manage this...


Many thanks!
Fred Boer


Table Structures:

Tbl_Library:

Book_ID
Title
Format
Location
DeweyNumber
CopyNumber
Etc...

Tbl_Author

Author_ID
AuthorLastName
AuthorMiddleName
AuthorFirstName

Tbl_BookAuthor

Book_ID
Author_ID
PrimaryAuthor (Yes/No)

 

[Fred Boer]

Uh.. never mind! Using the subform wizard, I added a second subform based on
a separate query, and, although I must confess I'm not *quite* sure how it
does it, it seems to be working just fine! <g>

Fred