Generate many-to-many relationship

[H@r@ld]

I have a single table called "Books". One column is "Book title" and one is "Author(s)".

One author may have written several books, and one book may be co-authored by several authors.

It should be ok to use the analyze function to normalize the table into 3 tables. (autors, books and booksauthors_xref)

The problem is that the Authors-column contains one or more autors separated by ;

When I use the analyze function I only get the first autor in the join table. Since authors "AA ; BB" are treated as one, not to entities I had to split the field into a "co-autor" column and manually add one and one co author.

Is it a smarter vay to do the normalization?

 

[KeithG]

If a book has more than one author just add another record in the booksAuthor table

 

[H@r@ld]

automatically?

of course, but if I have several thousand such relationships. Its not "just to add another record to the booksAutorsXref" table. There must be a smarter way.

 

[Len Boorman]

The problem is that the Authors-column contains one or more autors separated by ;
This is the fundamental problem. It is a multi valued attribute and until you normalise teh tables correctly ypu will have problems. There is no simple way out in my opinion.

L

 

[Fear Naught]

You could write some code that loops through each record and the table to separate the the authors in each record and append them to a new table. This would take some doing but as you have a ';' between each author you do have a constant character with which to work.


HTH

 

[Len Boorman]

You might find that exporting the table to Excel and using a combination of Find, Left$ Mid$ would enable you to break up the authors more easily and then use this data to reconstruct the data in a normalised database design

L