Query and intersection table problem

  • Thread starter Thread starter Fred Boer
  • Start date Start date
F

Fred Boer

I have a form which displays book information for my library database. The
form has command buttons to print spine labels and barcode labels. The
reports are based on a query. The query draws data from three tables:
Tbl_Library, Tbl_Author, and Tbl_BookAuthor, where Tbl_BookAuthor is a
intersection table.

I've run into a problem when I have a book with two authors. In this case,
the query contains two records for the same Book_ID, and, consequently, two
barcode labels are printed...

How can I restrict the printing to only one label? I suppose I'd want the
record which relates to the first author entered...
I've experimented with various joins, grouping, unique values, unique
records, but I'm stumped.
I've included table structures, SQL and Code below.

Thanks!
Fred Boer

Table structure:

Tbl_Library:

Book_ID
Title
CopyNumber
Numberofvolumes
VolumeNumber
Location_ID (Lookup to Tbl_Location)
etc...

Tbl_Author:

Author_ID
Author

Tbl_BookAuthor

Book_ID
Author_ID



Code to run report:

DoCmd.OpenReport "Lbl_DymoSpine", acViewNormal, , _
"[Book_ID] = " & Forms!Frm_LibraryDataEdit!txtBookID

SQL for query used by report:

SELECT Tbl_Library.Book_ID, [Tbl_Author].[AuthorLastName] & ", " &
[Tbl_Author].[AuthorFirstName] & " " & [Tbl_Author].[AuthorMiddleName] AS
Author, Tbl_Library.Title, Tbl_Library.CopyNumber,
Tbl_Library.NumberOfVolumes, Tbl_Library.VolumeNumber, Tbl_Library.Dewey,
Tbl_Location.Location
FROM Tbl_Location INNER JOIN (Tbl_Library INNER JOIN (Tbl_Author INNER JOIN
Tbl_BookAuthor ON Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON
Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID) ON Tbl_Location.Location_ID =
Tbl_Library.Location_ID;
 
Dear Fred:

To show only the first author in the query you must define what the
"order" is of the authors. Do you mean alphabetically first, or
something else.

You would use a correlated subquery to filter the Author to show only
the first one, whatever the definition of "first" is determined to be.

I've edited your query to use aliases for the tables in preparing
this. It's a lot for me to absorb using the long table names.

SELECT Lb.Book_ID,
Au.AuthorLastName & ", " & Au.AuthorFirstName
& " " & Au.AuthorMiddleName AS Author,
Lb.Title, Lb.CopyNumber, Lb.NumberOfVolumes, Lb.VolumeNumber,
Lb.Dewey, Lo.Location
FROM Tbl_Location Lo
INNER JOIN (Tbl_Library Lb
INNER JOIN (Tbl_Author Au
INNER JOIN Tbl_BookAuthor BA
ON Au.Author_ID = BA.Author_ID)
ON Lb.Book_ID = BA.Book_ID)
ON Lo.Location_ID = Lb.Location_ID
WHERE Au.AuthorLastName = (SELECT MIN(Au1.AuthorLastName)
FROM Tbl_BookAuthor BA1
INNER JOIN Tbl_Author Au1 ON Au1.Author_ID = BA1.Author_ID
WHERE BA1.Book_ID = Lb.Book_ID);

Having done this much, I see it is not perfect. If the two authors of
the book have the same last name (not that uncommon if brothers or
husband and wife co-author) you're still going to get both of them
this way. The problem is that "first" based on alphabetical by last
name really isn't sufficient, is it?

So, I'm going to hold off till you tell me what "first" means.

I have a form which displays book information for my library database. The
form has command buttons to print spine labels and barcode labels. The
reports are based on a query. The query draws data from three tables:
Tbl_Library, Tbl_Author, and Tbl_BookAuthor, where Tbl_BookAuthor is a
intersection table.

I've run into a problem when I have a book with two authors. In this case,
the query contains two records for the same Book_ID, and, consequently, two
barcode labels are printed...

How can I restrict the printing to only one label? I suppose I'd want the
record which relates to the first author entered...
I've experimented with various joins, grouping, unique values, unique
records, but I'm stumped.
I've included table structures, SQL and Code below.

Thanks!
Fred Boer

Table structure:

Tbl_Library:

Book_ID
Title
CopyNumber
Numberofvolumes
VolumeNumber
Location_ID (Lookup to Tbl_Location)
etc...

Tbl_Author:

Author_ID
Author

Tbl_BookAuthor

Book_ID
Author_ID



Code to run report:

DoCmd.OpenReport "Lbl_DymoSpine", acViewNormal, , _
"[Book_ID] = " & Forms!Frm_LibraryDataEdit!txtBookID

SQL for query used by report:

SELECT Tbl_Library.Book_ID, [Tbl_Author].[AuthorLastName] & ", " &
[Tbl_Author].[AuthorFirstName] & " " & [Tbl_Author].[AuthorMiddleName] AS
Author, Tbl_Library.Title, Tbl_Library.CopyNumber,
Tbl_Library.NumberOfVolumes, Tbl_Library.VolumeNumber, Tbl_Library.Dewey,
Tbl_Location.Location
FROM Tbl_Location INNER JOIN (Tbl_Library INNER JOIN (Tbl_Author INNER JOIN
Tbl_BookAuthor ON Tbl_Author.Author_ID = Tbl_BookAuthor.Author_ID) ON
Tbl_Library.Book_ID = Tbl_BookAuthor.Book_ID) ON Tbl_Location.Location_ID =
Tbl_Library.Location_ID;
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Dear Tom:

Thanks for your response! Sorry about the long table names... I've never
really tried using aliases.. perhaps I should! <g>

To tell you the truth, I'm not sure how to define "first" with respect to
Author, myself! This problem has surfaced since I re-did my faulty table
structure and moved
authors to a separate table. In the past, I've simply entered authors in a
single field. (i.e. "Ellison, Tom and Fred Boer"), and let the formatting of
the book's title page guide me (often one name is listed above or to the
left of another...). However, it is obvious that I was being completely
arbitrary in the past and that I am going to have to come up with something
more logical than that!

Could you be so kind as to check back on this thread in a day or so? As you
suggest, I need to decide what "first" means - and that means doing a little
research into proper library practices (Where *did* I put those library
school textbooks??). Or maybe some lurking librarian will straighten me out!

Many thanks!
Fred
 
Dear Fred:

Just what kind of a book would we author together? At least you got
it right, giving me top billing!

You should always try to keep a librarian around. You can never find
one when you need one. I know, just stand under the "Silence" sign
and make noise. One will come running!

I'll mark this thread "watch" and see what happens!

Dear Tom:

Thanks for your response! Sorry about the long table names... I've never
really tried using aliases.. perhaps I should! <g>

To tell you the truth, I'm not sure how to define "first" with respect to
Author, myself! This problem has surfaced since I re-did my faulty table
structure and moved
authors to a separate table. In the past, I've simply entered authors in a
single field. (i.e. "Ellison, Tom and Fred Boer"), and let the formatting of
the book's title page guide me (often one name is listed above or to the
left of another...). However, it is obvious that I was being completely
arbitrary in the past and that I am going to have to come up with something
more logical than that!

Could you be so kind as to check back on this thread in a day or so? As you
suggest, I need to decide what "first" means - and that means doing a little
research into proper library practices (Where *did* I put those library
school textbooks??). Or maybe some lurking librarian will straighten me out!

Many thanks!
Fred
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Dear Tom:
Just what kind of a book would we author together?
Click to expand...

How about "Chicken Soup for the Access Developer's Soul"... A collection of
short, touching essays. Possible essay titles: "How I learned to love "Me.",
or " Dot vs. Bang: Why can't they both just get along with each other?"

Or maybe "MVP's are from Mars and Newbies are from Venus". An involving
examination of how to bridge their communication gap. ( With a foreward by
John Vinson on "Ferreting out the problem when faced with no useful
communication from the newbie") An excerpt: Learn to look for the hidden
meaning when dealing with a newbie. When a newbie says, "I want to close the
database window", consider that what they *really* mean is "How do I hide
the Access application window?"...
At least you got
it right, giving me top billing!
Click to expand...

Of course!

Ok.. back to work... I tried your suggested SQL, (which, of course, worked
perfectly, thanks!). And, yes, it would have to be modified, as you suggest,
to cope with identical last names. Perhaps a concatenated expression
including first, middle and last names could be used. To be honest, your
query is a bit "over my head", but I'll be working my way through it as I
learning tool!

Now, as to what "first" means: it appears as if the accepted method of
dealing with two or more authors is to use the placement of names on the
title page. So, the topmost or leftmost author is used for shelf location
and spine labels. I'm still waiting for confirmation from a real, live
cataloguer (even among librarians, cataloguers are a breed apart, let me
tell you!!), but I'm pretty confident of this...

So... this leaves me utterly without a clue... I haven't the foggiest idea
how to begin to create a query/process that would manage this. It would have
to have something like the following steps:

1. Find all rows in the query with the current Book_ID.
2. In the case that there are two or more authors, identify which author was
added to the BookAuthor table first, and use that single row for the label.
I suppose if there were a date/time field in BookAuthor which was filled by
Now()... you might be able to get the earliest date/time, but I'm really
grasping at straws...

And in the end, all this might not be worth the trouble.. I mean, not *that*
many books have more than one author, and I guess I could simply throw away
the unnecessary labels... but...

Many thanks!
Fred
 
Dear Fred:

You win the prize! I've been replying in the NGs for years using the
"Dear Fred:" (or appropriately similar) salutation, and it is, I
suppose, a bit of a trademark to me. Not that anyone has noticed!

You're the first to be so polite as to respond back (at least that
I've noticed). So you win the prize. You're now entitled to go out
and by any present you wish (with your own money).

Now, unless you have time-stamped the rows as they were added, I do
not know of any reliable way to tell which Author was first. Such
things are usually done "by design" meaning the information was
designed in back when tables were designed. Some would use the
minimum of the ID if that is an autonumber, but I understood that's
not guaranteed to work in all cases.

I would be likely to add a boolean "first author" and default it by
the autonumber (risky, but at least a reasonable default). This has
the advantage of picking a First Author (whether it's right or not)
and giving them the alternative to fix it. At the same time, they can
then start getting it right for new titles starting as soon as that's
implemented. Seems like that would be better than putting the minimum
ID into the query so it cannot be fixed or done correctly in the
future.

Does this seem like a "best compromise" kind of solutions?

Dear Tom:


How about "Chicken Soup for the Access Developer's Soul"... A collection of
short, touching essays. Possible essay titles: "How I learned to love "Me.",
or " Dot vs. Bang: Why can't they both just get along with each other?"

Or maybe "MVP's are from Mars and Newbies are from Venus". An involving
examination of how to bridge their communication gap. ( With a foreward by
John Vinson on "Ferreting out the problem when faced with no useful
communication from the newbie") An excerpt: Learn to look for the hidden
meaning when dealing with a newbie. When a newbie says, "I want to close the
database window", consider that what they *really* mean is "How do I hide
the Access application window?"...


Of course!

Ok.. back to work... I tried your suggested SQL, (which, of course, worked
perfectly, thanks!). And, yes, it would have to be modified, as you suggest,
to cope with identical last names. Perhaps a concatenated expression
including first, middle and last names could be used. To be honest, your
query is a bit "over my head", but I'll be working my way through it as I
learning tool!

Now, as to what "first" means: it appears as if the accepted method of
dealing with two or more authors is to use the placement of names on the
title page. So, the topmost or leftmost author is used for shelf location
and spine labels. I'm still waiting for confirmation from a real, live
cataloguer (even among librarians, cataloguers are a breed apart, let me
tell you!!), but I'm pretty confident of this...

So... this leaves me utterly without a clue... I haven't the foggiest idea
how to begin to create a query/process that would manage this. It would have
to have something like the following steps:

1. Find all rows in the query with the current Book_ID.
2. In the case that there are two or more authors, identify which author was
added to the BookAuthor table first, and use that single row for the label.
I suppose if there were a date/time field in BookAuthor which was filled by
Now()... you might be able to get the earliest date/time, but I'm really
grasping at straws...

And in the end, all this might not be worth the trouble.. I mean, not *that*
many books have more than one author, and I guess I could simply throw away
the unnecessary labels... but...

Many thanks!
Fred
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Dear Tom:
You're the first to be so polite as to respond back (at least that
I've noticed). So you win the prize. You're now entitled to go out
and by any present you wish (with your own money).
Click to expand...

Whoo hoo! I'm a winner! I don't mind using my own money! Now I can get that
RAM upgrade I've wanted, RDRam is *so* expensive, and there are some Access
books I want, and.. uh.. wait a minute.. You *did* clear this with my wife,
didn't you? <g>

Thanks for your suggestions. They sound reasonable and doable to me.. My
database is actually quite small, and the number of multiple authored books
is small enough to allow for "after the fact" editing. I now have renewed
hope, and I'll begin work on this tomorrow!

Again, thanks so much!

I remain your humble and grateful servant... <g>

Fred
 
Back
Top