Reporting in Microsoft Access

You can create a report from your iBase database in a Microsoft™ Access database format. You can use Microsoft Access to create reports with more than one main entity type.

Why use a Microsoft Access database as a report format?

You might choose to report to a Microsoft Access database for any of the following reasons:
  • iBase reports can be combined to allow reporting on more than one main entity. For example, a report on telephones, might also report on the subscribers of the phones involved.
  • To allow iBase data to be combined with data from other systems; Microsoft Access can incorporate data from a wide variety of formats: Extensible Markup Language (XML), OLE DB, and Open Database Connectivity (ODBC).
  • Microsoft Access data can be used by specialist database report tools.
When you choose a Microsoft Access database as the format for an iBase report, the output is as follows:
  • Each main entity, link, or linked entity has a separate Microsoft Access database table.
  • Each record that you include becomes a row in the relevant database table.
  • Each field that you specify in the report definition becomes a column in the relevant database table.
  • Any formatting that you specify as part of the report definition, for example fonts or highlighting, is ignored.
Note: When you export to Microsoft Access, any duplicate fields have a '2' added to the end of the field name. This occurs if there are duplicate fields in a link type and its link end entity type.

Creating a Microsoft Access database report

  1. In the report wizard, enter the details of the report.
  2. Click Next to display the second page and in the Output to area, select Microsoft Access and then specify a file name and location.
  3. Click Finish. When the report is complete, click Close.

Viewing the Microsoft Access database

To view the database, start Microsoft Access and open the database that you created. Double-click the table that is named after the main entity in your report. For example, if you selected Telephone as your main entity, double-click Telephone_ to display the contents of the Telephone_ table.

Reporting on an extended selection of entity types

One of the reasons that you might choose to create reports in Microsoft Access is to extend the selection of the entities that you report upon, beyond one link distance. For example, in a report on calls by a particular telephone; being able to report on entities one further link away, means you might report on the subscribers too.

Reporting on different entity types requires that you create separate report definitions and use them to create reports, these reports can be generated into the same Microsoft Access database.

Note: Microsoft Access reports from iBase do not include all the relationships, these must be added manually.

Linking tables and creating relationships in Microsoft Access

  1. Generate the first report and output to a new Microsoft Access database.
  2. Generate the second report and output the report to the same Microsoft Access database, and select Update existing database.
  3. Select Extend the database to include the report data.

    The Combined database now has one table for each of the entity types in your reports.

  4. Create relationships between the tables that originated from the reports:
    1. Select Tools > Relationships. A relationship might exist between the MainEntity_ID fields in related tables.
    2. For each entity type, select a field relevant to your report in one table and drag it to the corresponding field in the related table. You can use the record IDs to help identify the relationships that need to be created.
    3. Click Create to make the relationship.