Summary

If you created an MS Access database using the Account Export window (UB> Utilities> Account Export), you can create an MS Excel spreadsheet of the MS Access database. Some customers use this feature to create customized reports in MS Excel using the data exported into the MS Access database. You can also skip this step and create a report using the MS Access database created from the Account Export window.

Step by Step

1     Open a blank MS Excel spreadsheet and select Data> Import External Data> New Database Query.

• This will open the Choose Data Source window.

2     Select the MS Access database you created in the Account Export window.

• Select MS Access Database as your Data Source and click OK. This will open the Query Wizard window.

3     Select the fields to include in the MS Excel spreadsheet you are creating.

• The Query Wizard window allows you to choose which data elements to include on the MS Excel spreadsheet.
• Click on the plus sign next to a folder in the Available table and columns section to display the fields that are available to include in the columns on your report.
• Double click on a field to move it over to the Columns in your Query section. You can select fields from any of the tables displayed.
• If you selected data from only one table the Query Wizard will open. If you have selected data from more than one table, skip to step 5 of the process.
  • If you import data from more than one table, you will have to set up a relationship between the information that was selected. For example, if you include meter and customer information on the spreadsheet, you will have to create a relationship that links the customer information to the meter information. If you do not link the information together, the information will display on the spreadsheet in a random order. For example, the meters attached to account 000001-000 will display attached to account 000007-000. You can establish a relationship between the meter table and the customer table using the UB customer number since that field is included on both tables. Defining this relationship will ensure that the information displays correctly on the report.

4     Import data into a spreadsheet from one database table.

• If you want to filter the data from the database before you enter it into the spreadsheet, highlight a field you want to filter the data by in the Column to Filter section.
  • After you have highlighted a field in the Column to Filter section, a field in the Only include rows where section will become enabled.
  • Select an argument qualifier (ends with, does not equal, etc.) to filter the report by from the drop-down menu. This will enable another field in the window to enter the rest of the argument.
  • If you want to add more than one qualifier argument to a field, select the AND or OR toggle.
  • If you want to filter by more than one field, select another field in the Column to filter field and follow the process above.
  • Click the Next button when complete. This will open the Sort Order window.
• Select the fields to sort by in the drop-down menus to sort the data. You can also sort the data after it has been entered into the spreadsheet.
  • Click the Next button when complete. This will open the Finish window.
• Click the Finish button to create the Excel spreadsheet with the data you have selected. This will open the Import Data window.
• The Import Data window is used to select which cell you would like to begin importing data into the spreadsheet. Select the upper left corner of the area you would like to import the data into and click the OK button.

5     If you selected data from more than one table you will have to establish a relationship between the data included on the spreadsheet.

• If you select data from more than one table, the tables will have to be linked together in order to define which records in the tables are related and should be displayed on the same rows of the Excel spreadsheet.
• An error message will display informing you that you must join the selected tables together using a program called Microsoft Query.
• Click OK. This will automatically open the Microsoft Query window.
• Find the field or fields in each table that are needed to link the data from one table to the other table, and create a relationship between those fields. Relationships are established by dragging and dropping a field from one table to another. For example, highlight and drag the Customer Number field from the Master table to the Customer Number field on the Meter table to establish a relationship between the two tables.
  • The Customer Number and Customer Sequence fields are in every table on the database and can be used to link the any or all of the tables together. When creating a relationship between tables, you should use both the Customer Number and Customer Sequence Number.  If a customer included on the report has more than one customer sequence number (for example, 000001-000, and 000001-001), that customer’s information will only display correctly on the spreadsheet if you create relationships using both the Customer Number and Customer Sequence fields.
• Create a filter in the Microsoft Query window if you would like to filter the data before exporting the MS Access database to an MS Excel spreadsheet.
  • Click on the icon. This will open the Criteria section in the middle of the window.
  • Click in the first Criteria Field cell, and select the field you would like to filter the export by. You can resize the columns in order to read the entire field name.
    • The naming convention of the fields is: table. field.
    • For example, if you would like to filter the report by transaction type, select Financial.Transaction-Type from the drop-down menu.
  • Double click in the Value cell to enter the filter argument. This will open the Edit Criteria window.
  • Enter an Operator and a Value to create an argument to filter the export by.
    • Click on the Values button to see all values available for this field.
    • The filter argument in the screen shot above will allow data with a transaction type of billing to be exported to the MS Excel spreadsheet you are creating.
  • Click File and Save or Save As to save your query. This could be helpful if you plan on importing the same data in order to create customized period reports.
  • Enter a query name and click the SAVE button.
  • This will return you to the Microsoft Query window. If you have entered all the desired filters and you are ready to export the data to your MS Excel spreadsheet, select File and Return data to Microsoft Excel Spreadsheet. This will open the Import Data window.
  • The Import Data window is used to select which cell you would like to begin importing the data into the spreadsheet. Select the upper left corner of the area you would like to import data into and click the OK button. This will populate the data into your spreadsheet.