Database Merge

2 MS Access Databases need to be merged there are 3 levels of access that need to be maintained duplicate data must be merged reports need to be maintained easy user interface ## Deliverables current db's are in MS Access 2000, we want to merge and move the data to a CRM. existing user levels and reports need to be created in the CRM. I want to use an Open Source LAMP application to do this. Project Overview So that our dedicated staff of environmental education professionals might continue to focus on the good work of fostering environmental stewardship, AFF seeks a database professional to merge our two home-grown databases into one database for organization-wide use. AFF currently has two primary databases: 1. the Alice Ferguson Foundation “Rolodex?? database, which holds: a. Hard Bargain Farm i. on-site education programs, including details about what AFF staff and schools participated, participant demographics, and logistical details b. AFF i. donor, membership, and other event participation data, including dates, amounts and details of charitable donations c. Trash Free Potomac Watershed Initiative/Potomac River Cleanup data i. Participant, field data 2. the Bridging the Watershed (BTW) database, which contains a. contact information for individuals, b. demographic data about field study participants; and c. event information This database is used to schedule field studies (events) and generate confirmation forms, etc., as well as to manage the data. These two databases need to be merged, and made more user-friendly so that: • Volunteers and staff can enter data without impacting the database functions/architecture; • Regular queries can be easily run for custom dates or ranges by staff other than database administrators; • New, relatively simple queries can be written by an intermediate level user; • Data entry at multiple locations can be efficiently synched across copies of the database (or another solution to our geographic challenge is provided); and • The database has enough flexibility to expand as programmatic uses increase and change. • Data can be separated by program or aggregated for organizational use. Project Specifications Program: Both databases are in Microsoft Access 2000. Most AFF staff run on a Windows XP platform with the Microsoft Office 2003 or 2007 package. Database Size/Scope: 1. AFF Rolodex: • 9,500 contacts in a table • 3,300 organizations in a table • 30 tables total, with varying levels of importance • Address elements such as states, counties, zip codes, etc are stored in separate tables • 11,000 links of contacts and organizations to events • 50 forms or subforms • 24 reports (some obsolete/infrequently used) • 250 queries • Replicated database • DesignMaster is 86MB 2. BTW Database: • 1,004 contacts in a table • 387 organizations in a table • 25 tables total, with varying levels of importance • Address elements such as states, counties, etc are stored in separate tables • 2607 links of contacts and organizations to events • 15 forms or subforms • 3 reports (some obsolete/infrequently used) • Approximately 280 queries • Database is 8.12 MB Standardization of Data: All contacts and organizations, regardless of program, must be consolidated in one place, with an indicator of what AFF program they’re affiliated with. Currently both databases have matching contact and organization tables, which has allowed transfer of this data from the BTW database to the AFF rolodex in the past. Data transfers should be verified Data Entry: As a small non-profit, AFF relies on staff and volunteers to do data-entry. As such, the format should be clear and easy to follow for new users of the database (possibly also new to Access). This front end should be separated from the back end of the database to protect the database structure from novice database users. Each segment of data (BTW, Trash, HBF, membership/donors) should have customized entry forms, based on programmatic needs. Security: The Rolodex is currently secured with a list of users at three levels of permissions; admin, donor information access, and general contact access. The merged database should maintain these security levels. Queries: AFF staff need easy access to statistical and programmatic data for reporting and evaluative purposes. This may require a merger of the participation tables in each database, though the fields are not currently identical. Reporting needs change over time, though some information needs remain fairly constant. The BTW database currently has a control panel that allows users with less familiarity with Access to run existing queries. The merged database should have an overall control panel to guide users to the functions they need. Some queries in the BTW database can be customized by the common user to run for different date ranges. This type of flexibility should be built into all appropriate queries in the merged database. Flexibility: Database needs to remain as open-ended as possible to allow for future expansion, including creation of new tables, queries, forms, and reports by staff database specialists should that become necessary. Simple Structure: The structure of the merged database should be as simple as is possible given our informational needs. The current databases have been adapted over time to fit the growing needs of our programs, and contain many ‘patches’ constructed to work around problems. Particular areas that might be more complicated than necessary include membership and addresses: Membership is currently set up so that John and Jane Doe, who are married, both link to a single membership number in a table that contains basic membership information, which is also linked to by each donation in the donor contributions table. This tracks the information that we need, but it becomes quite complicated for generating donation and donor lists, as well as address labels, etc. In the Rolodex, addresses are also tracked separately from the contacts and thus are linked to by the members of a household. This helps us to only edit one address when something changes, but becomes difficult in the querying process. A single primary household member is tracked, and links created between contacts to note those relationships. We would like to know whether we are on the right track with this structure, or if there is a better way to approach situations like these. Remote Access: The AFF Rolodex database is currently a replicated database because we have a number of staff who work outside of the main site (‘Hard Bargain Farm’), including our database managers, and AFF does not have uninterrupted access to a central server. AFF has a [login to view URL] account, through which we can use one shared computer at the office site, but this is only available to one user at a time, and will bump whoever is using that computer at the office. Remote Design Master level access is desired for more than one user. We seek guidance about whether there is a better way to approach this problem. Organization Education: Database professional will work with AFF’s database administrators to ensure that they have a full understanding of the merged database’s architecture and logic so that management of the database can continue to be conducted internally. Appropriate documentation of database structure and changes will be provided to AFF. Proposals Please send proposals, including a detailed scope of work, line item budget,