Re: Consolidate duplicate entries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Tue, 7 Apr 2009 09:41:13 +0100, "Tom" <Tom@xxxxxxxxxxxx> wrote:

You do indeed have bad database design, because the Authors table has
a BookID. Rather the Books table should have an AuthorID (if you only
want to record the primary author) or there should be a new table with
BookID and AuthorID (PK over both fields) if you want to record the
(perhaps several) authors for a book.
Enforce all relationships.
Make these changes first; then this "consolidation" is automatically
taken care of.

-Tom.
Microsoft Access MVP


Hi

Am rebuilding a book db which lists approx 40k of books, on looking through
a table listing the authors - with fields AuthSName, AuthFName, BookID,
AuthorID - we are noticing numerous duplications of the same authors.
AuthSName and AuthFName are text fields while BookID and AuthorID are
numeric fields.

What I would like to do is consolidate duplicated Authors - AuthSName and
AuthFName - so that they have the same AuthorID.

What is the best way to automate this process?

Any advice/suggestions very much appreciated.

TIA

Tom

.

Relevant Pages

  • Consolidate duplicate entries
    ... AuthorID - we are noticing numerous duplications of the same authors. ... AuthSName and AuthFName are text fields while BookID and AuthorID are ...
    (microsoft.public.access.tablesdbdesign)
  • Re: compond index and key faster/better?
    ... > bookid int identity ... > alter table books add constraint PK_books primary key clustered ... Since bookid is unique, why add authorid ... Having the clustered index on authorid is probably better than clustering ...
    (comp.databases.ms-sqlserver)
  • Re: A calculated field and a Master/Detail relationship
    ... I don't know if I understood the TQuery suggestion. ... > While authors have many books, ... > storing the BookId and the AuthorId as appropriate. ...
    (comp.lang.pascal.delphi.misc)
  • Re: compond index and key faster/better?
    ... Since bookid is unique, why add authorid ... Only for the purpose of having the clustered index that way. ... > Then again, PK of a books table should probably be the ISBN, as that ...
    (comp.databases.ms-sqlserver)
  • Re: A calculated field and a Master/Detail relationship
    ... While authors have many books, ... the appropriate field of each table (BookId to Books and AuthorId to Authors). ... Alan Lloyd ...
    (comp.lang.pascal.delphi.misc)