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.
i'm sure you could find such resources, perhaps at your library or on amazon.com or half.com
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)
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
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.
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.