Church Database alphabetical assignment

[jmarcrum]

Hi everyone!

I have a church database that keeps track of all of our church's members: first and last name, email address, phone number, home address (street, city, zipcode).

I was wondering....

Every Sunday morning one of the members stands before the congregation in the pulpit and reads about 10 verses from the Bible. After we read through the entire Bible (which takes about 2 1/2 to 3 years), we start over. Every Sunday, it's someone DIFFERENT that reads a DIFFERENT set of verses.

We HAVE been keeping up with who read Iast, what they read, who reads next, and what they WILL read on paper. However, I want to give our church the ability to use our member database to do this...maybe print out a report. You know, to keep track of which member read last and which verses were read, but don't want to have to enter it automatically each month...I want it to cycle through our members alphabetically and assign the Bible verses.

Is there a way to do this, does anyone have an ideas? Make use of a new table?

[Scott Price]

The ability to do this depends entirely on your motivation to learn more about databases and especially VBA. This is not a task for the faint of heart!

What you are asking is to change the structure of your database from primarily being a vehicle to store information to being an activity based scheduler.

It is possible, but first of all, let me suggest that you read a couple of the good books on Access, make sure they're at least 2 inches thick (not a joke). Also, may I suggest reading through Database Design for Mere Mortals 2nd Edition by Mike Hernandez. You can find it on Amazon for a few dollars, and it will do wonders for you in planning and designing the database you want.

Kind regards,
Scott

[Scott Price]

Actually I see from looking at your past threads that you do know something about VBA, which makes me think a little more positively about what you are attempting :-)

Would you mind posting the table structure of your current database, and how you are thinking about implementing the scheduling changes?

Regards,
Scott

[jmarcrum]

Actually I see from looking at your past threads that you do know something about VBA, which makes me think a little more positively about what you are attempting :-)

Would you mind posting the table structure of your current database, and how you are thinking about implementing the scheduling changes?

Regards,
Scott

Hey Scott,

Man thanks for showing interest in my problem! I have several tables, but the three of which I am concerned with are tblMember, tblMenDuties, and tblMenWorshipOp portunity. In tblMenWorshipOp portunity I have 15 different opportunities for "participat ing" in worship, one of which is "Read." The table tblMenDuties utilizes a one-to-many relationship in which I have designed a user-input form to assign opportunities (from tblMenWorshipOp portunity) to a selected member from tblMember.

I am really in a state of "thinking" about what to do now. It's like you said, I know a bit of VBA, and a bit of ACCESS, however, I'm a bit stuck, lol. And like you said, it no longer can just be a storage database. I have about 300 records in tblMember. Now, I want to do something with those members.

I have a query that pulls from tblMenDuties all of the members that wish to "Read". It's sorted in alphabetical order by member last name. I want to make another table (maybe tblRead) to input who read this week 3/2/2008 and automatically pull the next alphabetical name from the query to make a new record with next Sunday's date.

Really, it'd be a continuous process throughout the year...after the last record in the query has been reached (the list of members who want to read), the list will start over, with the first member in the query "alphabetically " and we can continue taking turns reading the Bible.

You're right....this is gonna be tough (sigh...haha)

Thanks,
Joseph

[Scott Price]

I'll try to help your thinking process along with a few questions:

Are you planning to automatically assign the scripture verses to the member? If so, where does that data come from, or where will it come from in your database? Do you currently have a list of readings stored in a table in the database?

How will you deal with an absentee member?

If the reading session is predicated by date, how will you deal with a missed session due to fire, flood, hurricane, war (perish forbid), etc?

Setting the automatic part isn't going to be as hard as dealing with the unexpected once the automatic part gets going!

Regards,
Scott

[jmarcrum]

I'll try to help your thinking process along with a few questions:

Are you planning to automatically assign the scripture verses to the member? If so, where does that data come from, or where will it come from in your database? Do you currently have a list of readings stored in a table in the database?

How will you deal with an absentee member?

If the reading session is predicated by date, how will you deal with a missed session due to fire, flood, hurricane, war (perish forbid), etc?

Setting the automatic part isn't going to be as hard as dealing with the unexpected once the automatic part gets going!

Regards,
Scott

Ok, you're getting me to thinking now. That automatic assigning of the verses is gonna kill me. If I make a table of verses that could take a month or so to complete alone. If I get the user to input the verses into a textbox, I could set a control source, but then again, that wouldn't be "automatic" .

I want the user to be able to select a year and a month and hit Go, and it assign members to readings on a respective date.

The absentee member is simple IMO. If a member is absent, a deacon just finds someone else from the congregation to "fill in" for the duty, no problem. If a member is absent, then that member just misses their opportunity. Their name is still in the qryWantsToRead, so their name will eventually come back around...

The same thing goes for a missed session due to a disaster. If a session is missed...it's just missed. I want the database to continue cycling through the query and making assignments.

Basically, this "making assignments" is just so our church can have an "ideal" electronic representation of who should be doing what, when.

Thanks again for your advice I hope we can keep talking! Am I on the right track?

Thanks,
Joseph

[Scott Price]

I think you're on the right track, just keep thinking!

The missed date issue may be a little tougher than you think... For example, assigning a member and a reading to a date isn't hard, but what happens when the date is missed? Do you just keep going without reading that passage? Does the same member read the next session?

These adjustments are easy to make in real life on the fly, but teaching a database to react to the unexpected isn't as easy! What will happen is that the database will treat a missed date as not missed and will happily continue to generate the assignments. However, if you want to be equable about it, and decide that, in fact, the member who was assigned to read on a missed date, should read on the next available date instead, how are you going to make your database reflect that change?

A related point that I've been assuming up to now, is that you intend to print out a report that reflects who reads what on which date... If so, this brings my question up again, how do you account for a missed member and a missed session?

Automatically assigning the member will generate a report with that member. If the member misses, the next in line will be assigned on the spot by the deacon to fill in, but how are you going to tell the database to skip the fill-in the next go-round?

Do you see what I'm getting at now? The automatic assignment isn't that difficult to create in a bare-bones sort of way. I'm referring to these other issues when I say it's difficult.

You can create it with just a few more tables, a few more queries and a bit of code. All I'm trying to help you do is foresee as many unexpected events as possible in order to provide for these when you begin to design the actual structure and later when you start coding. Otherwise you'll get it operational in a short (relatively speaking) time, then be disappointed, frustrated and ultimately dejected when you have to start over again and go through the structure re-designing and re-coding.

Keep the thoughts coming!

Regards,
Scott

[jmarcrum]

I think you're on the right track, just keep thinking!

The missed date issue may be a little tougher than you think... For example, assigning a member and a reading to a date isn't hard, but what happens when the date is missed? Do you just keep going without reading that passage? Does the same member read the next session?

These adjustments are easy to make in real life on the fly, but teaching a database to react to the unexpected isn't as easy! What will happen is that the database will treat a missed date as not missed and will happily continue to generate the assignments. However, if you want to be equable about it, and decide that, in fact, the member who was assigned to read on a missed date, should read on the next available date instead, how are you going to make your database reflect that change?

A related point that I've been assuming up to now, is that you intend to print out a report that reflects who reads what on which date... If so, this brings my question up again, how do you account for a missed member and a missed session?

Automatically assigning the member will generate a report with that member. If the member misses, the next in line will be assigned on the spot by the deacon to fill in, but how are you going to tell the database to skip the fill-in the next go-round?

Do you see what I'm getting at now? The automatic assignment isn't that difficult to create in a bare-bones sort of way. I'm referring to these other issues when I say it's difficult.

You can create it with just a few more tables, a few more queries and a bit of code. All I'm trying to help you do is foresee as many unexpected events as possible in order to provide for these when you begin to design the actual structure and later when you start coding. Otherwise you'll get it operational in a short (relatively speaking) time, then be disappointed, frustrated and ultimately dejected when you have to start over again and go through the structure re-designing and re-coding.

Keep the thoughts coming!

Regards,
Scott

Hey Scott!

Wow you are amzingly fast at your responses!!! I thank you so much!!!

I like everything that you've mentioned. There's absolutely no way I'll be able to get around skipping the "fill-in". I'll have to work on that one a bit more. You're right about the report. The paper will say that joe jones read proverbs 1:1-10 on 3/2/2008 and bob bradley should read proverbs 1:11-20 on 3/9/2008, but if joe is absent and bob has to fill-in for joe, that's ok in person...but the database needs to know that bob and joe should now "swap places" in a manner of speaking. Good thoughts Scott, I really appreciate it!

I was wondering though, how do I go about getting this started? I know that's a loaded question, because we've been talking about getting it started this whole time. I know I have to work out these bugs in my head before I can really get crackin, but...once I do, you were saying that it would be relatively simple to get a couple of tables going and set to the grindstone...

What is the best way to go at this?

[Scott Price]

To get started, plan the table structure on paper, paying especial attention to the relationships you'll have between the tables.

For example, I would personally consider the references to need at least two tables... tblBooks with fields, BookID, BookName, NumChapters. Example data: 1, Genesis, 50. Second table: tblChapters with fields, ChapterID, BookID, ChapterNum, NumVerses. Example data: 1, 1, 1, 31. If you wanted to list the text associated with each verse you would need a third table (you probably don't want to get into re-typing the entire Bible a verse at a time, though :-)

You have a One to Many relationship between tblBooks and tblChapters.

Next think of a way to write a function in VBA that you can call from within a query that will first of all take a starting reference, compare it with the number of verses in the chapter, then return a string concatenated with the book name, chapter number and the next 10 verses starting from the reference given. Decide how you are going to handle when the chapter contains a number of verses that isn't divisible by 10: are you going to add the remaining x number of verses from the next chapter?

You'll likely need another table to hold the last-read reference to use in this function; call it tblReadReferenc es with RefID, BookID, ChapterID, Verses (the verses field violates one of the normal form rules of database design, but we might be able to get away with it, we'll see...)

This is just an example of where I would start!

Regards,
Scott

[jmarcrum]

To get started, plan the table structure on paper, paying especial attention to the relationships you'll have between the tables.

For example, I would personally consider the references to need at least two tables... tblBooks with fields, BookID, BookName, NumChapters. Example data: 1, Genesis, 50. Second table: tblChapters with fields, ChapterID, BookID, ChapterNum, NumVerses. Example data: 1, 1, 1, 31. If you wanted to list the text associated with each verse you would need a third table (you probably don't want to get into re-typing the entire Bible a verse at a time, though :-)

You have a One to Many relationship between tblBooks and tblChapters.

Next think of a way to write a function in VBA that you can call from within a query that will first of all take a starting reference, compare it with the number of verses in the chapter, then return a string concatenated with the book name, chapter number and the next 10 verses starting from the reference given. Decide how you are going to handle when the chapter contains a number of verses that isn't divisible by 10: are you going to add the remaining x number of verses from the next chapter?

You'll likely need another table to hold the last-read reference to use in this function; call it tblReadReferenc es with RefID, BookID, ChapterID, Verses (the verses field violates one of the normal form rules of database design, but we might be able to get away with it, we'll see...)

This is just an example of where I would start!

Regards,
Scott

Scott dude!

You are awesome!! Got me thinking sooooo well on this project. You gotta love this forum!!!

Thanks Scott!
Thanks theScripts!!

[Scott Price]

You're quite welcome!

Glad we could help.

Regards,
Scott