SQL Query for Multiple Fields

  • Thread starter Thread starter George B.
  • Start date Start date
G

George B.

I have a library catalog database where the user needs to
have a result pulled from separate fields. A user would
search on title and author. The trick is 'author' has to
be searched on the author, coauthor, and corporate author
fields. Similarly for the title, 'title' would include
searching title, subtitle, and corporate title fields.
Put another way, I need syntax for what the user would
see as a simple search on author and title, but would
search all those other fields underneath. For example
the 'author' would be John Doe and the 'subject' would be
trains. Any help is greatly appreciated. Thank you!
 
I have a library catalog database where the user needs to
have a result pulled from separate fields. A user would
search on title and author. The trick is 'author' has to
be searched on the author, coauthor, and corporate author
fields. Similarly for the title, 'title' would include
searching title, subtitle, and corporate title fields.
Put another way, I need syntax for what the user would
see as a simple search on author and title, but would
search all those other fields underneath. For example
the 'author' would be John Doe and the 'subject' would be
trains. Any help is greatly appreciated. Thank you!
Click to expand...

Use OR logic in your query. This will get awfully ugly using the query
design grid; it's actually easier in SQL. Try constructing a Query in
the grid searching only the AUTHOR and TITLE fields; go into SQL view
and edit the WHERE clause to something like

WHERE ([Author] = [Enter author name:] OR [Coauthor] = [Enter author
name:] OR [Corporate Author] = [Enter author name:])
AND ([Title] LIKE "*" & [Enter word in title:] OR [Subtitle] LIKE "*"
& [Enter word in title:] OR [Corporate title] LIKE "*" & [Enter word
in title:])

Note the parentheses - it's one set of criteria for Author logically
AND'd with a set of criteria for Titles.

You might want to consider - especially for the Author field -
explicitly recognizing that there is a many to many relationship
between titles and authors. A given book might have FOUR authors - or
ten. (Some scientific journal articles have over a hundred!) Consider
having a table of Authors, and an Authorship table with the book ID
and the author ID, and perhaps an AuthorType field and/or an
AuthorNumber field indicating who is first, second, and third author.
 
Back
Top