bibliography: maybe I can't do what I want to do?

[grammatim]

I'm about halfway through my first Access book, and it's all about how
to get data _out_ of Access nicely -- Forms and Reports and all that.

For my imaginary bibliography program, I'd like to have something like
a Form (like a Word Form, maybe) for putting data _into_ the database:
there would be a list of New Book, New Article, New Book Chapter,
etc., which would provide an entry-blank for everything that might
come up in that entry. But in the Author field, when I start typing
the author's name, it would offer me a completion from the list
already in the table for that field, etc. Similarly for publisher and
place. (These are independent variables: Oxford University Press in
Oxford vs. New York vs. New Delhi vs. Melbourne is significant.)

Also, one item can have, say, several authors -- how do I tell Access
that one Record can have between 1 and n entries in a single field? or
would a table need to have an indefinitely large number of fields for
2nd, 3rd, etc. etc. author? (Some hard-science papers these days can
allegedly have dozens of authors.)

And one book might also have editors, translators, ... So my one list
of People has to be callable several times in a single record.

And one edited volume has several chapters by at least as many authors
-- surely each such book doesn't have to be a table all its own?

What topics or keywords do I need to be learning about in order to try
to make this happen?

 

[John W. Vinson]

I'm about halfway through my first Access book, and it's all about how
to get data _out_ of Access nicely -- Forms and Reports and all that.

For my imaginary bibliography program, I'd like to have something like
a Form (like a Word Form, maybe) for putting data _into_ the database:
there would be a list of New Book, New Article, New Book Chapter,
etc., which would provide an entry-blank for everything that might
come up in that entry. But in the Author field, when I start typing
the author's name, it would offer me a completion from the list
already in the table for that field, etc. Similarly for publisher and
place. (These are independent variables: Oxford University Press in
Oxford vs. New York vs. New Delhi vs. Melbourne is significant.)

Also, one item can have, say, several authors -- how do I tell Access
that one Record can have between 1 and n entries in a single field? or
would a table need to have an indefinitely large number of fields for
2nd, 3rd, etc. etc. author? (Some hard-science papers these days can
allegedly have dozens of authors.)

And one book might also have editors, translators, ... So my one list
of People has to be callable several times in a single record.

And one edited volume has several chapters by at least as many authors
-- surely each such book doesn't have to be a table all its own?

What topics or keywords do I need to be learning about in order to try
to make this happen?

Normalization.

It really sounds like your book isn't all that appropriate for what you want
to do! Designing a database starts with the Tables, and with Normalization of
those tables, and with Relationships between those tables. Your book may not
really delve into these rather challenging topics very much.

I think I posted this to a question of yours a few days back but just in case:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Check out Crystal's and Allen's tutorials particularly, looking for
information about Tables, Relationships, and Normalization.

In a nutshell, you'll need at least the following tables:

Publications
PublicationID <Primary Key>
PublicationType (e.g. Book, Article, Chapter, Review, Poster, Blog Entry)
Title
ParentPublicationID <link to another record in Publications, so that you can
link a Chapter to its Book for example>

Authors
AuthorID <Primary Key>
LastName
FirstName
<other biographical info>

Authorship
PublicationID <link to Publications>
AuthorID <link to Authors>
Role <e.g. Author, Editor, Reviewer>
<any info about this author in this publication, e.g. sequential position in
the author list>

This structure will let you create a Publication which consists of other
Publications (e.g. a book containing multiple chapters, a journal issue
containing multiple articles); each Publication can have any arbitrary number
of records in the Authorship table listing all its authors.

 

[grammatim]

Normalization.

It really sounds like your book isn't all that appropriate for what you want
to do! Designing a database starts with the Tables, and with Normalizationof
those tables, and with Relationships between those tables. Your book may not
really delve into these rather challenging topics very much.

I think I posted this to a question of yours a few days back but just in case:

I did look at them all ... and most of them appeared to deal with
versions of Access not later than 2000.

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

Check out Crystal's and Allen's tutorials particularly, looking for
information about Tables, Relationships, and Normalization.

In a nutshell, you'll need at least the following tables:

Publications
  PublicationID <Primary Key>
  PublicationType (e.g. Book, Article, Chapter, Review, Poster, Blog Entry)
  Title
  ParentPublicationID <link to another record in Publications, so that you can
link a Chapter to its Book for example>

Authors
  AuthorID <Primary Key>
  LastName
  FirstName
  <other biographical info>

Authorship
  PublicationID <link to Publications>
  AuthorID  <link to Authors>
  Role <e.g. Author, Editor, Reviewer>
  <any info about this author in this publication, e.g. sequential position in
the author list>

This structure will let you create a Publication which consists of other
Publications (e.g. a book containing multiple chapters, a journal issue
containing multiple articles); each Publication can have any arbitrary number
of records in the Authorship table listing all its authors.

Looks good -- thanks for the guides.

 

[John W. Vinson]

I did look at them all ... and most of them appeared to deal with
versions of Access not later than 2000.

The basic principles of relational database design have not changed since
2000, or indeed since the early 1980's with the publication of Codd and Date's
articles on the subject. The tutorials will give you a guide TO THE CONCEPTS,
which are a lot more important than the detailed version-dependent mechanics
of how to implement those concepts!

 

[grammatim]

The basic principles of relational database design have not changed since
2000, or indeed since the early 1980's with the publication of Codd and Date's
articles on the subject. The tutorials will give you a guide TO THE CONCEPTS,
which are a lot more important than the detailed version-dependent mechanics
of how to implement those concepts!

I found "Normalization" in the index of Access 2003 Bible -- it's
discussed on a couple of pages at the beginning -- and it turns out
it's treated fully in For Dummies, but not under that name. For
Dummies is also quite good on Relationships.

I still don't see whether I can have more than one Author of an item
without having more than one Author Field.

 

[Douglas J. Steele]

I still don't see whether I can have more than one Author of an item
without having more than one Author Field.

You've got a many-to-many relationship (one author can have written many
books, one book could have been written by many authors). That means you
need three tables: Authors, Books and a intersection entity to resolve the
many-to-many relationship.

 

[John W. Vinson]

I still don't see whether I can have more than one Author of an item
without having more than one Author Field.

Take a look again at my suggestion.

"Fields are expensive, records are cheap". If you have multiple authors, you
don't add new fields for each author - you add new RECORDS in the Authorship
table. This would be done on a Form based on Publications, with a Subform
based on Authorship; on the subform you would have a combo box to let you pick
the author (with some VBA code in the combo box's Not In List event to let you
add new authors).

If you're working in the Table - DON'T. Tables are designed for data storage,
not for editing or updating or searching! Use Forms as the interface between
you and the data. This lets you easily handle multiple-table relationships
like this one.

Publications
PublicationID <Primary Key>
PublicationType (e.g. Book, Article, Chapter, Review, Poster, Blog Entry)
Title
ParentPublicationID <link to another record in Publications, so that you can
link a Chapter to its Book for example>

Authors
AuthorID <Primary Key>
LastName
FirstName
<other biographical info>

Authorship
PublicationID <link to Publications>
AuthorID <link to Authors>
Role <e.g. Author, Editor, Reviewer>
<any info about this author in this publication, e.g. sequential position in
the author list>

 

[Larry Linson]

I found "Normalization" in the index of Access 2003 Bible --
it's discussed on a couple of pages at the beginning -- and it
turns out it's treated fully in For Dummies, but not under
that name. For Dummies is also quite good on Relationships.

I'm sad to see that you take beginner-level books as being the authority on
normalization. No "Dummies" book I have ever seen is large enough to contain
more than a brief introduction to database design, including normalization.
Riordan's "Designing Effective Database Systems" has good coverage on the
subject... you can probably find it at half.com, or some other online
bookstore. Either version will serve your needs.

I still don't see whether I can have more than one Author
of an item without having more than one Author Field.

You can, in Access 2007. Traditionally, those are known as "multi-value"
fields, but because everyone knows they do not meet the test of relational
database design principals, Microsoft tends to call them "complex data".
If, however, you choose to use these, I assure you that you will be sorry,
later. They are only useful for an end-user working in datasheet view --
which in itself is not a good idea. If you are, as I understand,
contemplating a database application to create bibliographies, or a
bibliography, you will get some "surprises" when you try to make use of
these non-relational constructs... "lookup fields" and "subdatasheets" are
other ones that you'll regret having used, as time goes on, too.

Others have told you how this should be handled... a separate table with
Author information and a third (sometimes called "junction" or
"intersection") table that links publications and authors by identifying a
record in the Publications table and another in the Author table.

Larry Linson
Microsoft Office Access MVP

 

[grammatim]

 > I found "Normalization" in the index of Access 2003 Bible --
 > it's discussed on a couple of pages at the beginning -- and it
 > turns out it's treated fully in For Dummies, but not under
 > that name. For Dummies is also quite good on Relationships.

I'm sad to see that you take beginner-level books as being the authority on
normalization. No "Dummies" book I have ever seen is large enough to contain
more than a brief introduction to database design, including normalization..
Riordan's "Designing Effective Database Systems" has good coverage on the
subject... you can probably find it at half.com, or some other online
bookstore. Either version will serve your needs.

Thank you for your "helpful" response. I am not about to take a
graduate course in computer science in order to learn to make an
Access database.

 > I still don't see whether I can have more than one Author
 > of an item without having more than one Author Field.

You can, in Access 2007.  

The vast majority of comments in the Word newsgroup indicate that
moving to Office 2007 is not worth the few improvements weighted
against the incredibly poor redesign of the entire GUI.

Traditionally, those are known as "multi-value"
fields, but because everyone knows they do not meet the test of relational
database design principals, Microsoft tends to call them "complex data".
If, however, you choose to use these, I assure you that you will be sorry,
later. They are only useful for an end-user working in datasheet view --  
which in itself is not a good idea.  If you are, as I understand,
contemplating a database application to create bibliographies, or a
bibliography, you will get some "surprises" when you try to make use of
these non-relational constructs... "lookup fields" and "subdatasheets" are
other ones that you'll regret having used, as time goes on, too.

How delightful to learn what "everyone knows."

How nice to know that existing features of Access are crap.

Others have told you how this should be handled... a separate table with
Author information and a third (sometimes called "junction" or
"intersection") table that links publications and authors by identifying a
record in the Publications table and another in the Author table.

If "others have told me," why do you find it necessary to repeat it?

  Larry Linson
  Microsoft Office Access MVP

Is Linson the eViL twin of Vinson, who has been unfailingly courteous
and, as I see from oher threads as well, unfailingly helpful?

 

[A Nother]

Thank you for your "helpful" response. I am not about to take a
graduate course in computer science in order to learn to make an
Access database.

The vast majority of comments in the Word newsgroup indicate that
moving to Office 2007 is not worth the few improvements weighted
against the incredibly poor redesign of the entire GUI.

How delightful to learn what "everyone knows."

How nice to know that existing features of Access are crap.

If "others have told me," why do you find it necessary to repeat it?

Larry Linson
Microsoft Office Access MVP

Is Linson the eViL twin of Vinson, who has been unfailingly courteous
and, as I see from oher threads as well, unfailingly helpful?
----------------------------------------------------------------------

The advice Larry gave you is very valuable and you would do well to follow
it. The things he told you to avoid should be avoided. Think Homer Simpson,
naked, holding his genitals and running, screaming like a little girl, for
the hills.

Weird database designs will always bite you in the ass. Database design
involves judgements which are not always clear or universally accepted.

KISS (Keep It Simple, Stupid).

As for your original enquiry, did you look at

http://office.microsoft.com/en-us/templates/TC010186431033.aspx?CategoryID=CT102144001033

which appears to have a subform for multiple authors??