Slide 1

							Slide 1                                Running a Warehouse Starter Query:
          http://www.iw.state.ma.us/   Navigate to the CIW Web Site
                                       http://www.iw.state.ma.us.
                                       Click on CIW Data.




Slide 2                                Click on MMARS on the top navigation bar.




Slide 3                                Click on the link to Starter Queries, it is a small
                                       red link near the top.
Slide 4   The starter queries are categorized by business
          function. Each link will open in MS Word,
          make sure you have Word installed.
          Modification dates are listed next to most of the
          categories. This demonstration will use an
          Intragovernmental Starter Query. Click on
          “Intragovernmental”.




Slide 5   When you open a link to a business function;
          all of the queries for that function are listed in
          the same document. The “View Name” is the
          name of the table you will need to link to in
          order to run the query. The code you need to
          select and copy will always start with the word
          “SELECT” and it ends before the next time you
          see “Business Function:”




Slide 6   Select the SQL code just like you select text in
          a word file or an email.
Slide 7   Once the SQL code is selected you need to
          copy it. You can copy using any one of the
          following three methods:
          Right Click (Click one with the right mouse
          button directly on the selected text) and choose
          “Copy” as shown in the screen shot.
           Go to the “Edit” menu and choose “Copy”
           Use the keyboard combination of Control + C




Slide 8   Once copied, the SQL will then be pasted in
          Microsoft Access.
          Access, if installed, will be listed in your
          programs list within the start menu.




Slide 9   Upon starting Access you will need to select an
          existing database (shown) or create a new one.
Slide 10   You will now need to link to the table specified
           in the starter query. If you have previously
           linked to this table you do not have to perform
           this step. This step is required for any table you
           have not linked to in the past.




Slide 11   Choose “ODBC Databases” from the “Files of
           type” drop down list.




Slide 12   Locate and click on “warehouse_prod”. Some
           users will see find this option in the “File Data
           Source” tab while others will find it in the
           “Machine Data Source” tab (shown).
Slide 13   At this point you will be prompted to enter your
           Login ID and password. Your Warehouse
           Login ID is the same as your MMARS Login
           ID (UAID). Your password is your MMARS
           Login PLUS the last 4 digits of your Social
           Security number. Your password never
           changes; there is no expiration like there is for
           your MMARS password. If you cannot access
           the warehouse with your ID and password;
           contact your department security officer for
           assistance. Enter your Login ID and password
           and click “OK”.



Slide 14   If your Login is successful you will see the list
           of all warehouse tables. Contact your security
           officer if you do not have access to the correct
           tables.
           All of the newMMARS tables start with the
           letter “M”, followed by the 2 or 3 letter
           business function code. The necessary table for
           this particular query is
           M_GA_Internal_Transaction_Agreement.
           Click on the name, it will highlight in blue.
           Click OK to link to the table.




Slide 15   Every time you link to a new table you will see
           the “Select Unique Record Identifier” box. This
           is just a list of all of the fields in the table,
           always click “Cancel”.
Slide 16   Once linked, the table name and globe symbol
           will always appear in the “Tables” area. You
           are now ready to start the Query.




Slide 17   Click on “Queries” on the left side of the screen
           in the Objects panel.




Slide 18   Click the “New” button.
Slide 19   All of your new queries should start in the
           Design View, which is the default choice. Click
           “OK” to begin the query.




Slide 20   The first step in a new query is choosing the
           table. The list that appears is a list of all the
           tables you have linked to. If you have only
           linked one table there would only be one option
           in the list. Locate and click on the appropriate
           table name.




Slide 21   Click “Add” to add the selected table to the
           query.
Slide 22   You can see in the background that the table
           has been added to the query building area.
           Click “Close” to close the table select window.




Slide 23   It is now time to paste the Starter Query, the
           SQL code, into access. Click on the “View”
           menu and choose “SQL View”.




Slide 24   Upon entering the SQL view, you will notice
           there is already a small amount of SQL code
           here. Leave the existing code alone, don’t
           delete or de-select it. You will paste the SQL
           code here and replace the existing text. Right
           click (click once with the right mouse button)
           anywhere in the empty white area.
Slide 25   Choose “Paste” from the menu. You could
           have also selected “Paste” from the view menu
           or used the keyboard combination of Control +
           V.




Slide 26   The SQL code from the Starter Query has now
           been pasted into the appropriate area. This SQL
           code is the “behind the scenes” code that builds
           a query. Once you return to the Design View
           you’ll see the built query.




Slide 27   To return to the Design View click on “View”
           and choose “Design View”.
Slide 28   Upon returning to the Design View, you can
           now see that the SQL code in the background
           has built the query. Some starter queries require
           you to set criteria on certain fields. For
           example, you would need to specify the Fiscal
           Year and Department. This particular query
           will prompt you to enter criteria upon running.
           If you ever see criteria in brackets [ ], that
           indicates that you will be prompted for criteria
           upon running. If there are no brackets [ ], you
           need to manually enter criteria. Check the
           criteria fields carefully before attempting to run
           any query. Click the “Run” button (the red
           exclamation point) to run the query.

Slide 29   With this particular query you will first be
           prompted to enter the Budget Fiscal Year.
           Enter the BFY and click “OK”.




Slide 30   You will now be prompted to enter your 3 letter
           Department code. Enter the Dept code and
           click “OK”. This is the last prompt for this
           particular query. The query will now run.
           Please note that queries, no matter how simple
           or complex, may take some time to run.
Slide 31   The results are now displayed in Access. You
           can now view, print, create a report in Access
           or export your data to Excel. You have
           successfully run a warehouse Starter Query.
           This handout serves as a brief overview of the
           process of running a starter query but it is
           highly recommended that you attend the
           Instructor Led Class – Intro to MMARS Data
           in the Warehouse for more detail.
           To see upcoming dates for the class please visit
           the Training page of the Knowledge Center.

           http://knowledgecenter.osc.state.ma.us/KC/Trai
           ning/Training.asp?tid=Training