Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003

    Question The best way to design ...

    Ok, I'm trying to design a database in Access 2002 ... for a church. I have a table "Members" which obviously holds names, details etc ...

    I then want to create a report sheet for each ministry, for example the music team. So I created a "Worship" table which I want to hold specific members to that team, their arrival stat's (late, on time, absent), who was leading, donations, comments etc.

    I thought of creating a seperate Attendance table, which holds the team members, arrival, donations ... data.

    But now I'm confused.

    Can anyone help me design this database?

    OR, have any good links to tutorial's on membership type databases.


  2. #2
    Join Date
    Oct 2003
    Boston, Mass. USA
    i'm sure you could find such resources, perhaps at your library or on or

    the 60 million dollar (or euro) question is ... what do the people who will use this database want to keep track of? If they dont' want to keep track of attendance then why bother? (unless it is just a practice exercise to help you learn)

    If the users don't know what they need/want to keep track of ... then before designing a database you need to put on a 'consultant' hat and help them figure it out. (or have someone else do it instead of you)

    good luck!

  3. #3
    Join Date
    Oct 2003
    thanks for the reply mark!

    Let me show you the form I built for them ... it might help you understand what I'm trying to accomplish here.

    can you help?

  4. #4
    Join Date
    Oct 2003
    Boston, Mass. USA
    Hi Tyler -

    well, based on the form you've shown, here is a possible data schema -

    various tables with some sample data as follows:

    MemberID MemberName ( MemberAddreess, MemberPhone, etc,)
    1, Catherine Thompson
    2, Dibs Dawson
    3, Donna Gibbons
    4, Esther Robb
    5, Greg Knowles
    6, Ian Simpson
    7, Jessica Conway
    8, Joel Taylor
    9, Josh Gibbons
    10, Katherine Gibbons
    11, Kylie Hutton
    12, Lit Morahan
    13, Liz Taylor
    14, Lynette Lanini
    15, Maka Mukoki
    16, Pagan Reedy
    17, Rauna Henry
    18, Ruth Bisset
    19, Sacha Tempest
    20, Toni Holgate
    21, Wendy Simpson

    Team ID, TeamName,
    1, Worship Team
    2, Setup Committee
    3, Refreshments Group

    Since you have one-to-many relationship with leaders and teams (you can have more than one leader per team and the same leader—theoretically--can lead more than one team),

    Leader-ID(ie index) , TeamID, MemberID
    1, 1, 9 (you could store it as 1,Worship,Josh but it’s easier to join tables this way)
    2,1, 8 (you could store it as 1,Worship,Joel, but it’s easier to join tables this way)
    3,2, 1, (if Catherine Thompson leads the setup committee))
    4, 3, 15 (if Maka Mukoki leads the refreshments committee)
    1, 2, 9 (supposing Joel also leads the setup committee)

    Index, TeamID, MemberID
    1, 1, 1
    2, 1, 2
    3, 1, 3
    4, 1, 4
    etc. until all 21 members of worship are in the table
    22,,2, 1, (since Catherine Thompson is also a member of the setup committee))
    23, 3, 15 (since Maka Mukoki is on the refreshments committee)
    24, 2, 9 (since Joel is a memberof not jus tleader of the setup committee)

    MeetingID, TeamID, Date, Time, LeaderID(join to MemberID based on who led that specific meeting)), GeneralComments, Issues: NeedAssistancc (1=yes, 0=-no)
    1, 1, 10/31/03, 7:00pm, 1, “It was Generally a good meeting”,”No issues of concern”, 1

    Index#, MeetingID, MemberID, PresentTadyAbsent*

    PresentTardyAbsent Table
    0 = Absent
    1 = On Time
    2 = Late

    This can get much more complex – if you want - for example: if assistance is needed, then do you want to have a combo box with a drop down of the 5 possible types of assistnace? Or will they just put that in the comments? Chances are it’s not worth your programming that level of detail, as I would be surprised to see users using it, except in a rather formal, rigid setting (military or a hospital where someone’s life could depend on, say, whether you data entered the correct blood type).

    If leaders change frequently and it's pretty flexible you could omit the TeamLeadersTable and just have the option to fill in any member-name as the leader of a particular meeting

    as noted with TeamsAndLeadersTable and TeamsAndMembersTable
    you can just store the MemberID, LeaderID (integer) instead of the person's name. This is a little harder for those newer to programming, but you can set up a combo box on the table's datasheet view so you will be able to enter the names even if you edit the table. However if it is a small database with only a few people using it, then performance isn'ta big factor and you could put the actual name in these tables not the id number. This can be easier if you're just starting, though you have to be careful that the name is typed correctly and the same in each table. Tom Jones in one table won't join properly with Thomas Jones in another. Likewise Bob/Robert, Kathy/Cathy/Cathie/Katherine, etc. Then John Smiith (typo) and John Smith can cause problems. This is why using the index field is better. Dibs Dawson ID is 2 and 2 will always join with 2 so you don't have that concern of somewhere it being spelled Dawsen.

    Again there are ways to simplify this - but it's hard to know - if you have 30 committees each meeting weekly with average 20 person per committee then you might be best with a fully developed schema. But if you have just 2 committees, it may be more complex than you need. Of course that is also impacted by your and users comfort level with the more advanced vs. simpler schema. For example a phone number table could be added

    Index,MemberID, PHone#, PhoneType

    rather than having phone# in the Member table. This way you can have as many phones tracked as necessary. Or it could be broadened and be a contacttype (phone # or email address). However it is onemore table join and if you're not comfortable with the joins and building queries yet then you might just leave phon# in the membertable for now.

    I guess what I'm saying is there is no real "right' way. There are ways that are cleaner and better in theory. But if it makes it too complex for the people who have to oversee it ... then, sometimes, that is not the best way.

    Hopefully this helps.
    Last edited by mdr02125; 10-31-03 at 23:54.

  5. #5
    Join Date
    Oct 2003
    OH MY GOSH MARK!!!! ... I feel like I owe you money for this post!!!

    That is SO clearly laid out ... I KNEW it had to be something like this, but I JUST couldn't get my head around it. Being my first relational DB.

    Wow, again, I'm blown away by your genorousity on time on this. I owe you!

    Many, many MANY thanks!

  6. #6
    Join Date
    Oct 2003
    Boston, Mass. USA
    Hi Joel -
    glad it helped. Someday you'll have a chance to help someone else .... and when you are able, please pass the favor on. I'm only passing on a favor someone did for me....


  7. #7
    Join Date
    Oct 2003
    hey Mark, can I email you the access database I did ... to your spec's and you could check that I've done it correctly?

  8. #8
    Join Date
    Mar 2001
    Lexington, KY
    You don't need autonumbers on the TeamLeadersTable and TeamsAndMembersTable table. Also, suffixes of 'Table' is pretty redundant.


  9. #9
    Join Date
    Oct 2003
    Boston, Mass. USA
    The suffix of Table was mainly put in for best clarity for forum readers... though I know some who prefer suffixes like Tble, Frm, Qry, etc. to make VB code and macros easier to read.

    the autonumber indexes in those tables aren't essential, perhaps more a style issue. Well, unless you're dealing with upwards of 50k or 100k records, then I don't know for sure if it would help performance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts