Compile data from multiple Access files into multiple excel files - 2

This project was successfully completed by diegogarcia0531 for $160 USD in 5 days.

Get free quotes for a project like this
Project Budget
$30 - $250 USD
Completed In
5 days
Total Bids
Project Description

Our project analyzes data regarding embassies for all countries since 1945.

The goal of this project will be to produce an application (in excel/access or desktop) to compile the data longitudinally (over years) for each country. The end result will be an excel workbook with approximately 340 sheets.

While the file system may seem cumbersome, the compile should be relatively straightforward. The file system for our project will consist of 67 access files (one for each year from 1945 to present), each containing approximately 340 tables (one for each country over these years). Each table records dyadic data on embassies.

We are currently in the middle of the data collection process which is being done in Excel and then converted to access. If an Excel based file system (with the same format as the Access one described above and below) is preferred to work with we are happy to discuss the possibilities.

Input (please see .mdb files and '[url removed, login to view]' in attached 'Inputs' folder for example)

1. The input Access (.mdb) files are of the following format:

---a. Each .mdb file represents one year

---b. Each .mdb file has around 340 tables (each representing one country)

---c. Each table has the following format:

------i. First column - Lists of all ~340 countries

------ii. Second column - Diplomatic Mission Code

------iii. Third Column - Focus

------iv. Fourth Column - Location

------v. Fifth through Ninth Columns - Leave and Entrance Dates

------d. The output will only concern itself with the first two columns (Country and Embassy).

2. There will also be a file which lists all countries and their birth and death years.

Output (please see ‘[url removed, login to view]’ for example)

1. The program should combine data for each year for a given country in a longitudinal spreadsheet (1945-2012). The name of the worksheet should be the country in question. The first column should have a list of all countries that the country in question has diplomatic relations with (i.e. any country that has a value in the "Embassy" column).

---a. The first row should have the years 1945 to 2012.

---b. Each subsequent row should copy the data for each country dyad for each year

------i. Remember: the name of the table in the input Access files becomes the name of the file of the output Excel file.

2. Code changes across years should be highlighted

---a. e.g. Embassy code in 2005 is 1, but in 2006 is 2

------i. Both cells should be highlighted

3. The Access files will be continually updated. When the code is run, only data found in the ‘Input’ folder should be found in the output

---a. e.g. If “Afghanistan, Pre Colonial” has no embassies in any of the .mdb files in the ‘Input’ folder, it should not have a tab in the ‘[url removed, login to view]’ file.

4. Years that are not found in the ‘Input’ folder should be grey in the ‘output’ file to indicate they are missing.

5. Data points that have an embassy attributed to a country for years either before they are “born” or after they are “dead” should be highlighted for that year.

---a. This pertains to both countries in the dyad.

---b. The country list and Birth/Death years will be continually updated

Completed by:

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online