8.4. Using an Access Table for Reporting

While you can do all of this reporting without putting it into a table, I have found that by using a table to house report formats, you can make changes without touching the code. This makes it easier to develop reporting applications and allow others to maintain them. How much you put into the report formats is up to you. I suggest having fields available for font parameters, number formats, formulas, and a true/false field to allow you to hide the row. Let's look at the merits of using a table.

Assume that you wrote code that builds an income statement report with about 30 lines and several sums. At the bottom, to come up with net income, add income subtotals together and subtract expense subtotals. Of course there are underlines, double-underlines, and other formatting, as well as formulas. This report works well for a while but after five or six months in production, someone wants to add a few lines to the report to break out some expenses. If you did all of this formatting in code, you would have to edit just about every line that has to do with formatting, formulas, etc.

Now, if you have a table that holds all of the instructions, you would just need to put in the lines that you need, which would hold the formatting that you wanted for those rows. You then might have to update the formulas in the table to account for the number of new rows. I have found that it is much easier to teach someone how to update a formula in R1C1-style notation than it is to teach someone who has never done programming how to update a reporting module. Your choice depends largely on the role that you want to play. Many consultants make a nice living by maintaining their own applications for clients, and there is nothing wrong with writing the report with code alone. However, I would seriously consider putting the reporting formats in a table to make your application easier to modify.

With that said, let's take a look at this method from the top. First, look at the table design. Go into Access and create a new table. The first column is a line number. Having line numbers, even if you don't print them, ensures that your report shows up in the expected order. Next, decide whether to force the user to put in the exact formatting strings that Excel requires, or whether to use descriptive text, and either have it look up the Excel string in a table or add it to your code. Once you have figured that out, determine the number of columns that you need and give them names that are descriptive enough to make it easy for the user. I suggest using a table of lookup values, as this both makes the programming side much easier and is easier for the user. In addition, you can eliminate or add options to the table without having to touch the code. You won't be able to do that for the formula strings, but for anything where you set Excel constants, I suggest having a table of values (see Figure 8-3).

Let's say that in your report you want to allow users to choose whether to place a border in any given row on the report. There are many ways to make these options available to your users. As previously stated, the easiest way is to have a table with the different options and a lookup value to the value of the Excel constant. To accomplish the border on the row, create a table with two columns called tbl_Border. If the user doesn't want a border, she can leave the field blank. See the values in Table 8-2; you can look up these values in the Immediate Window in Excel as you looked up the formulas earlier in this chapter and the Excel constants in Chapter 7.

Table 8-2. Excel constant lookup values for borders

Description	Excel_Border_Constant_Value
Diagonal Down	5
Diagonal Up	6
Edge Bottom	9
Edge Left	7
Edge Right	10
Edge Top	8
Inside Horizontal	12
Inside Vertical	11

Now that you have the values for the placement of the border, you need a second table with the values of the types of lines that you want on the border. Call this table tbl_LineTypes. See the values in Table 8-3.

Table 8-3. Excel constant lookup values for border line styles

Description	Excel_Line_Constant_Value
Continuous	1
Dash	-4115
Dash Dot	4
Dash Dot Dot	5
Dot	-4118
Double	-4119
Slant Dash Dot	13
No Line	-4142

Once you have the values, the code is very simple. Assume you store the constant values in a variables declared as Long, and use Blocation and Bstyle. To find out whether the code needs to run, first to make sure that the user selected a style. There are a number of ways to check, but I suggest setting the value to 0 right after declaring it; if there is no selection, there will be no value put in, and the code will be skipped. Here is the small section of code:

     If Blocation <> 0 Then     With xlrng.Borders(Blocation)       .LineStyle = Bstyle     End With     End If

You could also add options for line weight, line color, etc., but for now, keep it simple and allow these two options for borders. Also, put in a formula column, a font size, a Yes/No field for bold, and a number format column. The formula and number format will most likely be freeform text and the font size will just take a number. However, you could offer some styles as options for the number format. Table 8-4 defines what your table will look like. Call the table tbl_ReportFormat .

Table 8-4. Access table setup

Field name	Data type
Line_Number	Long Integer
Link_Field	Long Integer
Print_Description	Text
Excel_R1C1_Formula	Text
Excel_Number_Format	Text
Font_Bold	Yes/No
Border_Location	Text
Border_Style	Text
Font_Size	Long Integer

This gives you a good start to see the possibilities. Keep in mind that any other formatting or formula options that you want to set can be done with a table like this.

Now, let's put this to use. The first thing that you need to know is what to link to with the Link_Field. In this case, I use the product number for the rows where I pull data. Look at Figure 8-3 to see what the table looks like filled in.

Figure 8-3. The resulting table to produce a report format using the same data used in previous examples

In the previous examples, we used a query called qry_BaseQuery. I modify it in this example to be qry_BaseQuery_ReportFormat . The only difference is that I added the ProductNum field, since that is what we are using to link to the report format table. In these reports, I want every row to show up even if there are no values I have to be very careful about the order in which I run the queries. First, run qry_BaseQuery_ReportFormat. Next, run qry_ReportStep1 , which takes a parameter for the line of business that we want to report on. The final query uses a left join because we want all rows in the report format table and only the rows that match in the qry_ReportStep1 query. See Example 8-5 for the SQL of qry_BaseQuery_ReportFormat, Example 8-6 for the SQL of qry_ReportStep1, and Example 8-7 for the SQL of qry_ReportStep2.

Example 8-5. qry_BaseQuery_ReportFormat

 SELECT tbl_CostCenters.LineOfBusiness1, tbl_Products.ProductNum, tbl_CostCenters.LineOfBusiness2, tbl_CostCenters.CostCenter, tbl_CostCenters.CenterName, tbl_Sales.SaleDate, tbl_Products.ProductCategory, tbl_Products.ProductName, tbl_Sales.Quantity, tbl_Sales.TotalCost FROM tbl_Products INNER JOIN (tbl_CostCenters INNER JOIN tbl_Sales ON tbl_CostCenters.CostCenter = tbl_Sales.CostCenter) ON tbl_Products.ProductNum = tbl_Sales.ProductNum;

Example 8-6. qry_ReportStep1

 PARAMETERS StoreType Text ( 255 ); SELECT qry_BaseQuery_ReportFormat.LineOfBusiness2, qry_BaseQuery_ReportFormat.ProductNum, Sum(qry_BaseQuery_ReportFormat.Quantity) AS SumOfQuantity, Sum(qry_BaseQuery_ReportFormat.TotalCost) AS SumOfTotalCost FROM qry_BaseQuery_ReportFormat GROUP BY qry_BaseQuery_ReportFormat.LineOfBusiness2, qry_BaseQuery_ReportFormat.ProductNum HAVING (((qry_BaseQuery_ReportFormat.LineOfBusiness2)=[StoreType]));

Example 8-7. qry_ReportStep2

 SELECT tbl_ReportFormat.LineNumber, tbl_ReportFormat.Print_Description, tbl_ReportFormat.Excel_R1C1_Formula, tbl_ReportFormat.Excel_Number_Format, tbl_ReportFormat.Font_Bold, tbl_Border.Excel_Border_Constant_Value, tbl_LineTypes.Excel_Line_Constant_Value, tbl_ReportFormat.Font_Size, Sum(qry_ReportStep1.SumOfQuantity) AS Quantity, Sum(qry_ReportStep1.SumOfTotalCost) AS Sales FROM ((tbl_ReportFormat LEFT JOIN qry_ReportStep1 ON tbl_ReportFormat.Link_Field = qry_ReportStep1.ProductNum) LEFT JOIN tbl_Border ON tbl_ReportFormat.Border_Location = tbl_Border.Description) LEFT JOIN tbl_LineTypes ON tbl_ReportFormat.Border_Style = tbl_LineTypes.Description GROUP BY tbl_ReportFormat.LineNumber, tbl_ReportFormat.Print_Description, tbl_ReportFormat.Excel_R1C1_Formula, tbl_ReportFormat.Excel_Number_Format, tbl_ReportFormat.Font_Bold, tbl_Border.Excel_Border_Constant_Value, tbl_LineTypes.Excel_Line_Constant_Value, tbl_ReportFormat.Font_Size ORDER BY tbl_ReportFormat.LineNumber;

With the queries, we are ready to write the code in Access VBA to automate Excel. In this example, I have a variable for the type of store, and I fill in a value; you could easily run multiple types by using a recordset, as we did in the earlier examples. For simplicity, run only one, shown in Example 8-8.

Example 8-8. Building a report in Excel using a report format table

 Public Sub ExcelReportFormat(  ) Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim db As DAO.Database Dim qry As DAO.QueryDef Dim rs As DAO.Recordset Dim fld As DAO.Field Dim LocationChoice As String Dim w, x, y, z As Integer LocationChoice = "Storefront Locations" Set xlapp = New Excel.Application xlapp.Visible = True Set xlwb = xlapp.Workbooks.Add Set xlws = xlwb.ActiveSheet Set db = CurrentDb Set qry = db.QueryDefs("qry_ReportStep2") qry.Parameters(0).Value = LocationChoice Set rs = qry.OpenRecordset xlws.Range("A1").Value = "Sales Report for " & LocationChoice x = 1 For Each fld In rs.Fields   xlws.Cells(3, x).Value = fld.Name   x = x + 1 Next fld Set xlrng = xlws.Range("A4") xlrng.CopyFromRecordset rs y = 4 + rs.RecordCount For z = 4 To y    ' We are assuming that there are only going to be two fields with    ' values that need totaled. You can also write it so that you get    ' the count of fields -1 and assume that the result of that calculation    ' minus 8 is the last x rows that need to have calculations    For w = 3 To 8      Set xlrng = xlws.Range(xlws.Cells(z, 9), xlws.Cells(z, 10))      Select Case xlws.Cells(3, w).Value        Case "Excel_R1C1_Formula"          If xlws.Cells(z, w).Value <> "" Then          xlrng.FormulaR1C1 = xlws.Cells(z, w).Value          End If        Case "Excel_Number_Format"          If xlws.Cells(z, w).Value <> "" Then          xlrng.NumberFormat = xlws.Cells(z, w).Value          End If        Case "Font_Bold"          If xlws.Cells(z, w).Value = vbTrue Then          Set xlrng = xlws.Rows(z)          xlrng.Font.Bold = True          End If        Case "Excel_Border_Constant_Value"          If xlws.Cells(z, w).Value <> "" Then            With xlrng.Borders(xlws.Cells(z, w).Value)               .LineStyle = xlws.Cells(z, w + 1).Value            End With            w = w + 1          End If         Case "Font_Size"           If xlws.Cells(z, w).Value <> "" Then             Set xlrng = xlws.Rows(z)             xlrng.Font.Size = xlws.Cells(z, w).Value           End If       End Select     Next w  Next z   Set xlrng = xlws.Range(xlws.Columns(3), xlws.Columns(8))   xlrng.Delete   xlws.Columns.AutoFit   xlws.Columns(1).Font.Size = 10   xlws.Rows(1).Font.Size = 14   xlws.Range("A3").Value = "Line #"   xlws.Range("B3").Value = "Description"   Set xlrng = xlws.Range(xlws.Cells(1, 1), xlws.Cells(1, 4))   xlrng.HorizontalAlignment = Excel.xlCenterAcrossSelection   xlws.Columns.AutoFit  rs.Close  qry.Close  Set fld = Nothing  Set qry = Nothing  Set rs = Nothing  Set db = Nothing  Set xlrng = Nothing  Set xlws = Nothing  Set xlwb = Nothing  Set xlapp = Nothing End Sub

Since this procedure does not take any parameters, press the Run button or F5 inside the procedure when you have finished typing it in. There are a few things to note. First, it uses Select...Case instead of additional nested IF statements to make the code more readable. Also, this procedure assumes that the line style of the border is the column to the right of the border location because the location and line style are set at the same time. Next, increment w by 1 to skip that column during the next cycle of the For loop. You could also store all of the options in variables during the loop. Once we are done using the information for the report format, I set a range equal to those columns and delete them.

In reality, I would not format a report like Figure 8-4. However, it demonstrates setting different font styles, font size, border styles, etc. Also, look at the formulas. Writing R1C1-style notation in a table makes it easy to count the rows above or below a row to set your formulas. You are essentially forced into using R1C1-style notation unless you can be certain that you will always use the same columns and rows. Also, if you make changes to the format using A1-style notation, you are in for much more difficult changes to the formulas. It might seem more difficult to use a table, but if you hardcode reports and later need to make changes, you quickly see the value of spending the time up front to create the reports with information in a table. You might need to work though this a few times before you see the value, but I encourage you to do that.

Figure 8-4. The resulting workbook from Example 8-8, formatted to demonstrate the options that you have