If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Tables - Membership DB - advice needed
Hi. I have placed a few other posts in the new user forum and have received
some help. I've been using Excel. I jumped into Access before doing reading/research on things. Planning on starting again. Please bear with me as I tend to be very detailed. In responding please keep in mind that I'm completely new to Access and databases in general and need "dummy" instructions. The database will contain the membership for our American Legion post. This includes the Legion itself, the Auxiliary and the Sons along with a list of those we send courtesy copies of our monthly newsletter to. I won't give full details as to the changes that have been made or why. Right now all the members are in one table (including those we send the courtesy copies to). I know the table is a mess as it has too many check boxes (1 for each "group", there are 4), 1 for each type of membership (there are 5) and one for the mailing of the newsletter. I plan on breaking into seperate tables and need some detailed help. Here's my thinking and questions. Member Table: will include name (first, middle, last, suffix) address city state zip phone birthdate notes The primary key will be an auto-id field. I do have a member id field but it is not possible to use this as a primary key because #1 - when a new member joins they aren't given a member number until they are processed through Department & then National. We get supplemental rosters every 3 months with that information. and #2 - the people who get courtesy copies aren't members and therefore don't have a member id. Group Table: will include Auxiliary Legion Sons Courtesy Membership Type: will include Honorary Life Paid Life (those that have paid one fee for lifetime membership) 50+ (those with 50+ years are paid for, mostly in the Legion roster) 70+/20+ (those 70 & up with 20+ years in the Auxiliary, we pay for) Juniors (only applies to Auxiliary) (Note: Yes I know that you aren't supposed to use spaces or characters in the field names and I did read somewhere that you aren't supposed to start a field title with a number.) Question 1: The Member ID field. I was told that it should be in a seperate table because we have certain members that are "dual members". Some are dual Legion - SAL and some are dual Legion - Auxiliary. So they would have more than one member number. Exactly how do I do this without screwing up which number goes with which member? 2. How do I keep track of the paid information? In the original Excel file we were originally keeping paid info back to 2005 with each year it it's own column. That was getting a little annoying as the worksheet was just getting way to wide and we just started keeping the current year and the previous year. When I first setup Access, I just put in a column for the current year (named it 2008). I realized that wasn't going to work as we'd have to update every year, so I changed it to just paid. Thinking about it, I do think it would be best to have a way to keep track of at least the current year & previous year. Should I do a seperate table with this info? Is my thinking correct that if we did that, each year we'd add a new year and we could keep a history of the old years? Could this info be put into the table with the member numbers? Details please. 3. We do mail a newsletter out monthly. We only send one copy to each address. Now there are those who don't wish to receive a copy at all and there are those who receive it via E-mail. Currently I have a simple yes/no check box (but that doesn't help in figuring out who gets it via e-mail (I can't just look at my e-mail field because not everyone that I have an e-mail for, gets the newsletter via e-mail.) Where do I put this? Any and all suggestions are appeciated. Lisa |
#2
|
|||
|
|||
Tables - Membership DB - advice needed
OK, here's my take on it... should get you started:
tblMembers: MemberPK (PrimaryKey) strFirstName strMidInitial strLastName strSuffix strAddress1 strAddress2 strCity strState strZip strPhone strEmail dtDOB blnNewsLetter (Yes/No checkbox if newsletter is to be sent) strNewsLetterMethod (stores selection from a form dropdown of “Email, Post, etc”) memNotes tblMembership: (one:many relationship to tblMember) MembershipPK (PrimaryKey) intMemberFK (ForeignKey to tblMember) intMembershipType (ForeignKey to tblMembershipType) tblMembershipType: (one:many relationship to tblMembership) MembershipTypePK (PrimaryKey) strMembershipType (the names of the membership types) strMembershipTypeDesc (the descriptions of the membership types) tblGroup: (one:many relationship to tblMember) GroupPK (PrimaryKey) intMemberFK (foreign Key to tblMember) intGroupTypeFK (ForeignKey to tblGroupType) strMemberID (MemberID for each membership) tblGroupType: (one:many relationship to tblGroup) GroupTypePK (PrimaryKey) strGroupType (Names of the group types) strGroupTypeDesc (descriptions of the group types) tblPayment: (one:many relationship to tblMember) PaymentPK (PrimaryKey) intMemberFK (ForeignKey to tblMember) dtPaymentDate curPaymentAmount memPaymentNotes (allows entry of notes or special instructions) You will then use forms to edit/add members and their information. Hope this helps! Lisa - NH wrote: Hi. I have placed a few other posts in the new user forum and have received some help. I've been using Excel. I jumped into Access before doing reading/research on things. Planning on starting again. Please bear with me as I tend to be very detailed. In responding please keep in mind that I'm completely new to Access and databases in general and need "dummy" instructions. The database will contain the membership for our American Legion post. This includes the Legion itself, the Auxiliary and the Sons along with a list of those we send courtesy copies of our monthly newsletter to. I won't give full details as to the changes that have been made or why. Right now all the members are in one table (including those we send the courtesy copies to). I know the table is a mess as it has too many check boxes (1 for each "group", there are 4), 1 for each type of membership (there are 5) and one for the mailing of the newsletter. I plan on breaking into seperate tables and need some detailed help. Here's my thinking and questions. Member Table: will include name (first, middle, last, suffix) address city state zip phone birthdate notes The primary key will be an auto-id field. I do have a member id field but it is not possible to use this as a primary key because #1 - when a new member joins they aren't given a member number until they are processed through Department & then National. We get supplemental rosters every 3 months with that information. and #2 - the people who get courtesy copies aren't members and therefore don't have a member id. Group Table: will include Auxiliary Legion Sons Courtesy Membership Type: will include Honorary Life Paid Life (those that have paid one fee for lifetime membership) 50+ (those with 50+ years are paid for, mostly in the Legion roster) 70+/20+ (those 70 & up with 20+ years in the Auxiliary, we pay for) Juniors (only applies to Auxiliary) (Note: Yes I know that you aren't supposed to use spaces or characters in the field names and I did read somewhere that you aren't supposed to start a field title with a number.) Question 1: The Member ID field. I was told that it should be in a seperate table because we have certain members that are "dual members". Some are dual Legion - SAL and some are dual Legion - Auxiliary. So they would have more than one member number. Exactly how do I do this without screwing up which number goes with which member? 2. How do I keep track of the paid information? In the original Excel file we were originally keeping paid info back to 2005 with each year it it's own column. That was getting a little annoying as the worksheet was just getting way to wide and we just started keeping the current year and the previous year. When I first setup Access, I just put in a column for the current year (named it 2008). I realized that wasn't going to work as we'd have to update every year, so I changed it to just paid. Thinking about it, I do think it would be best to have a way to keep track of at least the current year & previous year. Should I do a seperate table with this info? Is my thinking correct that if we did that, each year we'd add a new year and we could keep a history of the old years? Could this info be put into the table with the member numbers? Details please. 3. We do mail a newsletter out monthly. We only send one copy to each address. Now there are those who don't wish to receive a copy at all and there are those who receive it via E-mail. Currently I have a simple yes/no check box (but that doesn't help in figuring out who gets it via e-mail (I can't just look at my e-mail field because not everyone that I have an e-mail for, gets the newsletter via e-mail.) Where do I put this? Any and all suggestions are appeciated. Lisa -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
#3
|
|||
|
|||
Tables - Membership DB - advice needed
I have been running electronic databases for organizations for 21 years, and
currently do it for six. Also have had to deal with every issue that you describe plus other more complex ones. Sub-members to members, an organizaiton with current members who have been in for 60 years and I know their membership status for each of those 60 years) In my opinion (and this is where those immersed in DB design theory may disagree with those that have really been doing this for small / medium sized volunteer organizations): The design starts with asking these questions: 1. What are the main uses of this information system? Record current status? Create reports? Long term / historical record keeping? Print Badges? Drive the newsletter mailing? 2. In the context of your answers to #1, and, yes, knowing all of those variations that you talk about, is there sort of one core status at the center of all of these? Like (being deliberately vague) "member", or "gets the newsletter". 3. How big is the organization? With most organizations, the answer to #1 is "many of the above" and the answer to #2 is "yes, it's "member" (of some type)", and to #3 it's small or medium sized volunteer organizaiton. For them, it's one big table for all of the information that you describe. Make a field for each year (than needs to be tracked" for that main status (e.g. "member" , "paid"). And yes, this means that each year you will be making minor changes to everything. And a field for each of those statuses that you describe. Those that are mutually exclusive can be combined. If you want to record more extensive information (other than that main status through the years) on individual, do that through linked tables. For example, a "history" table which records instances of them holding an office over the last 60 years, or a history of donations etc.. If the answer to #2 is "no" then what you really have is separate organizations / tables which merely draw from a common list of people. In that case, makke one "people" table (with numbers for them) which has their non-organization data (name, address etc.) with a primary key(e.g. "persnum") and a separate table for each organization which links to / draws from the people table. And that organization table has fields for everything that it needs to track. Fred "Lisa - NH" wrote: Hi. I have placed a few other posts in the new user forum and have received some help. I've been using Excel. I jumped into Access before doing reading/research on things. Planning on starting again. Please bear with me as I tend to be very detailed. In responding please keep in mind that I'm completely new to Access and databases in general and need "dummy" instructions. The database will contain the membership for our American Legion post. This includes the Legion itself, the Auxiliary and the Sons along with a list of those we send courtesy copies of our monthly newsletter to. I won't give full details as to the changes that have been made or why. Right now all the members are in one table (including those we send the courtesy copies to). I know the table is a mess as it has too many check boxes (1 for each "group", there are 4), 1 for each type of membership (there are 5) and one for the mailing of the newsletter. I plan on breaking into seperate tables and need some detailed help. Here's my thinking and questions. Member Table: will include name (first, middle, last, suffix) address city state zip phone birthdate notes The primary key will be an auto-id field. I do have a member id field but it is not possible to use this as a primary key because #1 - when a new member joins they aren't given a member number until they are processed through Department & then National. We get supplemental rosters every 3 months with that information. and #2 - the people who get courtesy copies aren't members and therefore don't have a member id. Group Table: will include Auxiliary Legion Sons Courtesy Membership Type: will include Honorary Life Paid Life (those that have paid one fee for lifetime membership) 50+ (those with 50+ years are paid for, mostly in the Legion roster) 70+/20+ (those 70 & up with 20+ years in the Auxiliary, we pay for) Juniors (only applies to Auxiliary) (Note: Yes I know that you aren't supposed to use spaces or characters in the field names and I did read somewhere that you aren't supposed to start a field title with a number.) Question 1: The Member ID field. I was told that it should be in a seperate table because we have certain members that are "dual members". Some are dual Legion - SAL and some are dual Legion - Auxiliary. So they would have more than one member number. Exactly how do I do this without screwing up which number goes with which member? 2. How do I keep track of the paid information? In the original Excel file we were originally keeping paid info back to 2005 with each year it it's own column. That was getting a little annoying as the worksheet was just getting way to wide and we just started keeping the current year and the previous year. When I first setup Access, I just put in a column for the current year (named it 2008). I realized that wasn't going to work as we'd have to update every year, so I changed it to just paid. Thinking about it, I do think it would be best to have a way to keep track of at least the current year & previous year. Should I do a seperate table with this info? Is my thinking correct that if we did that, each year we'd add a new year and we could keep a history of the old years? Could this info be put into the table with the member numbers? Details please. 3. We do mail a newsletter out monthly. We only send one copy to each address. Now there are those who don't wish to receive a copy at all and there are those who receive it via E-mail. Currently I have a simple yes/no check box (but that doesn't help in figuring out who gets it via e-mail (I can't just look at my e-mail field because not everyone that I have an e-mail for, gets the newsletter via e-mail.) Where do I put this? Any and all suggestions are appeciated. Lisa |
#4
|
|||
|
|||
Tables - Membership DB - advice needed
Hi StrayBullet,
Thanks...but I do have a few questions. Since being very new I don't know what certain things mean. What a str, dt (date?), bin, mem (memo?), int and cur (current?). Are these things you type into the field name or is it specifying a type of field setting? In the tblMembers, the MemberPK. If you meant that the Member ID # is the PK, that will NOT work as I had previously mentioned. I will have to use an auto-number field for the PK. Was going to name it PID or something so that I won't get confused with the Member ID field. 2nd tblMembers question.....NewsLetterMethod. You say it stores info from a form dropdown. A form drop down on the table? Or is this something that happens once I start creating forms? Also would that mean that I can't enter this info until after I have created the forms and I'd have to go through each member via the form to input the correct info? Bear with me here as now I've gotten confused again. tblMembership: intMemberFK (foreign key to tblMember...is this the primary key field from the tblMember?) Is the MembershipPK here an a auto-id field? intMembershipType foreign key to tbleMembershipType I assume is the PK in that table? Part of what's confusing me on the membership table is that I thought (not sure I can explain correctly) that it had to have equal info. If the FK is the PK from the member table, that's close to 1000. the MembershipType FK to the MembershipType table would have only 5. tblMembershipType: MembershipTypePK - auto-id field? I just answered a question myself by actually reading furthur. In the tblGroup, I see that's where the actual MemberID number would go. So the PK in the tblMember is an auto-id field. I assume the GroupPK field is an auto-id field? tblGroup: GroupTypePK another auto-id field I assume? tblPayment: Payment PK again another auto-id? Probably wouldn't put the payment amount. We just need to know if & when they paid the current year. Also how can I keep track of more than one year? We like to keep data for at least the current year & previous year. Last question for now. It looks like some of this data will only be able to be entered via the forms so that I'm sure the proper info gets into the proper place....PLEASE tell me that I can at least import from Excel the name, address, phone, e-mail & DOB information? My plan was to figure out what fields would be in the main table and then edit the information that I exported out to Excel to match that so that I could import it into a new table and then add the MemberPK field to it. Is that ok? I don't want to have to type in all the information for almost 1000 records. Or do I really need to setup all the tables first and then enter all data via the forms.....I'll be here forever. Lisa |
#5
|
|||
|
|||
Tables - Membership DB - advice needed
Hi Fred,
1. What are the main uses of this information system? Record current status? Create reports? Long term / historical record keeping? Print Badges? Drive the newsletter mailing? In reading further, yes the answer is many of the above. First off, I am the membership chairman (along with secretary & treasurer) for our American Legion Auxiliary. Originally I was keeping an Excel file for this information and sending updates to the person who was in charge of the post newsletter at that time. My husband took over as Post Editor and I maintain the mailing list. But it's more than that. I'm now actually maintaining the full rosters for the whole post. A main file (that is NOT touched), is kept on the post computer just in case anything were to ever happen to any of the membership chairmen. Seperate Excel files are broken out of that file for them to edit. The SAL is doing great as they highlight anything that they've changed or added so we can update the main file. The Legion isn't quite as good about this yet. So we use it to keep track of all 3 organizations. It is helpful if we keep at least the current year & previous years payment information. We do a newsletter (so there are also a list of people not members who get this). We don't necessarily need to print reports that often but we do need to occasionally print out a roster by organization. We don't necessarily need to have a long term history as the "paper" rosters are kept for this. 2. In the context of your answers to #1, and, yes, knowing all of those variations that you talk about, is there sort of one core status at the center of all of these? Like (being deliberately vague) "member", or "gets the newsletter". It used to be just for the purpose of sending out the newsletter (except for the Auxiliary part). Now it's very important that it keeps full track of the membership and to print the labels for the newsletter. (Note: I also print laels out at the begining of each year for each organization for sending out membership cards.) 3. How big is the organization? The total between all 3 "groups" and the courtesy people is 1000. After playing with the program and changing things around. I don't like the all data in one table concept. Too many check boxes and too easy to screw something up I think. Also I want to avoid having to re-do queries & forms every year. If the answer to #2 is "no" then what you really have is separate organizations / tables which merely draw from a common list of people. In that case, makke one "people" table (with numbers for them) which has their non-organization data (name, address etc.) with a primary key(e.g. "persnum") and a separate table for each organization which links to / draws from the people table. And that organization table has fields for everything that it needs to track. I appreciate the info. I'm leaning more toward the setup the StrayBullet mentioned as it was actually closer to what I was thinking about breaking things down to after doing reading/research. It's sorta making a little more sense to me now. I'm sure many more questions are going to arise as I go along. Especially once I "hit" forms. Lisa |
#6
|
|||
|
|||
Tables - Membership DB - advice needed
I have another question/possible problem. I went into the Excel file that I
had exported. I moved the stuff that will go into the main Members table into a seperate worksheet and moved them around to be in the order I wanted. I renamed the fields as well. Once that was ready, I opened a blank Access Database. I imported the info I just set up into a new table. It asked to add an auto-number field and make it the PK and I did so and then renamed it to MemberPK. That worked fine. I then created the other tables (Membership, MembershipType, Group and GroupType....I decided to hold off on the Payment one for the time being). I input the membership types (honorary, paid life, etc) into the MembershipType table. I then input the group types into the GroupType table. I then clicked on relationships and figured out how to relate these tables to one another. That seemed to work ok. I then thought that I could copy & paste the list of member #'s over from Excel into the MemberID field in the Group table. It said it imported them but nothing showed up. I tried a few different ways and nothing. 1. Do I have to wait to input the member #'s until I have forms setup? 2. When I created the members table....in Excel I edited out the listings for the dual members. We have 1 dual Legion - Auxiliary member and 4 dual Legion - SAL members. The are listing once per "group" which means they were listed twice in the All members sheet. Now this is where some confusion sets in. I was told that in your main table that you only have one person listed once. Now that means that the PK in that table is going to have 5 less than the memberID field in the Group table. Am I over thinking this? 3. This may be overthinking at this point as well (or it could be because it's 1:30pm and I haven't had anything to eat all day).....How do I deal with these dual members? Do I need to have two listings for each one in the main member table? Just when I figure one thing out, I get confused on something else.... Lisa |
#7
|
|||
|
|||
Tables - Membership DB - advice needed
Thanks...but I do have a few questions. Since being very new I don't know
what certain things mean. What a str, dt (date?), bin, mem (memo?), int and cur (current?). Are these things you type into the field name or is it specifying a type of field setting? str, dt, bin, mem, int and cur are prefixes. They refer to the field type (str = string, dt = date, mem = memo, int = integer and cur = currency. Use of prefixes when creating table fields can be helpful in the future when you are designing queries, forms, etc. In the tblMembers, the MemberPK. If you meant that the Member ID # is the PK, that will NOT work as I had previously mentioned. I will have to use an auto-number field for the PK. Was going to name it PID or something so that I won't get confused with the Member ID field. Yep! The MemberPK is an autonumber field – simply a unique identifier Of course, you can name it anything you like... I would however suggest something that specifies its origin is tblMember. 2nd tblMembers question.....NewsLetterMethod. You say it stores info from a form dropdown. A form drop down on the table? Or is this something that happens once I start creating forms? Also would that mean that I can't enter this info until after I have created the forms and I'd have to go through each member via the form to input the correct info? Yes, the intent is that the information would be stored here. While under daily circumstances, you would enter new data via a form, entering existing data would depend on your method (import or direct entry – direct entry into the table could be done, so long as the entries are consistent. The values entered (ex: Email or Post) would then be the rowsource for a combobox aka dropdown on your entry form, with the controlsource set for the strNewsLetterMethod field. This way, anytime you make a selection, it will be stored in the proper field. Bear with me here as now I've gotten confused again. tblMembership: intMemberFK (foreign key to tblMember...is this the primary key field from the tblMember?) Is the MembershipPK here an a auto-id field? intMembershipType foreign key to tbleMembershipType I assume is the PK in that table? Each of the PrimaryKeys for the tables above is autonumber type. In the case of intMemberFK, it is storing the MembershipPK. This is the basis for its relationship with tblMember. The one to many relationship allows multiple instance of the MemberPK in tblMembership. Additionally, intMembershipTypeFK would store the MembershipTypePK, in effect creating a many to many relationship between tblMember and tblMembershipType (meaning that while not required, each member could theoretically have multiple memberships as indicated in the original post) Part of what's confusing me on the membership table is that I thought (not sure I can explain correctly) that it had to have equal info. If the FK is the PK from the member table, that's close to 1000. the MembershipType FK to the MembershipType table would have only 5. tblMembership would store multiple instances of MembershipTypePK, creating a one to many relationship. Multiple people would have the same membership type, so each of those 5 membership types could appear multiple times in tblMembership, for each member. tblMembershipType: MembershipTypePK - auto-id field? I just answered a question myself by actually reading furthur. In the tblGroup, I see that's where the actual MemberID number would go. So the PK in the tblMember is an auto-id field. I assume the GroupPK field is an auto-id field? tblGroup: GroupTypePK another auto-id field I assume? Yes, each of the PK fields are autonumber. tblPayment: Payment PK again another auto-id? Probably wouldn't put the payment amount. We just need to know if & when they paid the current year. Also how can I keep track of more than one year? We like to keep data for at least the current year & previous year. intMemberFK is storage of the MemberPK, again creating a one to many relationship with tblMember. This allows any number of payment records for each member. Last question for now. It looks like some of this data will only be able to be entered via the forms so that I'm sure the proper info gets into the proper place....PLEASE tell me that I can at least import from Excel the name, address, phone, e-mail & DOB information? Import should be no problem, so long as the information is of the proper type. Excel and Access play very well together! My plan was to figure out what fields would be in the main table and then edit the information that I exported out to Excel to match that so that I could import it into a new table and then add the MemberPK field to it. Is that ok? I don't want to have to type in all the information for almost 1000 records. Or do I really need to setup all the tables first and then enter all data via the forms.....I'll be here forever. Setting up the tables first is definitely the way to go. One way I’ve done it in the past is to import the Excel sheet as its own table with an autonumber PK added. Then use queries to create the related tables, including the necessary fields and the PK into the FK field, copying the data into the new tables. After the data is checked and confirmed to have copied over properly, you can actually delete the copied fields from the first table, removing the duplicate data. That first table then would be your tblMember. I’ve set up a layout sample to help you visualize the relationships. It’s in pdf format, www.a2zpa.com/Relationships-for-MembershipDB.pdf -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
#8
|
|||
|
|||
Tables - Membership DB - advice needed
Lisa - NH wrote:
I have another question/possible problem. I went into the Excel file that I had exported. I moved the stuff that will go into the main Members table into a seperate worksheet and moved them around to be in the order I wanted. I renamed the fields as well. Once that was ready, I opened a blank Access Database. I imported the info I just set up into a new table. It asked to add an auto-number field and make it the PK and I did so and then renamed it to MemberPK. That worked fine. I then created the other tables (Membership, MembershipType, Group and GroupType....I decided to hold off on the Payment one for the time being). I input the membership types (honorary, paid life, etc) into the MembershipType table. I then input the group types into the GroupType table. I then clicked on relationships and figured out how to relate these tables to one another. That seemed to work ok. I then thought that I could copy & paste the list of member #'s over from Excel into the MemberID field in the Group table. It said it imported them but nothing showed up. I tried a few different ways and nothing. 1. Do I have to wait to input the member #'s until I have forms setup? The MemberID could be entered so long as intMemberFK (the primary key number from tblMember for each member) is entered as well. I suggest using a query. 2. When I created the members table....in Excel I edited out the listings for the dual members. We have 1 dual Legion - Auxiliary member and 4 dual Legion - SAL members. The are listing once per "group" which means they were listed twice in the All members sheet. Now this is where some confusion sets in. I was told that in your main table that you only have one person listed once. Now that means that the PK in that table is going to have 5 less than the memberID field in the Group table. Am I over thinking this? tblGroup will store intMemberFK (the primary key number from tblMember for each member) as many times as is necessary (one member can be in a single group or in multiple groups) 3. This may be overthinking at this point as well (or it could be because it's 1:30pm and I haven't had anything to eat all day).....How do I deal with these dual members? Do I need to have two listings for each one in the main member table? Relax, take a breather and come back to the database after you've had a chance to eat. You'll be fine I'm sure. Just when I figure one thing out, I get confused on something else.... Lisa -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
#9
|
|||
|
|||
Tables - Membership DB - advice needed
Hi Lisa,
From what I can see, StrayBullet's solution is a more expert way to do what what you are trying to to do. Hopefully it will work well for you. In a lot of years of doing this for volunteer organizations, I've seen that "more expert" can be a minus or a plus. Hopefully for you it will be a 100% plus. If any more questions, would be happy to answer.....I probably won't be monitoring this thread unless you let me know at tureks at ameritech dot net. Sincerely, Fred "Lisa - NH" wrote: Hi Fred, 1. What are the main uses of this information system? Record current status? Create reports? Long term / historical record keeping? Print Badges? Drive the newsletter mailing? In reading further, yes the answer is many of the above. First off, I am the membership chairman (along with secretary & treasurer) for our American Legion Auxiliary. Originally I was keeping an Excel file for this information and sending updates to the person who was in charge of the post newsletter at that time. My husband took over as Post Editor and I maintain the mailing list. But it's more than that. I'm now actually maintaining the full rosters for the whole post. A main file (that is NOT touched), is kept on the post computer just in case anything were to ever happen to any of the membership chairmen. Seperate Excel files are broken out of that file for them to edit. The SAL is doing great as they highlight anything that they've changed or added so we can update the main file. The Legion isn't quite as good about this yet. So we use it to keep track of all 3 organizations. It is helpful if we keep at least the current year & previous years payment information. We do a newsletter (so there are also a list of people not members who get this). We don't necessarily need to print reports that often but we do need to occasionally print out a roster by organization. We don't necessarily need to have a long term history as the "paper" rosters are kept for this. 2. In the context of your answers to #1, and, yes, knowing all of those variations that you talk about, is there sort of one core status at the center of all of these? Like (being deliberately vague) "member", or "gets the newsletter". It used to be just for the purpose of sending out the newsletter (except for the Auxiliary part). Now it's very important that it keeps full track of the membership and to print the labels for the newsletter. (Note: I also print laels out at the begining of each year for each organization for sending out membership cards.) 3. How big is the organization? The total between all 3 "groups" and the courtesy people is 1000. After playing with the program and changing things around. I don't like the all data in one table concept. Too many check boxes and too easy to screw something up I think. Also I want to avoid having to re-do queries & forms every year. If the answer to #2 is "no" then what you really have is separate organizations / tables which merely draw from a common list of people. In that case, makke one "people" table (with numbers for them) which has their non-organization data (name, address etc.) with a primary key(e.g. "persnum") and a separate table for each organization which links to / draws from the people table. And that organization table has fields for everything that it needs to track. I appreciate the info. I'm leaning more toward the setup the StrayBullet mentioned as it was actually closer to what I was thinking about breaking things down to after doing reading/research. It's sorta making a little more sense to me now. I'm sure many more questions are going to arise as I go along. Especially once I "hit" forms. Lisa |
#10
|
|||
|
|||
Tables - Membership DB - advice needed
Hi StrayBullet,
str, dt, bin, mem, int and cur are prefixes. They refer to the field type (str = string, dt = date, mem = memo, int = integer and cur = currency. Use of prefixes when creating table fields can be helpful in the future when you are designing queries, forms, etc. Thanks, I appreciate that info. Yep! The MemberPK is an autonumber field – simply a unique identifier Of That's what I thought. Yes, the intent is that the information would be stored here. While under daily circumstances, you would enter new data via a form, entering existing Yes I know that once things are setup, you enter data via the forms. Each of the PrimaryKeys for the tables above is autonumber type. In the case "clipped" relationship between tblMember and tblMembershipType (meaning that while not required, each member could theoretically have multiple memberships as indicated in the original post) I figured that out after looking at things for a while. I see you saw responded to the other post I put in earlier about my continued confusion about the dual members. I'll check that as soon as I finish this. tblMembership would store multiple instances of MembershipTypePK, creating a one to many relationship. Multiple people would have the same membership type, so each of those 5 membership types could appear multiple times in tblMembership, for each member. Sorry, I'm just not getting it right now. Maybe it's better explained in the other response I haven't gotten to yet. intMemberFK is storage of the MemberPK, again creating a one to many relationship with tblMember. This allows any number of payment records for each member. Ok, I'll probably better understand that once I figure the rest of this out and get to that point. Import should be no problem, so long as the information is of the proper type. Excel and Access play very well together! Yep, I did try it with the tblMembers and it worked. Setting up the tables first is definitely the way to go. One way I’ve done it in the past is to import the Excel sheet as its own table with an autonumber PK added. Then use queries to create the related tables, including the necessary fields and the PK into the FK field, copying the data into the new tables. After the data is checked and confirmed to have copied over properly, you can actually delete the copied fields from the first table, removing the duplicate data. That first table then would be your tblMember. I’ve set up a layout sample to help you visualize the relationships. It’s in pdf format, www.a2zpa.com/Relationships-for-MembershipDB.pdf Hmmmmm queries to create tables. Don't know if that would be easier or not as I don't know how to do that type of query. Confusion setting in again...lol! From the Excel file.....would this mean that I could include the MemberID field and maybe if so inclined create a field for GroupType and MemberType and specify what's what there. Then import into Access and use a query to build the other tables, then remove those fields from the tblMembers? If so that might be easier and how the heck do I do a query to make a table? I figured out how to do a regular query to show only those members I wanted. Thanks for the PDF. Now onto the next messages. Thanks for the help and being patient with me. Looking forward to your response. Lisa |
Thread Tools | |
Display Modes | |
|
|