Avoiding Redundency in the Database

[snowboarder234]

My predecessor set up a Language field in this library database.



Is there a better way of doing this? Should I create two fields, ie; English, Spanish and then provide a checkbox? That still seems a little redundant. What about a table for each language, then a lookup field? I should also add that the ultimate goal is to build a form once the database design is finalized and tested.

Thank you.







Here's another conundrum: 1 author has two books with one title, but listed as two entries as volume one and two. How to record that without entering the author twice?

[ranman256]

I would have 1 table for each language just to keep my sanity.

2:you would have an tAuthors table, and a tTitles table.
you would put in the authors index ID twice in tTitles,
but his name only once in tAuthors.

[ssanfu]

You haven't explained/described what the database's function is nor the table structures/relationship. That makes it really hard to answer your question.
Can you describe the purpose of the dB and ow it is used?


With what little you have provided, the language field is how I would probably set it up. It would be easy to add another language without changing the tables, queries,forms,reports.

And the field should NOT a lookup field.

[snowboarder234]

The database is a library of books specific to a department at a university. It is not a lending library for the whole campus.
Why should it not be a lookup field?

[ssanfu]

Why should it not be a lookup field?

See
The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm


Most experienced Access programmer do not use look up fields.


I would add another table "LANGUAGES" and change BOOKS.Language to BOOKS.Language_FK (Long).
If you had to add another language - say German - you would just have to add it to the table. No changes to the tables, queries, forms or reports.

[snowboarder234]

So I was making progress on the many-to-many relationships and have begun entering data when all of a sudden when I try to enter data in the Authors table the Author ID number is no longer being automatically generated and thus, the error message depicted below:
NewZip.zip

I've attached the zip file.

Also, I have a question about not being able to delete information in a field when I've made a mistake. Please let me know if that can be discussed here or if I need to move to another thread.

Thank you.

[ssanfu]

have begun entering data when all of a sudden when I try to enter data in the Authors table the Author ID number is no longer being automatically generated

The PK field has been changed from an Autonumber to a Number (Long). The Authors table PK field should be an Autonumber.

1) Add a new field. Name it "Author_ID_PK". Select Autonumber as the data type.
2) Check to ensure the fields "Author_ID" & "Author_ID_PK" have the same numbers (1 = 1, 2 = 2, etc)
3) Delete the existing field "Author_ID".
4) Move "Author_ID_PK" to the top.
5) Edit the name. Remove "_PK".
6) Set the new ""Author_ID" as the PK field.
7) Save the table.



Also found another error. In "tblLanguages", "Language" is a reserved word.
Change it to something like "LanguageName" or "txtLanguage".

[ssanfu]

Tear this dB apart.... see if it helps you.....

[snowboarder234]

I found an error in the database so I [unwisely] delinked the relationships and deleted the records. Now when I try to relink the Books & Authors to the junction table this is the error message I get. I realize the error messages gives a solution, but I'm afraid to proceed from here without extra advice before I mess it up more than I already have. I deleted the following records:

1.Title: Mexico and the United States: Neighbors in Crisis. Authors: Daniel G. Aldrich, Jr. & Lorenzo Meyer
2.Title: In the Shadow of the Mexican revolution. Authors: Hector Aguilar Camin & Lorenzo Meyer

UPDATE: After reading this link: https://support.microsoft.com/en-us/kb/112111
I printed out the Authors & Books Datasheets and manually matched them up the author to the book. I did find a duplicated book title, which I deleted, but still can't link the tables.

[snowboarder234]

Attached is the latest zipped file if it helps. Please see Zip3.
NewZip_2 is already outdated.

[ssanfu]

Why did you delete the relationship to delete records???


OK, the error message means you have records in tblBooksAuthors where the AuthorID_FK is not in Author.Author_ID_PK. Because you are trying to set Referential Integrity, the values in "tblBooksAuthors.AuthorID_FK" MUST be in the table "Authors.Author_ID_PK" (the PK/FK relationship).

---------------------------------------------------------------
One method

To see which record(s) need to be deleted in "tblBooksAuthors", create a query using this SQL (switch to SQL View):

SELECT tblBooksAuthors.AuthorID_FK AS BooksAuthor, Authors.Author_ID_PK AS Author
FROM Authors RIGHT JOIN tblBooksAuthors ON Authors.Author_ID_PK = tblBooksAuthors.AuthorID_FK
ORDER BY tblBooksAuthors.AuthorID_FK;

When you execute this query, any blanks/Nulls in the "Authors" column means you have to delete those records in the "tblBooksAuthors" table.

From "tblBooksAuthors", you will need to delete the records where AuthorID_FK is 22, 23, 33 and 34.



Now do the same thing for "tblBooksAuthors" and "tblBooks". The query is

SELECT tblBooksAuthors.BookID_FK AS BooksAuthors, tblBooks.Book_ID AS Books
FROM tblBooks RIGHT JOIN tblBooksAuthors ON tblBooks.Book_ID = tblBooksAuthors.BookID_FK
ORDER BY tblBooksAuthors.BookID_FK;

When you execute this query, any blanks/Nulls in the "tblBooks" column means you have to delete that record in the "tblBooksAuthors" table.

From "tblBooksAuthors", you will need to delete the delete the record where BookID_FK is 29.

---------------------------------------------------------

The other method is to delete ALL records from "tblBooksAuthors", re-link the tables and re-enter the records in "tblBooksAuthors".





----EDIT: The two queries (above) are only temporary queries. You can delete the two queries after you get the tables re-linked.

[snowboarder234]

Thank you again for your assistance. I used the first method you suggested and it worked.
The reason I deleted the links was because I didn't know how else to delete a record -- which is what I'm asking assistance for in this case:

I'm assuming I take the same steps in the above post. But wanted to double check if that's correct.
Thank you.

Also, as an aside -- I'm assuming I can't alpha sort the BookTitle field because that field is hypertext?

[ssanfu]

You need to delete the record from the junction table "tblBooksAuthors" (the "many" table), not table "Authors" OR "tblBooks" (the "one" tables).
If you want to delete an Author from the Author table, there cannot be any records in "tblBooksAuthors" with a FK to that Author.

[snowboarder234]

Before I proceed I want to make sure I understand you clearly. I am to delete the AuthorID_FK from this table?:

Then delete the author in question, then relink? Is that correct?
Thank you.

[ssanfu]

NO!

Do not delete any relationships. Do not delete any fields.

You delete RECORDS in the tblBooksAuthors junction table.

Open the query "Query1", find the record you want to delete, press the delete key.