SOP for AQS Data File Generation

Table of Contents (click on a topic below to jump to that section):

·  Introduction

·  Import Monitor Protocol Data From AQS

·  Manually Add Monitor Protocol Data

·  Create AQS Data Text Files

o  Raw Data Files

o  Precision and Accuracy/Bias Data Files

Introduction

Before you can use the Toolbox to create AQS Raw Data (RD), Precision (RP), or Accuracy/Bias (RA) text files for the first time, you must have already set up your site and monitor in AQS. If you have not yet done this, follow the guidance in the AQS User Guide from the EPA TTN website to complete this process.

  1. Once you have set up a site and monitor in AQS, you must enter the 4-digit AQS Site ID you assigned the site in AQS into the “Site” form of the Toolbox.
  2. From the Main Switchboard of the Toolbox, click on the “Add or Modify Personnel, Site, And Sampler Information” button.
  3. Click on the “Add/Modify Sites” button.
  4. Use the navigation buttons near the bottom of the screen to navigate to the site or sites to which you need to enter an AQS Site ID. If the AQS Site ID field is not populated, enter the 4 digit AQS Site ID you assigned to the site in AQS and close the form when you have finished entering AQS Site IDs for all relevant sites.
  5. You must also import or enter Monitor Protocol data into the Toolbox. The Monitor Protocol data consists of the AQS parameter code, method code, etc. that you have assigned to each monitor.
  6. You only need to import or enter Monitor Protocol data into the Toolbox once to begin with. You would only need to import or enter additional Monitor Protocol data into the Toolbox if you add a new site, monitor, or pollutant that you will be submitting data for to AQS.
  7. When you set up a monitor in AQS, you are given the option of entering Monitor Protocol data. The following screen from the AQS application shows where you enter Monitor Protocol data in AQS.

It is not required by AQS that you enter Monitor Protocol data when you set up a monitor in AQS. Once you submit your first RD text file to AQS for a site and monitor, the Monitor Protocol data will be automatically generated by AQS. However, ITEP recommends entering the Monitor Protocol data into AQS during your monitor set up if you are a first time AQS user because it is easier to create these records in AQS than it is to create them in the Toolbox.

·  If you have entered Monitor Protocol data into AQS or have previously submitted AQS RD files, continue with the “Import Monitor Protocol Data from AQS” section of this SOP.

·  If you have NOT entered Monitor Protocol data into AQS and choose to use the Toolbox to create these records, continue with the “Manually Add Monitor Protocol Data” section of this SOP.

o  Note: If you are unsure whether or not you have entered Monitor Protocol data in AQS, proceed with the “Import Monitor Protocol Data from AQS” section of this SOP to determine if the data exist in AQS.

·  If you have NOT entered Monitor Protocol data into AQS and choose to do so now using AQS, follow the guidance in the AQS User Guide on the EPA TTN website to complete this process. Once you have entered the Monitor Protocol data into AQS, continue with the “Import Monitor Protocol Data from AQS” section of this SOP.

Import Monitor Protocol Data From AQS

1.  First the Monitor Protocol data report must be downloaded from AQS.

a)  After logging onto AQS, click on the Retrieval drop-down menu of AQS

b) 
Choose STandard Report Selection, as shown in the following screen.

2.  You should receive a screen resembling the following one shown.

a)  Check that you are in Tribal Mode by making sure that the words “Tribal Mode” appear in the blue title bar of the form.

·  If you need to get to Tribal Mode, select “Turn Tribal Mode ON” from the “Action” menu item.

b)  For the Report Code field, choose “AMP500.”

c)  Click on the “Send via Email” radio button.

d)  Under the Report Outputs, check the “WORKFILE” box.

e)  For the File Name field, type “MK.”

f)  Under Report Selection Mode, click on the “Monitor Selection(detail)” radio button.

g)  Your screen should now look similar to the following.

3.  Click on the “Monitor Selection” tab to move to the following screen.

a)  Choose your Tribe in the Tribal Code field located in the upper left hand section of the screen.

b)  Choose the Site ID(s) that you need to download Monitor Protocol data for (for each site, you need to use a separate row).

·  If you are interested in downloading the Monitor Protocol data for all sites and parameters, which would be the case if you have not entered or imported any monitor protocol data into the Toolbox, choose “ALL” in the Pollutant Type field in the “Global Report Criteria” section.

·  If you need to only add Monitor Protocol data to the Toolbox for one or more sites and/or parameters, you can use the fields in the “Site-Monitor Criteria” section to specify only the sites and parameters that you have not yet entered or imported Monitor Protocol data into the Toolbox for.

c)  After selecting all desired Site IDs, proceed to Step 4 of this SOP (if you are unable to see any Site IDs in the drop-down boxes, see the following tips).

Note: If no Site IDs appear in the drop-down box on the AQS form and you are sure that data have been submitted to AQS, it is likely that the data were submitted using the “traditional” method of identifying sites with the state and county codes instead of the “tribal” method of identifying sites with the tribal code. In order to use the Toolbox to create AQS raw data files, you need to transition to the “tribal” method. Complete the following steps for each of your sites to make this transition.

i.  Check that you are logged in to AQS using your screening group and NOT as a read only user (the name in parentheses in the blue title bar should appear as your screening group name, NOT “Read Only”).

·  If you are logged in to AQS as a read only user, exit AQS and log in to AQS again, this time selecting your screening group instead of the read only option.

·  The blue title bar should resemble the following, with your screening group name in place of “Information Management Group.”

ii.  Check that you are in Tribal Mode.

·  If you need to get to Tribal Mode, select “Turn Tribal Mode ON” from the “Action” menu item.

iii.  Go to the “Maintenance” menu and choose “Site” to open the on-line Site Maintenance screen.

iv.  In the screen that opens, specify the State Code, County Code, and Site ID for the site you want to apply a Tribal Code to. Then click on the “Execute Query” button, as shown in the following screen.


v.  Once the site data appear on the screen, select the appropriate Tribal Code from the Tribal Code drop-down field and save the results, as shown in the following screen.

·  You should see a message “1 record applied and saved” appear at the bottom of the screen.

vi.  After you have completed these steps for all of your sites, return to Step 1 of the Import Monitor Protocol Data From AQS section of this SOP.)

4.  Click on the “Report Options” tab to move to the following screen.

a)  Click on the “Uncheck All” button on the right hand side of the screen.

b)  Check only the “MONITOR PROTOCOLS” box.

c)  Click on the “Generate Report” button.

5.  An email should be sent to you containing a link to the report. You can now exit from AQS.

6.  When you receive the email, click on the link included in the email to retrieve the report.

a)  The link is a zipped file (with file extension .zip) that contains a text file (with file extension .txt) of the report and a PDF file with a description of the report.

b)  Extract the text file (the file with the .txt extension) to your hard drive (C:\).

·  Do NOT extract the text file to a folder on your hard drive (such as “My Documents” or even the “Desktop”), but rather directly onto your C:\ drive.

c)  Change the name of the file you extracted to “MK.txt.”

d)  Open the text file and delete the last line of text that tells you how many records were written.

e)  Save the text file.

Note: If the text file only contains field names without data, you have not entered any monitor protocol data into AQS. You can use the Toolbox to create these records (see the “Manually Add Monitor Protocol Data” section of this SOP) or use AQS to enter monitor protocol data (follow the guidance in the AQS User Guide on the EPA TTN website).

7.  Now from the Main Switchboard of the Toolbox, click on the “AQS Data File Creation” button under the “Reporting” category.

8.  The following window will open.

9.  Click on the “Import Monitor Protocol Data from AQS” button to open the following window.

10.  Click on the “Import Text File” button. You will receive a message box stating that the import was successful once the data import process is completed by the Toolbox. Click the OK button on this message box

11.  Click on the “Inspect Temporary Import Table” button to verify that the import was successful.

a)  Scroll through the data to make sure that the data look reasonable.

b)  The number of records in the table is displayed at the very bottom of the table (it is a good idea to write down this number in your database logbook).

c)  The following screen shows example data.

d)  If the data look reasonable, close the table; be careful NOT to close Access entirely. If you accidentally close Access, you will need to reopen the Toolbox and navigate through the forms to get back to this one.

12.  Click on the “Set Up Transfer from Temporary to Permanent Table” button. A query will open in design view.

a)  Check that all of the fields in the temporary table (tblMonitorProtocol_Temp) have corresponding fields in the “Append To” row in the query design grid.

b)  Make sure to use the horizontal scroll bar to see all of the fields. Your screen should now look similar to the following screen. If so, save and close the query. Proceed with Step 13.

Note: If any of the fields in the design grid start with “Expr”, you must delete all of the fields and repopulate the design grid.

·  Click on the “Edit” menu and select the “Clear Grid” option.

·  To repopulate the design grid, click on the first field in the tblMonitorProtocol_Temp box just below the asterisk (*).

·  Use the vertical scroll buttons on the tblMonitorProtocol_Temp box to scroll to the bottom.

·  While holding down the <shift> key, click on the last field in the list. Now all of the fields should be highlighted.

·  Click anywhere within the highlighted area and drag the fields to the first column in the design grid of the query window.

·  Verify that all fields have a value selected in the “Append To” row.

·  Save the query.

·  Close the query window.

13.  Click on the “Transfer Data from Temporary to Permanent Table” button.

a)  Click “Yes” when you receive the message box about running an append query.

b)  Verify that the number of records that are being transferred (in the second message box) is the same as the number of records in the temporary table (see your database logbook) and click “Yes.”

14.  Click on the “Inspect Permanent Table” button to verify that the transfer was successful by making sure that the data that you just imported are shown.

15.  Click on the “Delete Temporary Import Table” button.

16.  Click on the “Continue to Create AQS Data Files Form” button. Continue with the “Create AQS Data Text Files” section of this SOP.

Manually Add Monitor Protocol Data

1.  From the Main Switchboard of the Toolbox, click on the “AQS Data File Creation” button under the Reporting category.

2.  The following window will open.

3.  Click on the “Manually Add Monitor Protocol Data” button to open the following window.

a.)  Choose your Tribe from the drop-down box.

b.)  Choose the AQS Site ID that you will be entering new monitor protocol data for.

c.)  Click on the “View Existing Monitor Protocol Records for This Site” button to see if any monitor protocol data are already in the Toolbox for this site.

·  If there are data in this query, please verify that the protocol record you are entering does not duplicate any of the existing records.

·  If there are no data in the query, there are no monitor protocol records in the Toolbox for this site, and therefore you need to enter monitor protocol data for all of the pollutants monitored at this site.

·  Close the query window.

d.)  From the Pollutant drop-down box, choose the pollutant that you are adding the monitor protocol data for.

e.)  Choose the POC.

·  If this is the first time that you have entered monitor protocol data for a site, you will need to type in the POC instead of choosing it from the drop-down box.