Integrating NISI I ICMS Reports in Access Database

Updated by Sarah Grace Rogers 10/12/2022

1. Make sure reports are pulled and sorted in an organized fashion (by site name and accession number).
a. Copy them into another folder for integration/assessment to keep the original reports separate.
2. Start with the excel workbook. You will need to format the data in the correct way in order to copy and paste it into
Access.
a. Create three more sheets in the workbook. Name the original “Original,” then the new ones “Needed
Information,” “Concatenated,” and “Formatted.”
b. Copy the “Original” into the “Needed Information” Sheet.
c. Delete the columns/fields that will not be needed in Access (the reason behind their deletion is listed in the
“General Information and Notes” section of the report):
i. Artist/maker
ii. Cataloger
iii. Class 1
iv. Collector
v. Condition
vi. Field Site
vii. Location
viii. Maintenance Cycle
ix. Object Status
x. Origin
xi. Quantity
xii. Site Name
xiii. Status Date
xiv. Storage Unit
xv. Type Name
xvi. Use Date
d. Copy the remaining columns into the “Concatenated” sheet.
i. This is where you will concatenate all the information to fit into the fields in Access.
ii. Reorder the columns in the following order:
1. Accession Number (highlight as “Checked Cell”; this will not be copied into Access but
will be recorded in the “Accession Information” box)
2. Catalog Number
3. Field Number
a. Move the “State Site Number” column in this space and rename it as “Field
Number”
4. Create a blank “Description” column next.
a. This will be concatenated with the following information:
i. Description
ii. Alternate name
iii. Object
iv. Class 2
v. Class 3
vi. Class 4
b. See instructions below on concatenating.
5. Weight
a. Copy and paste the “Measurements” column here and rename it “Weight.” The
content will be split into the weight and caliber later.
6. Count
a. Item count
7. Catalog Notes
a. This will be concatenated with the following information:
i. Color
ii. Condition description
iii. Material
iv. Cultural ID
v. Culture of use
 vi. NAGPRA
vii. Historical/cultural period
viii. Manufacturing date
ix. Place of manufacture
x. Identified by
xi. Identified date
b. See instructions below on concatenating.
8. Excavation Unit and Level
a. Copy and paste the “Within Site” column here and rename it “Excavation Unit
and Level.”
9. Measurements
a. This will be filled after splitting the calibers from the weight. You may create a
marker for it just as a reminder.
iii. Concatenating
1. How to
a. =CONCATENATE(firstcell&”,“&cell&”,“&cell&”,“&cell&”,“&cell&”,”lastce
ll&””)
b. Hit enter
c. Drag down to fill the column completely for every artifact. You may need to
wrap text to keep the boxes short.
d. MAKE SURE YOU DO NOT DELETE THE ORIGINAL COLUMNS. Keep
them and highlight them as “Good.”
2. Description
a. Description
b. Alternate Name
c. Object
d. Class 2
e. Class 3
f. Class 4
3. Catalog Notes
a. Color
b. Condition Description
c. Material
d. Cultural ID
e. Culture of Use
f. NAGPRA
g. Historical/Cultural Period
h. Manufacture Date
i. Place of Manufacture
j. Identified By
k. Identified Date
e. Copy the moved and concatenated data into the “Formatted” sheet.
i. Split Weight/Measurements
1. Insert a column on either side of the “Weight” column.
2. Highlight the weight column.
3. Go to the “Data” tab then “Text to Columns.” Choose “Delimited.” Check other and type
“G” in the space. Select each column and make sure they are “Text.” Finish and allow it
to paste.
4. Go through the columns and check to make sure it split correctly for everything. If there
are outliers, just correct them one-by-one: have the weight in one column and the caliber
in the other.
5. Go through and delete any other words/fillers in the spaces that do not contain any actual
data/numbers.
6. Move the column with the caliber measurements to the end of the sheet (after Excavation
Unit and Level) and rename it as “Measurements.”
7. Make sure the weight column is labeled as “Weight” still.
8. Delete the empty columns you created around “Weight.”
ii. Format Weight
 1. Highlight the column
2. Control-F
3. Do to the “Replace” tab
4. Type “__WT ”
5. Click “Replace All”
6. Go through and check that it deleted everything but the numbers. You may fix some
one-by-one if needed.
iii. Format Measurements
1. Highlight the column
2. Control-F
3. Do to the “Replace” tab
4. Type “Caliber (in): ” or any other variation of this
a. If there are not many (and they are not all in the same format), you may deleted
everything but the number by hand, one-by-one.
5. Click “Replace All”
6. Go through and check that it deleted everything but the numbers. You may fix some
one-by-one if needed.
iv. Double check other fields.
1. Remove any “Needed” fillers (typically show up in TMPLs).
2. Make sure the “Count” column is there.
3. Triple check the “Weight” and “Measurements” to make sure they only have numbers.
v. Format to Text
1. Highlight the whole sheet. Right click, and choose “Format cells,” then highlight “Text”
and click “OK.”
vi. Unwrap
1. Highlight the whole sheet and click the “Wrap Text” button twice to turn it off for
everything.
f. Make sure all of this is saved as you go.
g. Rename the workbook with “Formatted” (Ex: “358S_Formatted”)
h. Before moving to the Access file, you must set up the Access file.
3. Setting up the Access File
a. Type in the Accession Number in the lower left box.
i. All Accession Files have been created for all NISI I ICMS directories (except for TEST1). These
are listed below:
1. Siegeworks
a. NISI-00116.359S (main)
b. NISI-00116.356
2. Southwest Village
a. NISI-00115.358S (main)
b. NISI-00115.3581
c. NISI-00115.SWTAGS
3. Holmes Fort
a. NISI-00138.360 (main)
b. NISI-00138.3601
c. NISI-00138.HTAGS
4. Jail
a. NISI-00115.358.J (main)
b. NISI-00115.TAGS
5. Dozier
a. NISI-00116.359.D
6. Restroom
a. NISI-00118.684
b. Hit “Enter” a couple times.
c. Go to “Go to Data Entry Form…”
d. Click “Accession Form”
e. If an accession file has not been created:
i. Type in the EXACT accession number you search with in the “Accession Number” box at the top
of the “Accession Information.”
 ii. Name the collection in the “Collection Name” box.
1. For NISI I, all names are formatted as “NINETY SIX NATIONAL HISTORIC SITE,
[INSERT SITE NAME]”
iii. Click on the “Catalog Information” tab.
1. Input any key information in the “System” box.
a. For NISI I, this is a key to the data:

Field Number is the State Site Number.

Description is Description; Alternate Name; Object; Class 2; Class 3; Class 4.
Weight is in grams.
Notes are Color; Condition Description; Material; Cultural ID; Culture of Use;
NAGPRA; Historical/Cultural Period; Manufacture Date; Place of Manufacture;
Identified By; Identified Date.
Measurements are caliber in inches.

f. If the accession file is already created (which it should be):

i. Go to the “Catalog Information” tab.
ii. Right click one of the column headings.
iii. Choose “Unhide Fields”
iv. Uncheck the following:
1. Accession Number
2. Catalog ID
3. Date Excavated
4. Site Number
5. Material Type
6. Period
7. Barcode
8. Prov_id
9. Catalog_cf
10. Restriction ID
v. Click close.
vi. Ensure that it displays the following fields in order:
1. Catalog Number
2. Field Number
3. Description
4. Weight
5. Count
6. Catalog Notes
7. Excavation Unit and Level
8. Measurements
4. Go back to the Excel “Formatted” sheet and copy all the NISI and TMPA records beginning to end, Catalog Number
through Measurements. (Do not copy the TMPLs.)
5. Go back into Access, right click the first row of the first column, and paste.
6. Make sure the number pasted is the number of records from the report.
7. If it does not want to paste, reading the popup to understand why. Go back through the Excel sheet to proofread for any
errors.
8. If it pastes some, but not all, records, you can go to the Navigation Pane in Access and double click “Paste Errors” to
see what it did not paste. Then find those records in the Excel sheet to pinpoint the problem. Delete all the records in
Access, and re-paste them with the corrections.
9. MAKE SURE YOU SAVE IN ACCESS.
10. Also Save As Backup.
11. Pulling and Printing Assessment Forms:
a. Type in the accession number in the bottom left box and hit Enter several times.
b. Go to “Go to Reports…”
c. Go to “Assessment Report”
d. Click on “Sorted by Catalog Number”
e. Type the Accession Number (that you typed in the box) then click “OK”
f. Type your name (Ex: “S. Rogers”) then click “OK”
 g. Type “Acquisition Assessment” then click “OK”
h. Exit out of the Assessment Report Menu
i. Right click on the Assessment Reports
j. Export as a PDF
k. Save in appropriate file with appropriate name (Ex: “358S_AssessmentReport”)
l. Close out.
m. Go to where you saved it and open the PDF. Print.

General Information and Notes

● ICMS Fields categorized by Access Fields

o Accession Number—Accession Number (records at the top of the data sheet, not in the Catalog Information
tab

Catalog Number Catalog Number

Field Number State Site Number

Description Description
Alternate Name
Object
Class 2
Class 3
Class 4

Weight Measurements
*Remove the “__WT” and Caliber

Count Item Count

Catalog Notes Color

Condition Description
Material
Cultural ID
Culture of Use
NAGPRA
Historical/Cultural Period
Manufacture Date
Place of Manufacture
Identified By
Identified Date

Excavation Unit and Level Within Site

Measurements Caliber (inches)

*Only the number (Ex: “0.655”)*

● Fields deleted from original report

o Artist/maker (typically blank; double check)
o Cataloger (will be updated in ICMS as we go)
o Class 1 (already correct)
o Collector (already correct)
o Condition (already correct)
o Field Site (typically blank; check case-by-case)
o Location (already correct)
o Maintenance Cycle (will be updated in ICMS as we go)
o Object Status (already correct)
o Origin (already correct)
 o Quantity (same as item count)
o Site Name (already correct)
o Status Date (will be updated in ICMS as we go)
o Storage Unit (already correct)
o Type Name (same as alternate name)
o Use Date (typically blank; double check)
● Fields hidden in Access
o Date excavated
o Site number (does not work correctly when we use this field so we insert the site number into field site)
o Material type
o Period
o Barcode
o Prov_ID
Catalog_cf
o Restriction ID
o The following will reappear after refreshing in access, but that is normal; just make sure to hide them before
pasting the reports from Excel:
▪ Date excavated
▪ Site number
▪ Material type
▪ Period
● Considerations
o Fields that are correct and were not pulled in reports or integrated into Access
▪ Ctrl prop
▪ Center number
▪ Reproduction
▪ Catalog folder
▪ Other numbers
o Identified by and date
▪ In some but not all—double check each
o Field Site
▪ Typically blank but double check each
o Date Excavated
▪ Normally a field in Access but because it is not in ICMS/reports, it will not be included
o We may need to merge directories in the future—several are duplicated pieces of data.