1 / 30

Safari OpenAccess and Microsoft Office 2007 and above

Safari OpenAccess and Microsoft Office 2007 and above. November 3, 2011. Open Microsoft Excel and Click on the Data tab, then “From Other Sources”, and select “From Microsoft Query”. Data Source box will appear, highlight the database you want to access and click OK.

minjonet-roussel
Download Presentation

Safari OpenAccess and Microsoft Office 2007 and above

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Safari OpenAccess and Microsoft Office 2007 and above November 3, 2011

  2. Open Microsoft Excel and Click on the Data tab, then “From Other Sources”, and select “From Microsoft Query”

  3. Data Source box will appear, highlight the database you want to access and click OK

  4. Enter your Alpha username and password, Click OK

  5. Select the table that you want to access. Click on the + symbol in front of a table to see what fields are available. If you want to use all of the fields in a table just double click the table name.

  6. You can select specific fields to use by either double clicking on the field name or highlighting the field name and clicking the right arrow key. The fields that you select will move to the right box. If you accidentally select a field that you don’t want, you can click the field name in the right box then click the left arrow button to move it back.

  7. If you want to see the fields in Alphabetical order, Click the “Options” box and mark the box before selecting fields

  8. This is what the fields look like in Alphabetical order

  9. After selecting the fields you want to use, click Next

  10. Filter Data if you wish to limit the number of records that are returned. In this example, we are selecting only classified employees.

  11. We also selected only records with a vacation leave balance greater than 20. Once you have completed the filters, click Next.

  12. You can select to sort by any fields you initially selected. Since we didn’t select a field to identify who the record is for we are going back to the first screen of the query to select name.

  13. We selected the full_name field. The fields will appear on the spreadsheet in the order selected. We want the first column on the spreadsheet to be the name so we can highlight the field and use the arrows on the right side to move it up in the list.

  14. Now everything is listed the way we want it to appear on the spreadsheet so we can click next until we get back to the sort option.

  15. By default the data will be sorted by the primary field which is the employee ID, select Full_name to sort by that field.

  16. If you have created a Query that you would like to use again you can press the ‘save query’ box. If you do save it, you will come back to this screen again and then you can return the data to Microsoft Excel. When you click on ‘save query’ the next box will appear on your screen.

  17. Name the query file so you can identify it. It is recommended to include the name of the table you used in the filename as the table will not automatically be open when you use the query. Queries are saved by default to this location, you can change the location if you wish.

  18. Click Finish to Return the data to Excel

  19. Click OK to place the data in the worksheet starting at cell A1

  20. We only received one record so we are going to edit the query to bring in more data. To edit the query, click on Data, Refresh All dropdown and click on Connection Properties

  21. Click on the ‘Definition’ tab and then click the “Edit Query” box

  22. We decreased the vacation leave balance filter to select any balance greater than 10

  23. After selecting to return the data to Excel, this box will appear again, click OK

  24. This box will appear next and you will need to login again for the query to complete

  25. Example of results

  26. At a later time, you can access the spreadsheet again and if you want to update the data you would select DATA and then Refresh All. You may receive a security message as shown on the next slide. You will be prompted for your username and password to continue.

  27. If you receive this security notice, click OK

  28. If you want to use a query you previously created to pull data into a new spreadsheet (this would be the query saved from the query wizard) you would open a new spreadsheet then select Data  From Other Sources  From Microsoft Query.

  29. Click on the ‘Queries’ tab and select the name of the query you want to use. Click Open. You will then need to enter your username and password and you will be taken to the Query Wizard where you will have the opportunity to make changes to the query before loading the data. This needs to be marked in order to use the query wizard

  30. Questions? • Contact MEC Fiscal Services • Carol Van Sickle, 614-934-6541 • Michelle Powers, 614-934-6522

More Related