Creating an Access Report

Suppose you'd like a nice-looking report listing all of the users in your database together with the count of cases assigned to each one, along with the status of those cases. Microsoft Access is ideal for preparing this sort of hierarchical report. To get started, open up your FogBugz database in Access.

â– Note If you store FogBugz data in Access, you can open the database directly. If you're using SQL Server for your FogBugz database, you can create a new Access project based on the SQL Server data; search the Access help for "project" for more information. If you're using MySQL, you'll need to install the MySQL ODBC driver and attach the MySQL tables to an empty Access database to proceed.

Create a new Access query and add the Bug, Status, and Person table to the query. Figure 4-18 shows how these three tables can be joined (this particular screenshot is from an Access project based on a SQL Server FogBugz database).

Figure 4-18. Tables for building an Access query

Select the ixBug field from the Bug table, the sStatus field from the Status table, and the sFullName field from the Person table. As you can see, FogBugz field and table names are very close to what you'll see on the user interface. Group the query on the sStatus and sFullName fields, and tell Access to count the ixBug field. The SQL for the query I'm using in this case is:

SELECT COUNT(Bug.ixBug) AS Count, Status.sStatus, Person.sFullName FROM Bug INNER JOIN Status on Bug.ixStatus = Status.ixStatus INNER JOIN Person on Bug.ixPersonAssignedTo = Person.ixPerson GROUP BY Person.sFullName, Status.ixStatus

Save the query as BugCounts and close it. Navigate to the Queries container inside of Access, select the BugCounts query, and click the New Report button on the Access toolbar. Select the Report Wizard and click OK. On the first panel of the wizard, select all three fields for the report and click Next. On the second panel of the wizard, add sFullName and sStatus as grouping levels and click Next. On the third panel of the wizard, just click Next (you don't need to add any sorting because Access automatically sorts by the grouping fields). On the fourth panel of the wizard, select the Outline 2 layout and click Next. On the fifth panel of the wizard, select the Formal style and click Next. Click Finish to open the report. Figure 4-19 shows this report after a bit of editing in design view.

Figure 4-19. Access report based on FogBugz data
0 0

Post a comment