I am setting up a database for our local Ronald McDonald House Charity to manage a list of contacts and donors, similar to a mailing list. The primary use of this database will be for mailing brochures, invitations, and other material about our fundraising events. The entities consist of Individuals, Households, and Businesses. All of the information fields are the same with each entity with the exception of Businesses that eliminates the first name field but contains an extra field for "Contact within Organization".
I have all the entities in one table using the LastName/Org field as a primary name identifier, and I have simplified the data entry process using a tabbed form to differentiate the Businesses fields. My basic reason for this is so that my overall report will list all entries alphabetically rather than by report section. In addition to the basic information, we have check boxes to catagorize each entry, and to link these entities to different funding campaigns (which can be several for each entity) as a means of filtering the list based upon the campaign we wish to promote.
Individuals and Businesses are fairly straight forward and can be done in one table, but with Households I have a dilemma. With multiple entities within a household who may individually have a connection to the charity, I need to uniquely identify each member of the household. I do not like the idea of entering each name separately in the table because of the pitfalls of data entry errors when last names and addresses are duplicated, etc. Should I create a table based upon a primary record which focuses on a "head of household" so to speak (the one side of the relationship), and then a linked table to contain any other members of the same household which contains only the information unique to that person (the many side of the relationship)? Or, should I create a table containing only the last name, address and pertinent data focused on "the location" (the one side), and use another table to link all the entities connected to that location (the many side)? My second question is how to encorporate both of these tables into a single report which would list out each separate entity for a complete mailing list, and then how to sort out all the records from both tables to compile the filtered mailing lists for our various campaigns. There are also times when I would want to combine the household names in an address for an invitation, like Mr. and Mrs. John Doe, or John and Mary Doe, rather than sending separate mailings to each individual within the household.
Any advice on the best way to structure this database would be most appreciated. I am familiar with creating complex forms for all this input, but I need a little help with the structure, and in developing the right reports to present the data.
And PS. 2 More questions: I have a form with a subform. When I open the form the focus is in the first field of the subform. I want the focus to be in the first field of the main form. How do I change that? Also, how do I replace the generic "999" with a default area code in a phone number mask?
Many thanks, Sandra
I have all the entities in one table using the LastName/Org field as a primary name identifier, and I have simplified the data entry process using a tabbed form to differentiate the Businesses fields. My basic reason for this is so that my overall report will list all entries alphabetically rather than by report section. In addition to the basic information, we have check boxes to catagorize each entry, and to link these entities to different funding campaigns (which can be several for each entity) as a means of filtering the list based upon the campaign we wish to promote.
Individuals and Businesses are fairly straight forward and can be done in one table, but with Households I have a dilemma. With multiple entities within a household who may individually have a connection to the charity, I need to uniquely identify each member of the household. I do not like the idea of entering each name separately in the table because of the pitfalls of data entry errors when last names and addresses are duplicated, etc. Should I create a table based upon a primary record which focuses on a "head of household" so to speak (the one side of the relationship), and then a linked table to contain any other members of the same household which contains only the information unique to that person (the many side of the relationship)? Or, should I create a table containing only the last name, address and pertinent data focused on "the location" (the one side), and use another table to link all the entities connected to that location (the many side)? My second question is how to encorporate both of these tables into a single report which would list out each separate entity for a complete mailing list, and then how to sort out all the records from both tables to compile the filtered mailing lists for our various campaigns. There are also times when I would want to combine the household names in an address for an invitation, like Mr. and Mrs. John Doe, or John and Mary Doe, rather than sending separate mailings to each individual within the household.
Any advice on the best way to structure this database would be most appreciated. I am familiar with creating complex forms for all this input, but I need a little help with the structure, and in developing the right reports to present the data.
And PS. 2 More questions: I have a form with a subform. When I open the form the focus is in the first field of the subform. I want the focus to be in the first field of the main form. How do I change that? Also, how do I replace the generic "999" with a default area code in a phone number mask?
Many thanks, Sandra
