Software to update database

**Summary** We currently have a database including over 400 workbooks. Each workbook contains the historical membership data for a specific Intergovernmental Organization (IGO) going back to 1816. Currently we have roughly 340 countries for which we have collected data. Recorded dates are used to generate longitudinal datasets within each workbook. Please see attachments for examples. The project will have three major components: I. Migration of the Excel workbooks into a Microsoft Access database. II. A GUI used to modify the current country list and repopulate the database. III. A GUI to consolidate the longitudinal data of each organization into multiple list datasets. ## Deliverables **Access Database** The coder will propose the process and format of the database. Necessary spreadsheets to import include 'Notes', 'Validation Data' (to update by hand and populate the longitudinal data), and "Longitudinal Data'. The format of these sheets within the database do not necessarily have to be the same as in the excel workbooks. **Country List Updater** Each IGO workbook has membership data for a list of roughly 340 countries. It is occasionally necessary to modify the list across all workbooks while preserving the historic data specific to the country. A. With regards to manipulating the country list, the software should be able to do the following: ? Show the current country list (loaded from an XLS file) ? Option to change the Name, Family, Birth, or Death years of a country ? Split a country into two or more countries ? Combine two or more countries ? Add new countries ? Remove countries ? Option to add an additional column or row of "COW codes" along side the countries. ? Option to add an additional column or row of "Family" along side the countries. Countries will be updated on the following sheets: 'Notes', 'Validation Data', and 'Longitudinal'. The sheets 'Membership Comparison', 'Comparison Summary', 'Comparison', 'COWraw', and 'COW Data' can be removed from the output. A final "Modified Country List" should be visible before the full process runs. B. After (or during) the update, the code must also repopulate database accordingly. *Note: VBA macros are currently used to populate sheets found within IGO workbooks (macros can be found in "IGO\_stateunit\[login to view URL]"):* *"ValidationDataPopulate"* directly populates the 'Validation Data' sheet from the 'Notes' sheet (using a VLOOKUP based equation) *"LongitudinalPopulate"* populates 'Longitudinal' sheet from 'Validation Data' with by putting an equation in each cell that follows the general logic of: ? A country receives a 1 for the years in which it is a full member ? A country receives a 2 for the years in which it is a partial member ? A country receives a 0 for the years in which it is in existence but not a member ? A country receives a "null" for the years in which it is not in existence ? All countries receive "nulls" for the years in which the IGO is not in existence These are time consuming macros (written by an inexperienced coder) and could likely be greatly improved. We would like the option of making the following slight changes to the logic: ? The ability to either give, or not give, credit to a country for the year it leaves an IGO (example: if Afghanistan leaves the UN in 1991, we should have the option of Afghanistan receiving a 1 in 1991, or receiving a 0) ? The ability for an IGO to be "active" (meaning all member states receive credit for that year) or "defunct" for the year it becomes defunct (this year is found in cell "C1" of the 'Validation Data' sheet). ? The ability for countries which are alive but not members of an IGO to receive either a 0 or a "null" ? The ability for country which goes from a full membership to a partial membership in an IGO to receive either a 1 or a 2 for that year (example: if a country transitions from full membership in an IGO in 1995 to partial membership, there should be the option for it to receive a 1 or a 2 for 1995). *Caveat: Due to limitations in the VBA macros used to populate the 'Longitudinal' worksheet certain workbooks have had to be modified by hand. **The code should have built into it a comparison phase, where, before updates, it looks for discrepancies between the current 'Longitudinal' sheet and what it would "expect" the sheet to be had it been generated automatically by the 'Validation Data' sheet.** Discrepancies should be noted in an output.* *If the coder can develop an improved method for dealing with states that are partial or full members more times then the current code can account for, this would be highly favorable.* Generating the updated Access Database will create new database file in a designated folder as opposed to overwriting the original. **Consolidated List Dataset** The output will be a one-sheet list dataset ordered by IGO name and Year (see attached files), pulled from the 'Longitudinal' sheet in each IGO workbook. There will be the option of exporting to one of three (or any combination) formats. 1. For the *IGOUnit* output the number of rows will be equal to number of Years times the number of IGOs, and the number of columns will be equal to the number of Countries plus any extra variables (see below). 2. For the *StateUnit* output the number of rows will be equal to the number of Years times the number of Countries, and the number of columns will be equal to the number of IGOs. 3. For the *Dyadic* output every possible country pair should be listed in the first two columns (first 4 columns if "COW Country Codes" are included") for each year. The subsequent column headers should be the IGO names. The data for each dyad-year should use the following logic: a. If either of the countries do not exist for that year, it coded as a "null" b. If both countries are in existence, but either are not members of a given IGO, it is coded as a "0" c. If both countries are full members (1) of an IGO, it is coded as a "1" d. If both countries are partial members (2) of an IGO, it is coded as a "2" e. If one country is partial (2) and the other full (1) in an IGO, it is coded as a "3" 4. For the *Aims* output the number of rows will be equal to the number of Years times the number of IGOs, and the columns will be equal to the number of Aims. An IGO will receive a 1 for each Aim it is coded as for every year it is in existence. States in the same family should not be compared. If the number of rows exceeds that allowed by excel for any of the list datasets, the output should be broken into multiple files. There should be the option to include COW Country Codes, Family names, Weights, Aims, Region, Budget, Staff, and Headquarters in the columns. The user should be prompted to upload the excel files for each that is chosen to be included (see attached files for format of these files). The user should also be able to choose the year range. There should be the option to exclude all partial country memberships. The user should be able to choose the output file format (XLS or CSV). An estimated time for completion should be calculated before the process is run. During the process a progress bar with estimated time to completion should be visible. Please familiarize yourself with the included files and examples and respond with any questions or proposals you may have. **Attached Files** Attached are example files for Inputs: Individual IGO workbooks Country List COW Codes Weights Aims Budget Headquarters Region Staff Example files for Outputs: IGOUnit_Output StateUnit_Output Aims_Output Dyad_Output Reference Files: IGO List IGO\_stateunit\_split (contains currently used macros) GUI_mockup (Incomplete. Suggestions are more than welcome)