Background

A little known fact: I wouldn’t be a programmer today if I hadn’t first been an Archaeologist. Working through college and my early career as first a Lab Technician, and later, as the Laboratory Director for employee-owned SWCA Environmental Consultants’ Tucson office, I began programming our company databases to improve efficiency and organization.

When I began working in the lab to learn the procedures, I eventually realized that much of the data entry we were required to do duplicated efforts. As my knowledge and credibility at the company grew, I began to look at ways to build a comprehensive database system that would only require data entry once, capable of producing all the subsequent reports and forms required for curation from a single dataset.

Environment

Artifact and photography data for the company were stored in different Microsoft Access databases prior to development. The forms and reports required by the local museum for curation were provided in document form and filled out either by hand or manually on a computer.

My Contribution

To streamline the process, I designed and built a single Microsoft Access database that could be replicated for each project site. The new system would leverage shared data about the project to pre-populate repetitive data across all forms and centralized all project management to a single data archive.

To accomplish those goals, I taught myself incrementally how to program advanced features in MS Access using VBScript for Applications (a skillset I then went on to apply to Excel spreadsheet customizations as a Fundraising Campaign Director and to web development with ASP server-side scripting for personal and professional use).

Components

Artifact and Sample Data

The heart of the lab’s operations, each artifact and soil or pollen sample that was excavated, collected, or documented on-site would come to the lab for processing and data entry. The provenance and material data recorded would need to be translated from hand-written records to data worksheets within the MS Access database.

With the newly integrated system of data entry, at least a third of the repeating data columns could be pre-populated from centralized data.

Image Data

Similar to the artifact and sample data, each photograph taken in the field would be collected with provenance, subject, and photographer information. These records would then be transferred to a database.

While once stored in a separate database entirely, the new system allowed for all data in the project to be captured through a single portal, allowing for lab techs to reduce their level of effort by removing data columns that were common to all formats.

Box and Artifact Labeling

A part of preparation for project curation at the local museum or institutes included correctly cataloguing, labeling, and organizing all the artifacts and samples for the project. Artifact and box labels each had their own unique requirements. Prior to developing the new system, our procedures would print hundreds of blank copies for box labels, filling them in by hand as needed.

To improve this process, I added those same label templates to the new data system and then populated them with the necessary data. Boxes and artifacts could now be labeled by printing out the forms and distributing them as needed.

Curation Forms and Reporting

The final process for the archaeology lab was preparing all documentation and reports according to specifications laid out by the governing bodies and curation institutes. By moving these forms into our data system, we were able to automate all the reporting curation procedures by simply printing out the required forms for each project without further data entry or oversight.

Outcomes

The lab’s efficiency was vastly improved with this system and I shared the database with the Laboratory Director in the Phoenix office as well, moving toward a company-wide standard for all of SWCA Environmental Consultants.

Years later, I was visiting friends at our old SWCA offices when I was introduced to the current Lab Manager. She immediately knew my name and told me that she refused to update her lab computer because she was still relying on the data system I’d built 15 years ago. It was truly gratifying to learn that the system had withstood the test of time that long without further upgrades or maintenance.

More Case Studies