---
title: "Historic Assay Loader Extension"
canonical: "https://servicedesk.maxgeo.com/space/DataShed4/1665105933/Historic%20Assay%20Loader%20Extension"
format: markdown
---
> Macro (toc)

## > Macro (anchor)

Format Import Data

The historic assay loader can import data for a single lab and a single method for each element at a time, this means that the original data will need to be broken down into;

- Data from each different laboratory
- Elements with different methods

![image](media://fe41e61a-1e98-4760-88d8-11b673b4d5f7)

The example in the picture is in Access but can also be done in Excel using a different worksheet for each set of data.

The easiest way is to break the data into each different laboratory and method, this will reduce errors in loading the data. It is advisable to also name the worksheets or tables with the laboratory name and the method as shown above to reduce confusion when loading the data.

For each element it is best to have the element and the unit of measurement in the header (i.e. Ag_ppm, Al_Pct) as this will make it easier during the loading process.

Also recommended to add a batch number field, in the example above this is called Lab_Job_No, you can create batch numbers during the loading process but it is recommend doing this at this stage to make life a little easier during loading.

![image](media://9ad0cebb-cb75-4b0f-a4bf-e9322193aaff)

The historic assay loader will only accept external data from an Access file .mdb format, this means either importing the worksheets into a suitable database or saving an existing database in a .mdb format. > Macro (anchor)




## Setup Historic Loaded

Copy the historic assay loader into the same folder as your data, then change its name to include the client and a date. Found in the RDS D:\ Extensions.

MDS_v466_Historical_Assay_Loading_64Bit.accdb

To

Maxgeo_Historic_Assay_Loading_241219.accdb

This is to stop an altered database being copied over the original or accidently changing the original.

![image](media://a04d1e37-e358-485c-9fc8-80546e2fac1e)

When you open the database it may give a warning about blocking macros.



![image](media://835d9d5f-9d83-4b94-9488-8f2e70e03a94)

If this occurs close the database and right click on the file and open the properties, then unblock the file at the bottom of the general tab by ticking Unblock and hitting apply.

This will make this ‘Security:’ section disappear.

Reopen the file and the message should not appear.




![image](media://285b5f4e-d226-4e01-a40e-e57bbb913a25)

Next click on the arrow next to the Tables and select Forms.

Then double click on ‘DBA Tools’

In the ‘Linked Tables’ select the database you are loading the data into in the ‘ODBC:’ dropdown menu.

Click ‘Populate >>’

This will link the historic loader to the MDS database.

## > Macro (anchor)

Historic Loader

![image](media://b167b69e-b78c-4061-8038-67ddaf32dada)

Double click on extASS_HistoricLoader_SUB and the form for Normalise Historic Assays will load.

### > Macro (anchor)

Add Library Data

![image](media://03a926b6-9288-4808-b607-a8a7b5c9430f)

Before attempting to load the assay data you will need to check that all the library data is present, the main libraries used in assay data is found in the libraries tab.

#### Laboratories

The first library to check is the Labs library and make sure that the laboratories exist in the library.

Add any missing laboratories to the library lab codes. If adding a new laboratory you can often find information to fill out the table by ‘googling’ the laboratory. The lab code does not need to match the importing data exactly as this field will be selected from the existing laboratories in the library during loading.

#### Lab Element Alias

Check if the laboratory element alias exists in the library, if any are missing then you can add them to this table.

#### Lab Methods

Check to see if all the laboratory methods are present for each laboratory, if any are missing add them to this library.

Method codes can be found in //Corp Data/CorpData General/ Templates/Outsourcing/Lab Method Codes

### > Macro (anchor)

Loading Data

![image](media://9b9d1ff1-45e8-46e5-b773-3233a8d7c5c6)

1. Select the ‘External Access Database’ then select the access database (.mdb) in the top dropdown menu and then select the table in the bottom dropdown menu.
2. The source file should automatically select the source file name but can be changed.
3. Select the Sample ID field if not automatically selected.
4. Select the lab code for the data.
5. If there is a batch number field in the data select it in the drop down box next to the ‘YES’ radio button, if there is no batch number field select the ‘NO’ radio button and create a batch number for this data, it will automatically use the source file name.
6. Detection limits, not recommended as this will set the detection limits for all the elements during the loading process.
7. Select the element to be loaded there are two options for loading the elements.

#### Single elements Loading

Select the element to be loaded the adjust the data in section 8 for that element and then select ‘Append Samples’ in section 9 and single element will be loaded.

#### Multiple elements Loading

![image](media://6b347714-320b-4d1a-b49e-f80c9ba65142)

 Select multiple elements to load using ‘shift’ or ‘Ctrl’ to highlight the element and click ‘Populate Element Details’ at the bottom of section 7.


![image](media://3b9e9b1c-c505-440a-86e1-1a892ddb0c54)

Go to the ‘Element Details’ tab and the table should be populated except for UnitCode which will need to have data entered (Pct = %), also if you know any detection limits for each element for the selected method you can add them in this table. A way to check if all the correct units have added correct is to filter the Lab_Elements field for contains ‘ppm’ or ‘pct’ etc.

Check that the Lab_Methods and Generic_Methods are correct for each element.

Also check if there is missing library data you can go to the libraries tab and enter them then reload all the data and repeat the process.

When the table is correctly filled out return to the “Import Historic Assays’ tab make sure all the assay results fields are highlighted and select ‘Append Samples’ in section 9 and the Progress text field will show the progress of the loading process.

![image](media://26a1ae86-92a7-427c-901d-659af510ed51)

Once the import is complete repeat for the other tables with different labs and methods.