Solved Bible Database Forms

[WaldinTheAnalyst]

in my books form when I select the first four books in my combobox, Genesis, Exodus, Leviticus, Numbers and click my "Open Verse Form" button it opens my Verse Form displaying the respective data related to the Book Name I selected in my combobox (thanks to the incredible help i recieved from you gentleman), however, any value or Book Name I select in my Combobox following from Deuteronomy (book5) and below does not display anything when the Verses Form opens. Once again I've added a screenshot below for visual clarity, please ease my stress and assist me to solve this.

 

[The_Doc_Man]

Whatever method you use to select any of the first four books works, but other methods don't. Look carefully at what happens when you select Exodus or Leviticus (because they are not the 1st book nor the last book). Then you need to select Deuteronomy and see what happens there.

You need to read up on using breakpoints in your code.

As a really wild-eyed guess, since 1-4 work but 5 doesn't, the problem MAY be that your data for the later books isn't properly tagged so cannot be selected.

 

[theDBguy]

Are you able to post a sample db?

 

[MajP]

I selected in my combobox (thanks to the incredible help i recieved from you gentleman), however, any value or Book Name I select in my Combobox following from Deuteronomy (book5) and below does not display anything when the Verses Form opens.

That is because you do not have any matching records for that book.
See my rowsource. It only returns possible books.

SELECT books.book_id,
       books.book_name
FROM   (books
        INNER JOIN chapters
                ON books.book_id = chapters.book_id)
       INNER JOIN verses
               ON chapters.chapter_id = verses.chapter_id
GROUP  BY books.book_id,
          books.book_name
ORDER  BY books.book_id;

 

[WaldinTheAnalyst]

That is because you do not have any matching records for that book.
See my rowsource. It only returns possible books.
View attachment 116869

SELECT books.book_id,
       books.book_name
FROM   (books
        INNER JOIN chapters
                ON books.book_id = chapters.book_id)
       INNER JOIN verses
               ON chapters.chapter_id = verses.chapter_id
GROUP  BY books.book_id,
          books.book_name
ORDER  BY books.book_id;

yes I noticed that your query only returns books if it has associated chapters and verse, which would assume that all my other books hasn't any associated chapters and verses...is this correct and if so, could my problem be with my relationships?

 

[WaldinTheAnalyst]

Are you able to post a sample db?

yes sure

 

[WaldinTheAnalyst]

That is because you do not have any matching records for that book.
See my rowsource. It only returns possible books.
View attachment 116869

SELECT books.book_id,
       books.book_name
FROM   (books
        INNER JOIN chapters
                ON books.book_id = chapters.book_id)
       INNER JOIN verses
               ON chapters.chapter_id = verses.chapter_id
GROUP  BY books.book_id,
          books.book_name
ORDER  BY books.book_id;

so my million dollar question would be how do I fix this error in my data?

 

[MajP]

yes I noticed that your query only returns books if it has associated chapters and verse, which would assume that all my other books hasn't any associated chapters and verses...is this correct and if so, could my problem be with my relationships?

The relationship looks correct, but you data may be wrong.

You have 35,000 chapters from 74 books. Most books have around 40 chapters so that looks correct.
You have 35,000 verses but they only point to 150 unique chapters. My guess is that you are not pointing to the correct chapters.

There is no easy fix unless this comes from some data source. My guess is the key is wrong.

I think the table structure is normal but not needed, unless you are going to have more information about a chapter. Currently you only have a Chapter number and this is overkill. If you were planning to hold other things like Chapter name, Chapter Summary, Author, etc. then you need that table.

So if this is the case I would simply get rid of the chapter table if not planning on more chapter fields.

Versus table then simply holds the
Book_ID
Chapter_Number

This simplifies things and makes it much easier to see this problem.

 

[MajP]

so my million dollar question would be how do I fix this error in my data?

You need to look at the data and see what verses is pointing to the wrong chapter and thus wrong book.
From this point of view (not knowing the bible) I could not tell you if the data is wrong. I would have to know what book and chapter a verse should point to. In fact it could be correct and the versus you have only belong to these handful of books. I am guessing that is not the case.

If you populated this by hand then the fix is probably like this assuming you added the versus in order

I would build a form to do it if not you are manually updating a ton of update queries.
Form is like a split view showing the versus
Form has two unbound combo boxes versid start and verseid end.
Form has unbound Book combo
Form has cascading Chapter combo

Pick a range of verses and select the correct book and correct chapter.
Hit the update button
This runs an update query something like
"update Verses Set Chapter_ID = " & me.cmboChapter & " WHERE VerseID Between " & me.cmboStart & " AND " & Me.cmboEnd

1. Decide if you want to simply get rid of the chapter table and add chapter number to the verse table. If you plan more meta data about the Chapter then leave the table. If you only care about a number, now is the time to fix
2. Look at the data and pick a range of verse IDs and tell me what book and chapter they should point to. I will see if I can demo a quick form.
With the split form view you will be able to then see your fixes as you run the updates.

 

[WaldinTheAnalyst]

You need to look at the data and see what verses is pointing to the wrong chapter and thus wrong book.
From this point of view (not knowing the bible) I could not tell you if the data is wrong. I would have to know what book and chapter a verse should point to. In fact it could be correct and the versus you have only belong to these handful of books. I am guessing that is not the case.

If you populated this by hand then the fix is probably like this assuming you added the versus in order

I would build a form to do it if not you are manually updating a ton of update queries.
Form is like a split view showing the versus
Form has two unbound combo boxes versid start and verseid end.
Form has unbound Book combo
Form has cascading Chapter combo

Pick a range of verses and select the correct book and correct chapter.
Hit the update button
This runs an update query something like
"update Verses Set Chapter_ID = " & me.cmboChapter & " WHERE VerseID Between " & me.cmboStart & " AND " & Me.cmboEnd

1. Decide if you want to simply get rid of the chapter table and add chapter number to the verse table. If you plan more meta data about the Chapter then leave the table. If you only care about a number, now is the time to fix
2. Look at the data and pick a range of verse IDs and tell me what book and chapter they should point to. I will see if I can demo a quick form.
With the split form view you will be able to then see your fixes as you run the updates.

Wow Let me do a quick analysis of my data

 

[WaldinTheAnalyst]

You need to look at the data and see what verses is pointing to the wrong chapter and thus wrong book.
From this point of view (not knowing the bible) I could not tell you if the data is wrong. I would have to know what book and chapter a verse should point to. In fact it could be correct and the versus you have only belong to these handful of books. I am guessing that is not the case.

If you populated this by hand then the fix is probably like this assuming you added the versus in order

I would build a form to do it if not you are manually updating a ton of update queries.
Form is like a split view showing the versus
Form has two unbound combo boxes versid start and verseid end.
Form has unbound Book combo
Form has cascading Chapter combo

Pick a range of verses and select the correct book and correct chapter.
Hit the update button
This runs an update query something like
"update Verses Set Chapter_ID = " & me.cmboChapter & " WHERE VerseID Between " & me.cmboStart & " AND " & Me.cmboEnd

1. Decide if you want to simply get rid of the chapter table and add chapter number to the verse table. If you plan more meta data about the Chapter then leave the table. If you only care about a number, now is the time to fix
2. Look at the data and pick a range of verse IDs and tell me what book and chapter they should point to. I will see if I can demo a quick form.
With the split form view you will be able to then see your fixes as you run the updates.

Thing with the chapter table is that it holds:
chapter_id PK auto incr
Book_id
chapter_number
Now the chapter_id counts entry from 1 down to the last thousands and the chapter_number will restart at soon as a new book_id starts

You need to look at the data and see what verses is pointing to the wrong chapter and thus wrong book.
From this point of view (not knowing the bible) I could not tell you if the data is wrong. I would have to know what book and chapter a verse should point to. In fact it could be correct and the versus you have only belong to these handful of books. I am guessing that is not the case.

If you populated this by hand then the fix is probably like this assuming you added the versus in order

I would build a form to do it if not you are manually updating a ton of update queries.
Form is like a split view showing the versus
Form has two unbound combo boxes versid start and verseid end.
Form has unbound Book combo
Form has cascading Chapter combo

Pick a range of verses and select the correct book and correct chapter.
Hit the update button
This runs an update query something like
"update Verses Set Chapter_ID = " & me.cmboChapter & " WHERE VerseID Between " & me.cmboStart & " AND " & Me.cmboEnd

1. Decide if you want to simply get rid of the chapter table and add chapter number to the verse table. If you plan more meta data about the Chapter then leave the table. If you only care about a number, now is the time to fix
2. Look at the data and pick a range of verse IDs and tell me what book and chapter they should point to. I will see if I can demo a quick form.
With the split form view you will be able to then see your fixes as you run the updates.

the only solution I can think of is to add the "book_name" column to the "Verses" table this way each verse would have a book name associated with it, and this I would probably do manually. but this is my solution at my inexperienced level, would it be possible if you could go ahead with your fix and I'm sure that would solve the issue even though I don't understand its logic at this moment.

 

[WaldinTheAnalyst]

but something doesn't make sense to me if I am drawing data from the same My SQL Workbench be, and I've sought my data from the same dataset using the same tables how come does the first four columns data only pull through with this query in access?

 

[MajP]

Again, I do not think there is any problem with the relation. It is just not needed because you really are not doing anything with chapters
It is a whole table only to hold a Chapter number. If you had more fields related to a chapter then this would absolutely make sense.

You can leave it giving you some flexibility in the future or simply do the following.

Current design
Verse_ID
Chapter_ID
Verse_Number
Text (bad name)
Chapter_Number

Delete:
Chapter_ID

Add:
Book_ID
Chapter_Number (which is already there for some reason)

Now delete the chapter table.

Now link a Verse to a book by BookID and the Chapter number is in the Verse table.

However it will work as you have it designed and this modification is not required.

how come does the first four columns data only pull through with this query in access?

I do not know what this means, please explain.
What four columns populates, which do not?

but this is my solution at my inexperienced level, would it be possible if you could go ahead with your fix and I'm sure that would solve the issue even though I don't understand its logic at this moment

I do not think I can fix anything since I do not think anything is broken. I could simplify the design by having a verse be a child of a book and hold the chapter number. I would pull the chapter number from you current table.
But if I am guessing correctly you have the verses pointing to the incorrect chapter and potentially in turn the incorrect book. However, there is no way to update to the correct chapter and book without knowing where it is supposed to point.

You have some big problems with PK
Primary keys have to be unique. You have 2 Books with a PK of 1.

 

[WaldinTheAnalyst]

You need to look at the data and see what verses is pointing to the wrong chapter and thus wrong book.
From this point of view (not knowing the bible) I could not tell you if the data is wrong. I would have to know what book and chapter a verse should point to. In fact it could be correct and the versus you have only belong to these handful of books. I am guessing that is not the case.

If you populated this by hand then the fix is probably like this assuming you added the versus in order

I would build a form to do it if not you are manually updating a ton of update queries.
Form is like a split view showing the versus
Form has two unbound combo boxes versid start and verseid end.
Form has unbound Book combo
Form has cascading Chapter combo

Pick a range of verses and select the correct book and correct chapter.
Hit the update button
This runs an update query something like
"update Verses Set Chapter_ID = " & me.cmboChapter & " WHERE VerseID Between " & me.cmboStart & " AND " & Me.cmboEnd

1. Decide if you want to simply get rid of the chapter table and add chapter number to the verse table. If you plan more meta data about the Chapter then leave the table. If you only care about a number, now is the time to fix
2. Look at the data and pick a range of verse IDs and tell me what book and chapter they should point to. I will see if I can demo a quick form.
With the split form view you will be able to then see your fixes as you run the updates.

Thing with the chapter table is that it holds:
chapter_id PK auto incr
Book_id
chapter_number
Now the chapter_id counts entry from 1 down to the last thousands and the chapter_number will restart at soon as a new book_id starts

 

[MajP]

The book of Genesis has 50 Chapters and 1533 versus, but your mapping has 33,666 versus mapped against Genesis. 1808 alone against Chapter 1. Looking at it I think that number is more like 12.
book chapter chapter Number of Verses
Genesis 1 1 1808
Genesis 2 2 1761
Genesis 3 3 1673
Genesis 4 4 1529
Genesis 5 5 1326
Genesis 6 6 1395
Genesis 7 7 1393
Genesis 8 8 1208
Genesis 9 9 1266
Genesis 10 10 1214
Genesis 11 11 1231
Genesis 12 12 1084
Genesis 13 13 1123
Genesis 14 14 1101
Genesis 15 15 978
Genesis 16 16 888
Genesis 17 17 672
Genesis 18 18 750
Genesis 19 19 729
Genesis 20 20 698
Genesis 21 21 728
Genesis 22 22 724
Genesis 23 23 662
Genesis 24 24 633
Genesis 25 25 521
Genesis 26 26 538
Genesis 27 27 478
Genesis 28 28 456
Genesis 29 29 402
Genesis 30 30 375
Genesis 31 31 382
Genesis 32 32 362
Genesis 33 33 325
Genesis 34 34 306
Genesis 35 35 224
Genesis 36 36 310
Genesis 37 37 232
Genesis 38 38 228
Genesis 39 39 215
Genesis 40 40 229
Genesis 41 41 195
Genesis 42 42 153
Genesis 43 43 165
Genesis 44 44 168
Genesis 45 45 127
Genesis 46 46 131
Genesis 47 47 122
Genesis 48 48 175
Genesis 49 49 139
Genesis 50 50 134

 

[MajP]

hing with the chapter table is that it holds:
chapter_id PK auto incr
Book_id
chapter_number
Now the chapter_id counts entry from 1 down to the last thousands and the chapter_number will restart at soon as a new book_id starts

So what? What do you do with that. Does not do anything except potentially not allow the user to pick a chapter number that does not exist in that book.
You are not going to add new books or chapters to the Bible.

A verse simply needs to store a book ID and a chapter number.

Again the problem is your data not your table design. Either will work. You are pointing many records to the wrong chapter ID. Only way to fix that is to correct the chapter (and possibly book).

 

[WaldinTheAnalyst]

Again, I do not think there is any problem with the relation. It is just not needed because you really are not doing anything with chapters
It is a whole table only to hold a Chapter number. If you had more fields related to a chapter then this would absolutely make sense.

You can leave it giving you some flexibility in the future or simply do the following.

Current design
Verse_ID
Chapter_ID
Verse_Number
Text (bad name)
Chapter_Number

Delete:
Chapter_ID

Add:
Book_ID
Chapter_Number (which is already there for some reason)

Now delete the chapter table.

Now link a Verse to a book by BookID and the Chapter number is in the Verse table.

However it will work as you have it designed and this modification is not required.



I do not know what this means, please explain.
What four columns populates, which do not?


I do not think I can fix anything since I do not think anything is broken. I could simplify the design by having a verse be a child of a book and hold the chapter number. I would pull the chapter number from you current table.
But if I am guessing correctly you have the verses pointing to the incorrect chapter and potentially in turn the incorrect book. However, there is no way to update to the correct chapter and book without knowing where it is supposed to point.

You have some big problems with PK
Primary keys have to be unique. You have 2 Books with a PK of 1.

I think you made sense when you said there are missing associations in my Data, so in my SQL workbench I ran the below queries on my data just to see what I could establish, please see my SQL comments & code in screenshots below. when i ran your inner join in mySQL worbench i recieved the same results, only four tables Genesis, Exodus, leviticus and Numbers.
The INNER JOIN only includes rows where all the joins have matches in each table. this means that books with missing chapters or chapters with missing verses will be excluded entirely. andi thought my SOLUTION for this would be a LEFT JOIN, if i want all books to appear, even if some lack chapters or verses this would work instead right?

 

[WaldinTheAnalyst]

The book of Genesis has 50 Chapters and 1533 versus, but your mapping has 33,666 versus mapped against Genesis. 1808 alone against Chapter 1. Looking at it I think that number is more like 12.
book chapter chapter Number of Verses
Genesis 1 1 1808
Genesis 2 2 1761
Genesis 3 3 1673
Genesis 4 4 1529
Genesis 5 5 1326
Genesis 6 6 1395
Genesis 7 7 1393
Genesis 8 8 1208
Genesis 9 9 1266
Genesis 10 10 1214
Genesis 11 11 1231
Genesis 12 12 1084
Genesis 13 13 1123
Genesis 14 14 1101
Genesis 15 15 978
Genesis 16 16 888
Genesis 17 17 672
Genesis 18 18 750
Genesis 19 19 729
Genesis 20 20 698
Genesis 21 21 728
Genesis 22 22 724
Genesis 23 23 662
Genesis 24 24 633
Genesis 25 25 521
Genesis 26 26 538
Genesis 27 27 478
Genesis 28 28 456
Genesis 29 29 402
Genesis 30 30 375
Genesis 31 31 382
Genesis 32 32 362
Genesis 33 33 325
Genesis 34 34 306
Genesis 35 35 224
Genesis 36 36 310
Genesis 37 37 232
Genesis 38 38 228
Genesis 39 39 215
Genesis 40 40 229
Genesis 41 41 195
Genesis 42 42 153
Genesis 43 43 165
Genesis 44 44 168
Genesis 45 45 127
Genesis 46 46 131
Genesis 47 47 122
Genesis 48 48 175
Genesis 49 49 139
Genesis 50 50 134

WOW!!! Okay I'm convinced that you have solved the problem, can you please in Leimans terms tell me how do I get my database to good functionality concerning this specific issue at hand. you have provided me with much insight above as to altering my table with explanations so much that it confuses me, please now that we know this is the issue how would you suggest i move forward with this, Thank Youwell done

 

[MajP]

WOW!!! Okay I'm convinced that you have solved the problem

No. I fixed nothing. I only pointed out the problem. You have bad data and my guess is that somewhere along the line someone did an update query incorrectly. So verses are pointing to the wrong chapter ID and thus the wrong book.

This does not fix anything because the problem is a data problem and not a table problem. Many of the book and chapter numbers are incorrect.

You somehow need to assign the correct book and chapters to a given verse. I am assuming you did not assign and collect these verses by hand and somewhere the keys just got messed up. Do you have the source data somewhere? If not you are going to have to do this by hand and that is 35K updates.
Even if you made a nice form to do the updates in groups, at most you would update10-100 versus at a time. Still a lot of work when you have 35k to update.


I did go ahead an modify the table to the simpler structure. I did some other fixes on the books form so you can pick a chapter and go to that chapter. But again you can leave it as is, but this is simpler for me. Simply move the Book_ID into the verses table.

In my design now the Verse has a BookID and a ChapterNumber.

 

[WaldinTheAnalyst]

Whatever method you use to select any of the first four books works, but other methods don't. Look carefully at what happens when you select Exodus or Leviticus (because they are not the 1st book nor the last book). Then you need to select Deuteronomy and see what happens there.

You need to read up on using breakpoints in your code.

As a really wild-eyed guess, since 1-4 work but 5 doesn't, the problem MAY be that your data for the later books isn't properly tagged so cannot be selected.

Thank you after running queries and analyzing my data i have found the inconsistencies