Newbie questions

[Mart]

I have minimal knowledge of Access database design, but have fiddled around
with a few in so far as I've added fields and data and constructed a few
queries and reports.

My questions related to a database of books that I want to eventually want
to be able to be queried on the web:

1. Many books start with "A" or "The".
How can/do I place these words at the end of the book title, preceded by a
", " when a web user searches for a book title. At what point is this task
actually carried out? Along the same lines, how could I see an alphabetized
list of book titles in the database which ignores the "A" and "The", eg. "The
Lion, the Witch and the Wardrobe" would appear under "L".

2. Many books have more than one author.
Short of having fields like "author1", "author2" etc. is there a slightly
less cumbersome way of listing more than one author in a table?

3. The database will consist of three tables: books, authors and
illustrators. Much of the information in the books table will be reproduced
in either the author or illustrator tables. How do I get around having to
enter the same data into three tables (I suspect the relational thing comes
into play here )

Thanks very much.

Mart

 

[Keith Wilby]

I have minimal knowledge of Access database design, but have fiddled around
with a few in so far as I've added fields and data and constructed a few
queries and reports.

My questions related to a database of books that I want to eventually want
to be able to be queried on the web:

I think there might be an issue there but I'll let someone else handle it.

1. Many books start with "A" or "The".
How can/do I place these words at the end of the book title, preceded by a
", " when a web user searches for a book title. At what point is this task
actually carried out? Along the same lines, how could I see an
alphabetized
list of book titles in the database which ignores the "A" and "The", eg.
"The
Lion, the Witch and the Wardrobe" would appear under "L".

I would suggest storing the data in the format you want the user to see, ie
"Lion, the Witch and the Wardrobe, The"

2. Many books have more than one author.
Short of having fields like "author1", "author2" etc. is there a slightly
less cumbersome way of listing more than one author in a table?

3. The database will consist of three tables: books, authors and
illustrators. Much of the information in the books table will be
reproduced
in either the author or illustrator tables. How do I get around having to
enter the same data into three tables (I suspect the relational thing
comes
into play here )

It does indeed. You need to think about entities. The book is one entity,
its author(s) is another and its illustrator(s) another. Therefore you need
three tables, one for each entity. Each book can have one or more author so
there is a one-to-many relationship, as there also is for illustrator(s).
Simplified, your schema might look like this:

tblBook
ID
Title
Publisher
Edition

tblAuthor
ID
BookID
AuthorName

tblIllustrator
ID
BookID
IllustratorName

There will be a 1:M relationship between tblBook.ID and tblAuthor.BookID and
another between tblBook.ID and tblIllustrator.BookID

That OK?

Keith.
www.keithwilby.com

 

[Mart]

Hello Keith

Many thanks.

Interesting that you suggest actually storing the data in the format that
will ultimately be seen. I thought that this might be the route to go, but
suspected that there may a "relatively" easy way of shifting either an "A" or
"The" to the end with some hard code. Nevertheless, your suggestion is just
as simple.

About the tables relations - I understand exactly what you're saying, but
how is this implemented in Access exactly? Is it a case of simply setting up
of the three tables with their fields and then establishing the relationship
between the related fields across the various tables? And, in which table
would I enter, for example, the author(s) details, so that I only have to do
it once, and that what I've entered is available in all tables?

Apologies for the entry level questioning, but I'd hate to start something
like this only to get it wrong

Mart

 

[Keith Wilby]

Hello Keith

Many thanks.

Interesting that you suggest actually storing the data in the format that
will ultimately be seen. I thought that this might be the route to go, but
suspected that there may a "relatively" easy way of shifting either an "A"
or
"The" to the end with some hard code. Nevertheless, your suggestion is
just
as simple.

You could code for it but it depends on whether you'd ever want the title
displayed "normally".

About the tables relations - I understand exactly what you're saying, but
how is this implemented in Access exactly? Is it a case of simply setting
up
of the three tables with their fields and then establishing the
relationship
between the related fields across the various tables?

Yes, you do all this in the "realtionships" window.

And, in which table
would I enter, for example, the author(s) details, so that I only have to
do
it once, and that what I've entered is available in all tables?

Actually, having thought a little more, the relationship between book and
author is many to many - a book can have many authors and an author can
write many books - so this is a little more complex. I'm just about to
leave for the day so if no-one jumps in I'll come back to it tomorrow.

 

[Mart]

Thanks Keith. Very helpful.

You could code for it but it depends on whether you'd ever want the title
displayed "normally".


Yes, you do all this in the "realtionships" window.


Actually, having thought a little more, the relationship between book and
author is many to many - a book can have many authors and an author can
write many books - so this is a little more complex. I'm just about to
leave for the day so if no-one jumps in I'll come back to it tomorrow.

 

[tina]

as Keith said, you have a many-to-many relationship between books and
authors, and also a many-to-many relationship between books and
illustrators. recommend the following structure, as

tblBooks
BookID (primary key)
Title
Publisher
CopyrightDate
<any other fields that describe a book, only>

tblAuthorsIllustrators
PersonID (pk)
FirstName
MiddleInitial
LastName
<any other fields that describe an author or illustrator, only>
some folks are talented enough to do both - sometimes illustrating their own
books, and sometimes writing a book here, and illustrating somebody else's
book there; using one table eliminates duplicate data issues. if you're
tracking specific data about authors that you don't track for illustrators -
and/or vice versa - then put author-specific fields into a child table, and
illustrator-specific fields into another child table, as

tblAuthorDetails
ADetailID (pk)
PersonID (foreign key from tblAuthorsIllustrators)
<any fields that are specific to authors>

tblIllustratorDetails
IDetailID (pk)
PersonID (foreign key from tblAuthorsIllustrators)
<any fields that are specific to illustrators>

tblBooksDetails
BDetailID (pk)
BookID (fk from tblBooks)
PersonID (fk from tblAuthorsIllustrator)
Function ("author" or "illustrator" or "both")
each person listed for each book will be identified as an author or an
illustrator; the book "Microsoft Access 2000 Bible" has two authors, so
there would be two records in this table for that book, each with the
Function value of "author". you can have as many records as necessary to
list all the authors and illustrators for each book - one person per record.
if one person was an author *and* illustrator for a single book, enter
"Both" in the Function field for that record.

the above suggestions aside, you know more about the data you want to store
and manipulate than you could tell us in this forum, so it's always best if
you analyze your own data and lay out the normalized tables/relationships
yourself. to that end, recommend you read up on the basics of relational
design principles. for more information, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

hth

 

[Mart]

Hey Tina

Thanks for your very comprehensive reply. You've given me plenty to think
about!
I begin to see that I really don't have the savvy, at this point, to even
begin setting up this DB.

Tx again

Martin

 

[Fred Boer]

Dear Mart:

Microsoft offers templates for Access - and one of them is a books database.
However, I can't seem to get to the templates to check the URL because of
something to do with having a "Windows Live ID". I don't know what this is
all about - but someone else might.

There are inexpensive library software programs available, often for little
more than the cost of a few books - google for "Library software".

You might be interested in a freeware program for libraries that I have
created:

http://www3.sympatico.ca/lornarourke/fred.html

Cheers!
Fred

P.S. Now I have to see what this Windows Live ID is all about... argh...
I've got a "Passport", and I've installed the "Genuine Windows Verification"
software... and it isn't enough!?

 

[Mart]

Thanks Fred.
Interesting surname you have there. Very ingrained in the history of our
country (South Africa).

 

[Fred Boer]

Hi Mart:

My parents emigrated to Canada from Holland after the Second World War. My
surname has been a bit of a burden at times - what with the Boer War, and
various comedic mispronunciations; both when I was in school as a boy, and
now, by my students - behind my back, of course! But, as an adult, I like
how it connects me to my family's history, and, since I was raised on a
farm, connects me to that too!

How's the weather there? It's been cold here in Ontario (-25 degrees
celsius...)

Cheers!

Fred Boer (No... we didn't fight in the war... no, it isn't pronounced
"bore"... yes, it rhymes with "sewer" and some other unpleasant words... )

 

[tina]

you're welcome, Mart.
and take heart - you have the savvy to recognize and accept that you're at
the beginning of a long road, and that puts you way ahead of the game at
this point. if you start off on the right track and learn relational design
principles first and then the software tool (unlike many, many people - and
i was one - who learned "backwards" and went through much more struggle and
pain than necessary because of that), you'll find that the pieces come
together more easily and the big picture makes sense. once you begin to get
into it, and see what you can accomplish, Access can be a lot of fun to work
with - and so cool when you're able to things with it that you never
would've thought you could do!

 

[Mart]

Yeah well you can have the -25! It's a rather pleasant +28 here today. I have
a friend in Montreal who I'm always having a go at about the climate he lives
in - freezing in winter, steamy in summer. I just don't get it! (kind of like
me and relational DB's at the moment )

Cheers
Martin

 

[Mart]

Tina

Thanks for your encouragement. Have downloaded a pile of documents and am
reading through them. Hopefully the light will turn on soon.

Cheers
Martin

 

[Keith Wilby]

Tina

Thanks for your encouragement. Have downloaded a pile of documents and am
reading through them. Hopefully the light will turn on soon.

I've been developing using Access for 15 years and I'm *still* learning.
The learning curve is a nice gentle one for me now but there's always a new
trick/tip just around the corner, it keeps the job interesting

Keith.
www.keithwilby.com