|< Day Day Up >|
One of the nice things about working in Access is that you can write queries in a graphical design mode and save them for use in VBA. While that is useful, you might also want to modify the queries from VBA. This can be as simple as changing the query to a parameterized query, or it can be a situation when you want the VBA to choose which columns of data the query should return. In other cases, you might want to change the query from a query that uses the Sum function on the rows to one that takes an Average or Standard Deviation.
5.3.1. Referring to Queries
You can refer to queries with DAO or ADO. I am going to focus on using DAO in the next examples. However, there is no reason why you can't use ADO and ADOX to do the same thing. I believe that DAO is a little more intuitive and easier to use, but use the one you are most comfortable with.
Earlier in the book, you saw how to pull data from Access when you are in Excel and use the CopyFromRecordset method of the Range object to put the data into the Excel worksheet. You do the exact same thing in Access when you automate Excel; refer to Chapter 3 to refresh your memory if necessary. While in Access, you need a few variables to use the DAO objects: one variable for the database, one variable for a recordset to hold the results of a query, and possibly a QueryDef object if you are going to create and/or modify queries, as we will do in the next examples. Example 5-9 shows how to create a new query that selects all of the records from a table called tbl_CostCenters and calls the query "Query1."
Example 5-9. Creating a new query
Public Sub MakeQuery1( ) Dim db As DAO.Database Dim qry As DAO.QueryDef Dim sqltxt As String Set db = Access.CurrentDb sqltxt = "Select tbl_CostCenters.* from tbl_CostCenters" Set qry = db.CreateQueryDef("Query1", sqltxt) qry.Close Set qry = Nothing Set db = Nothing End Sub
The biggest difference between getting a DAO database connection from Excel and from within Access is that in Access, the CurrentDB method is a pointer to the current open database that you are working in. This makes creating the connection much easier. If you are using ADO, use the Access.CurrentProject.Connection property to return a reference to the open database as an ADO connection.
Another topic that often comes up is what to do in situations when you need data that is in multiple databases. I suggest keeping it simple. Unless there are serious performance issues, link the data from the other data sources into the Access database you are working in and query them as though they were tables in your database. You will not be able to create queries in the other database, but you will be able to perform most other tasks. If you absolutely need to have a second connection, you can create the connection using the method shown in Chapter 3.
You may have noticed that in Example 5-9, the DAO database object has a CreateQueryDef method. This does exactly what it sounds like it does: creates a new query definition. The syntax is simply a name and the SQL text separated by commas. The CreateQueryDef method is useful, but I find that in most cases you will modify queries that already exist. Example 5-10 shows how to modify Query1 to select only centers that are storefront locations, as identified in the field LineofBusiness2.
Example 5-10. Modifying an existing query
Public Sub ModifyQuery1( ) Dim db As DAO.Database Dim qry As DAO.QueryDef Dim sqltxt As String Set db = Access.CurrentDb sqltxt = "Select tbl_CostCenters.* from tbl_CostCenters " & _ "Where tbl_CostCenters.LineofBusiness2 = ""Storefront Locations""" Set qry = db.QueryDefs("Query1") qry.SQL = sqltxt qry.Close Set qry = Nothing Set db = Nothing End Sub
This example illustrates the use of the QueryDefs collection of the DAO Database object. When you set a variable of type DAO.QueryDef to an existing query in the database, you can modify the query, open it using the OpenRecordset method, or, if it is an action query, run it with the Execute method. The SQL property of the QueryDef object allows you to change the query by writing SQL, assigning it to a string variable, and assigning the SQL property the value of the string. You also might notice the quotation marks in the sqltxt variable code; I want to ensure that double quotes are used in the query so that using an apostrophe will not affect the code. I find more errors for SQL code breaking down because an entry using an apostrophe when the code was written using single quotes than any other single error with SQL.
While this example seems to work well, you might not want to continue modifying the query each time that you change the criteria for LineofBusiness2. You can accomplish the same things by changing the query to a parameterized query and then setting the parameter with the code. See the new SQL in Example 5-11; it is saved as Query1Parameter.
Example 5-11. Parameter SQL example
SELECT tbl_CostCenters.* FROM tbl_CostCenters WHERE (((tbl_CostCenters.LineOfBusiness2)=[LOB]));
With this as the SQL, every time the query opens, it asks the user for the parameter. You will not want your user prompted when he runs your code, but the entry of the parameter can be handled through code. Example 5-12 shows the value of the parameter being passed to the procedure when it is called.
Example 5-12. Parameter code example
Public Sub UseQuery1Parameter(Ptext As String) Dim db As DAO.Database Dim qry As DAO.QueryDef Dim rs As DAO.Recordset Set db = Access.CurrentDb Set qry = db.QueryDefs("Query1Parameter") qry.Parameters("LOB").Value = Ptext Set rs = qry.OpenRecordset qry.Close If Not rs.BOF And Not rs.EOF Then rs.MoveFirst While Not rs.EOF Debug.Print rs.Fields(0).Value & " " & rs.Fields(1).Value rs.MoveNext Wend End If Set qry = Nothing Set db = Nothing End Sub
This example opens the recordset and writes two of the fields to the Debug window, provided that there is data; check for this by determining that you are not at both the beginning and end of the recordset. A very easy way to cycle through a recordset is to use the While Not rs.EOF...Wend statement. Be sure to have the rs.Movenext line inside the While statement, or it will continue to run the code until you force a break, because it will never get to the end of the recordset until you move to the next record. Also note that since there is only one parameter, you could refer to the parameter as qry.Parameters(0).value instead of using the name of the parameter, but I think it is best to use the name of the parameter anytime you know it.
5.3.2. Querying Data with Form Input
What if you want to create a generic form that allows you to select a table, select a field from the table, and enter in criteria for the selected field? The input would return all records from the table that met that criteria. This is actually a fairly straightforward task when using an Access form. We will add an extra step by dumping the result to a new Excel workbook. Use this if you have a database of addresses and want to send a mailing to every person on the list in a particular zip code on some occasions, every person added after a particular date on other occasions, etc. Having to build static queries to do that would be very time consuming. Also, having the data in multiple tables would add to the complexity. The generic form example is a simple solution to a complex problem.
In Access, go to the Forms tab and create a new form in Design View. When the blank form comes up, click on View Form Header/Footer to add a header and footer to the form. Next, go to the Properties dialog by pressing Alt+Enter. On the Format tab of the Properties dialog, set Record Selectors, Navigation Buttons, and Dividing Lines to No. (This step is for appearance only, since this form will not be doing any record navigation, so record selectors and navigation buttons are unnecessary and actually make the form look cluttered.) If you dont see the control toolbox, click on View Toolbox to bring it up.
You need a drop-down box that holds the names of the tables and queries in the database, a list box that holds the fields in the selected table or query, and a text box to hold the criteria. First, create a query that gives you the names of the tables and queries in the database by querying the Name field of the MSysobjects table where the Type is 1 or 5 (to give you Tables and Queries) and the Flags value is 0. Set the Flags value to 0 because you want only regular tables and select queries, not system tables or action queries. Save this query as qry_TablesQueries:
SELECT MSysobjects.Name FROM MSysobjects WHERE (((MSysobjects.Type)=1 Or (MSysobjects.Type)=5) AND ((MSysobjects.Flags)=0));
Now, go back to your form and place a combo box on the form. The wizard comes up, as shown in Figure 5-4. In order for the wizard to recognize the field in qry_TablesQueries as valid, you must have Access set to show system objects by going to Tools Options and checking the "System objects box in the Show section of the View tab. This only needs to be checked while you design the form. When you are finished, uncheck the box.
Figure 5-4. The first step in creating a combo box, when you can select whether you want to use a table or query to look up the values or enter in a list
In the wizard, select "I want the combo box to look up the values in a table or query," and press Next. On the next screen, click the Radio button for queries, select the query called qry_TablesQueries, and click Next, as shown in Figure 5-5. On the next screen, click the right arrow to move the Name field to the Selected Fields list, as shown in Figure 5-6. There is only one field available in the figure, but if there were multiple fields, you would select the one holding the data you want to retain. On the sort screen, select a field to sort, or just press Next. The next screen shows the values in the query, which will be all of the regular tables and the select queries; just press Next again. On the final screen, pick a label for your combo box in this example, I chose Data Selection and press Finish. The final screen is shown in Figure 5-7.
Next, put a list box on the form. I suggest drawing the list box big enough to hold the names of your queries and to have enough room for at least four or five rows of data you can always resize it later. When the wizard comes up, press the Cancel button. The Properties dialog should be open for the list box; if it isn't, press Alt+Enter to bring it up. On the Properties dialog box, click on the Data tab and set the Row Source Type to Field List. Next, click on the label for the list box and set the caption
Figure 5-5. Selecting the table or query that holds the data you want to show in the combo box
Figure 5-6. Selecting the field name
Figure 5-7. The final step, when you enter specific text that you would like to use to label your combo box
to Field Selection, either by setting the caption property on the Format tab of the Properties dialog for the label or by double-clicking on the label and editing the text.
Next, put on a text box to hold your specified criteria. Set the label for this text box as Criteria, click on the Other tab on the Properties dialog, and set the name to be PText.
Now you are ready to write code. Click on the Combo box, and go to the Event tab on the Properties dialog. On the drop-down menu for the On Change event, select Event Procedure, and click on the ellipsis to the right of the menu. This brings up the code window, where you enter Example 5-13.
Example 5-13. Combo box change example
Private Sub Combo0_Change( ) Me.List2.Enabled = True Me.List2.RowSource = Me.Combo0.Value Me.List2.Requery Me.PText.Value = "" Me.PText.Enabled = False End Sub
This enables the list box and sets the row source to be equal to the selected table or query. The Requery method of the list box updates the list box with the field list of the new selection. The PText box is set to an empty string and is not enabled until a field name is selected. Next, go to the Event tab of the list box, select Event Procedure on the After Update event drop-down menu, click on the ellipsis, and enter Example 5-14.
Example 5-14. List box After Update example
Private Sub List2_AfterUpdate( ) Me.PText.Enabled = True End Sub
Now, go to the toolbox, put a Command button on the form, and press Cancel when the wizard comes up. On the other tab of the Properties dialog for the Command button, change the name to RunButton. Then go the Event tab of the properties dialog for the PText text box, select Event Procedure for the After Update event, click on the ellipsis, and enter the code in Example 5-15. Note that the user will have to move the focus from the PText box after entering the text before the RunButton becomes enabled.
Example 5-15. Text box After Update example
Private Sub PText_AfterUpdate( ) Me.RunButton.Enabled = True End Sub
Next, click on some of the gray space outside the form to get to the form's properties, or select Form on the drop-down menu at the top of the Properties dialog. On the Event tab, go to On Open, select Event Procedure from the drop-down menu, and click on the ellipsis. Then enter the code in Example 5-16.
Example 5-16. Form on Open Event example
Private Sub Form_Open(Cancel As Integer) DoCmd.Restore Me.List2.Enabled = False Me.PText.Enabled = False Me.RunButton.Enabled = False End Sub
This code ensures that a user does not run the code before entering the correct information. Now you are ready to put the code into the Command button's On Click event. Go to the Properties dialog of the Command button, and on the Event tab, go to the On Click event. Select Event Procedure from the drop-down menu, and click the ellipsis. In the code window, click on Tools References and add a reference to the object library for your version of Excel. Then enter the code shown in Example 5-17.
Example 5-17. Button code example
Private Sub RunButton_Click( ) 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 x As Integer Dim sqltxt As String Set db = Access.CurrentDb Set qry = db.CreateQueryDef("") sqltxt = "Select " & Me.Combo0.Value & ".* " & _ "From " & Me.Combo0.Value & " Where " & _ Me.List2.Value & " = [PValue] " qry.SQL = sqltxt qry.Parameters("PValue").Value = Me.PText.Value Set rs = qry.OpenRecordset Set xlApp = New Excel.Application xlApp.Visible = True Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.ActiveSheet x = 1 For Each fld In rs.Fields xlWs.Cells(1, x).Value = fld.Name x = x + 1 Next fld Set xlRng = xlWs.Cells(2, 1) xlRng.CopyFromRecordset rs xlWs.Columns.AutoFit rs.Close qry.Close Set fld = Nothing Set rs = Nothing Set qry = Nothing Set db = Nothing Set xlRng = Nothing Set xlWs = Nothing Set xlWb = Nothing Set xlApp = Nothing End Sub
As you review this code, notice that a parameter called PValue holds the value of the criteria the user specifies; I don't know whether the user will pick a field that needs a string, number, date, etc. If I attempt to put the criteria in the SQL text, I need to know what the data type is. However, if I use a parameter, Access does this for me, so I don't need to check the type to determine whether I need quotes.
There are some other items that I would like to point out in this code. When you create a query definition with an empty string as a name, it is only a temporary query definition and will be dropped when the code is finished. Also, to create a parameterized query when writing SQL text in code, put the name of the parameter in brackets. Finally, I am not saving or closing the Excel document; instead I am setting the variables to be equal to nothing. This removes the reference to the Excel objects in the code but leaves the application open.
When you have finished testing your work, put a meaningful caption on the Command button and add a title. I also suggest adding a Command button to close the form in the footer. When the wizard comes up, select Form Operations and Close Form. Click Next, leave the default exit picture, and click Next again. Keep the default name and choose Finish. When you open the form, you should see something similar to Figure 5-8. Your form may vary in size, font, color, etc.
Save your form with a meaningful name when you are done. I have called mine frm_GenericQuery. This form could be used in any database as long as the database had a reference established to the Excel object library and you brought along the query for the tables and queries. Generally, I suggest writing code like this that can be moved from one application to another. You might be tempted to have a list of tables built into your combo box or to create a form specific to each table, but then you won't be able to use past work in new applications you create.
Now that you have been introduced to creating interactive queries and are familiar with the syntax, you can write more complex queries and perform additional automation within Excel.
Figure 5-8. The completed form
5.3.3. Creating Crosstab Queries
In Access databases, it is very common to use crosstab queries for summarizing data. While this is their intended purpose, I generally recommend using a pivot table instead of a crosstab query for most purposes. You are limited to one field in a crosstab query. Despite this limitation, it is often necessary or just desirable to build a crosstab query to summarize data. For this example, assume you have three tables: one for centers, one for sales, and one for products, and assume you have a query called qry_BaseQuery that brings up the following fields:
Let's assume that you want a crosstab report with Product Categories in columns across the top and Center Names as rows. In addition, you need two crosstab reports, one for Quantity and one for Total Cost. This report can be created easily through the wizard. Instead, go into Design View for a new query, and change the query type to crosstab by going to Query Crosstab Query. Right-click in the gray space at the top of the query, select Show Table, and select the qry_BaseQuery table. On the field list, double-click (or drag down) the CenterName, ProductCategory, and Quantity fields so that they move into the query fields. Next, on the crosstab row, select Row Heading for CenterName, Column Heading for ProductCategory, and Value for Quantity, and change the Group By option in the Quantity column to Sum. Finally, save the query as qry_QuantityCrosstab. Your query should look similar to Figure 5-9.
Figure 5-9. The design view of the crosstab query summarizing quantity by center and product category
Once you have saved the query, change the Quantity field to TotalCost, go to File Save As, and name it qry_TotalCostCrosstab. You can now open either query and see either the quantity of each product category for each center or the total cost of each product category by center.
There are some limitations to this. For example, if you want the quantity and total cost by product category by center while also getting an average price per unit, you can use two crosstab queries that would join with a third query to bring in the fields that you need and perform any math. While this would work, you would need to maintain the query every time new product categories were added, and this type of query and join runs very slowly. This brings us to a query concept that I call the Created Crosstab Query.
5.3.4. Generating a Created Crosstab Query
This query is created by using VBA to write SQL for the query. The concept is rather simple, although the implementation can be tricky. To create this query, go to the Modules tab in Access and select New Module. Go to Insert Procedure, call this procedure CreatedCrosstabPerUnit, and make it a Public Sub. When this query is finished, we will export it to Excel and perform a row and column sum.
Here are the basics of this query. First, perform a Group By query on the field that you want as the column headings to ensure that you get only column headings with values and that the query stays up to date. (If you want all possible column headings, query the source table for the field to get all possible values.) Next, write a series of immediate if (IIF) statements that bring in the value field if the value in the column heading field matches the column's value; otherwise it puts in a zero. Unlike a regular crosstab, it has zeros in unused cells instead of blanks. Once the immediate if statements are written for each column, add other fields that need to be selected, along with the appropriate Group By statement, and save the query.
To do this, you need DAO Database, Query, and Recordset objects, along with Excel Application, Workbook, Worksheet, and Range objects. In addition, you need several string and integer variables. The full code is listed in Example 5-18.
Example 5-18. Created Crosstab example
Public Sub CreatedCrosstabPerUnit(queryname As String, colfield As String, _ rowstr As String, valfield1 As String, valfield2 As String) Dim db As DAO.Database Dim qry As DAO.QueryDef Dim rs As DAO.Recordset Dim fld As DAO.Field Dim xlApp As Excel.Application Dim xlWb As Excel.Workbook Dim xlWs As Excel.Worksheet Dim xlRng As Excel.Range Dim w, x, y, z As Integer Dim sqlstr As String Dim numfmt As String Dim sqlstrcol As Collection Dim varitm As Variant Set sqlstrcol = New Collection Set db = Access.CurrentDb Set rs = db.OpenRecordset("Select Name from MSysobjects Where " & _ "Name = ""qry_CreatedCrosstab""") If rs.EOF And rs.BOF Then Set qry = db.CreateQueryDef("qry_CreatedCrosstab") End If If Not rs.EOF And Not rs.BOF Then Set qry = db.QueryDefs("qry_CreatedCrosstab") End If rs.Close Set rs = Nothing Set rs = db.OpenRecordset("Select " & colfield & " From " & queryname & _ " Group by " & colfield) If Not rs.EOF And Not rs.BOF Then rs.MoveFirst While Not rs.EOF sqlstr = "Sum(IIF([" & colfield & "] = """ & rs.Fields(0).Value & _ """,[" & valfield1 & "],0)) As [" & rs.Fields(0).Value & "_" & valfield1 & "]" sqlstrcol.Add sqlstr sqlstr = "" sqlstr = "Sum(IIF([" & colfield & "] = """ & rs.Fields(0).Value & _ """,[" & valfield2 & "],0)) As [" & rs.Fields(0).Value & "_" & valfield2 & "]" sqlstrcol.Add sqlstr sqlstr = "" sqlstr = "Sum(IIF([" & colfield & "] = """ & rs.Fields(0).Value & _ """,[" & valfield2 & "],0))/(Sum(IIF([" & colfield & "] = """ & _ rs.Fields(0).Value & """,[" & valfield1 & "],0))+.000001) AS [" & _ rs.Fields(0).Value & "_PerUnit]" sqlstrcol.Add sqlstr sqlstr = "" rs.MoveNext Wend End If sqlstr = "Select " & rowstr For Each varitm In sqlstrcol sqlstr = sqlstr & ", " & CStr(varitm) Next varitm sqlstr = sqlstr & " From " & queryname & " Group By " & rowstr qry.SQL = sqlstr qry.Close rs.Close Set rs = db.OpenRecordset("qry_CreatedCrosstab") Set xlApp = New Excel.Application xlApp.Visible = True Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.ActiveSheet x = 1 For Each fld In rs.Fields xlWs.Cells(1, x).Value = fld.Name x = x + 1 Next fld Set xlRng = xlWs.Cells(2, 1) xlRng.CopyFromRecordset rs Set xlRng = xlWs.Cells.SpecialCells(xlCellTypeLastCell) y = xlRng.Row + 1 w = xlRng.Column x = 0 For z = 1 To rs.Fields.Count If xlWs.Cells(1, z).Value Like "*" & valfield1 Then x = z End If If x = z Then z = rs.Fields.Count Next z rs.Close For z = x To w Set xlRng = xlWs.Cells(y, z) If Not xlWs.Cells(1, z).Value Like "*PerUnit" Then xlRng.FormulaR1C1 = "=Sum(R2C:R[-1]C)" End If If xlWs.Cells(1, z).Value Like "*PerUnit" Then xlRng.FormulaR1C1 = "=Sumproduct(R2C[-2]:R[-1]C[-2]," & _ "R2C:R[-1]C)/Sum(R2C[-2]:R[-1]C[-2])" End If If xlWs.Cells(1, z).Value Like "*" & valfield1 Then numfmt = "#,##0" End If If Not xlWs.Cells(1, z).Value Like "*" & valfield1 Then numfmt = "$#,##0.00" End If Set xlRng = xlWs.Range(xlWs.Cells(2, z), xlWs.Cells(y, z)) xlRng.NumberFormat = numfmt Next z Set xlRng = xlWs.Range(xlWs.Cells(y, x), xlWs.Cells(y, z - 1)) xlRng.Font.Bold = True With xlRng.Borders.Item(Excel.xlEdgeTop) .LineStyle = Excel.xlContinuous .Weight = Excel.xlThin .ColorIndex = 3 End With Set xlRng = xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(1, z - 1)) xlRng.Font.Bold = True With xlRng.Borders.Item(Excel.xlEdgeBottom) .LineStyle = Excel.xlContinuous .Weight = Excel.xlThin .ColorIndex = 3 End With xlWs.Columns.AutoFit Set xlRng = Nothing Set xlWs = Nothing Set xlWb = Nothing Set xlApp = Nothing Set fld = Nothing Set rs = Nothing Set qry = Nothing Set db = Nothing End Sub
You can call this procedure from the Immediate Window in the design view of a module. The syntax to call this procedure to get Quantity, Total Cost, and Per Unit Cost by Center and by Product Category is:
Call CreatedCrosstabPerUnit ("qry_BaseQuery", "ProductCategory", "CenterName", _ "Quantity", "TotalCost")
The syntax is the same whether you call it from within another procedure or from the immediate window. To show you how useful this can be, if you want to look at the actual products instead of only the product category, change the string that says ProductCategory to ProductName. Now you can imagine a reporting system that lets you pick the level of detail that you want to see and, at the press of a button, create a report. And, even though there are many different combinations of reports, the same module creates them all.
A lot happens in this example. After the declarations and the CurrentDB reference are set, it checks to see if there is a qry_CreatedCrosstab query in the database. If it exists, the code sets the qry variable equal to the existing query. If it does not exist, it creates the query definition. This step is useful because you never know what might be deleted on a given computer when you distribute an application. This step ensures that the code runs error-free.
Next, query the colfield as a Group By query to get each unique instance of the values in that field. Once you have a recordset with all of the necessary columns, write the immediate if statements. In this example, each unique IIF statement is written to a collection.
While this step can be avoided by writing to the query string each time and concatenating, I like putting each one in the collection so that I can build the entire SQL string in one section, rather than writing to the string over several sections. I think this both makes debugging easier and ensures the commas are handled correctly.
Notice that on the IIF statement where division is being done, I have added .000001 to the denominator. By adding this small number, you can avoid a divide by zero error if for some reason the denominator were zero. While you could instead test for a zero, the code quickly gets very confusing with the additional test. Adding this very small number does not change the result except in very rare instances, and for business functions it has no impact.
Each column created by the IIF statement is named by the AS keyword. Use brackets on both sides of the name to ensure that no errors are caused by spaces in the name. After the statements are done, the SQL String is put together, the .SQL property of the query is set to the string, and the query is closed.
Next, set the recordset using the new query's OpenRecordset method, create the Excel objects, and drop the field names into the worksheet. Send the recordset to Excel using the CopyFromRecordset method of the ExcelRange object, and determine where the last row of data is so that totals can be done by column. Excel makes this very easy because each worksheet has a specially defined cell that tells you the last cell used, called xlCellTypeLastCell. If you set a range equal to this cell, you can use the .Row property of the range to determine the last row. Add one to this row to find out which rows your formulas belong in.
The formulas themselves may look strange to you if you have never used R1C1 notation , but once you figure it out, it is very easy to use and is much simpler than figuring out where you are to determine the column letter and row number. The R stands for row and the C for column. The syntax is ordered by R and then C. If you refer to the Row that you are in, you simply use R. The same idea works for column. If you put a number directly after R or C and don't put it in brackets, Excel treats it like an absolute reference. The formula has a $ in front of the row or column identifier. If you put a number in brackets directly after R or C, it moves the number of columns or rows specified. For rows, a negative number moves up the spreadsheet, so if you are in row 10 and you enter R[-2], you refer to row 8; R refers to row 12. For columns, a negative number moves left, so if you are in column D and you enter C[-2], you refer to column B; C refers to column F. Be careful not to refer to a column less than column A or a row less than row 1.
This makes it very easy to write sum formulas when you want to sum an entire recordset, because you want the second row of the column (R2C) and the row above the sum row (R[-1]C). Use this formula for all of the columns that are not per unit. If the column you are summing is per unit, you have two ways to do it. The first way would be to take the sums that you have just calculated and divide them. While this would certainly be easy, in some cases you have only the result and the weighting column, so I wanted to demonstrate how to take a weighted average. This is made easy by the SumProduct function in Excel. For this function, enter your two ranges set off by commas, and Excel will return the sum of the two ranges multiplied by each other. If you ever took a statistics class, you would have created a column that held the weightings, which were then added up and divided by the sum of the column that held the quantity. Excel lets you do this in one step by taking the SumProduct and dividing it by the sum of the quantity column.
Now that the formulas are taken care of, some formatting is needed. In this case, the top row is bold with a red border below. The formula row is bold with a red border above it. The quantity fields are given a number format without decimals but with commas if they go over 1,000. The total cost and per unit fields are formatted as Currency with a Dollar sign and two decimal places.
The columns are then auto-fit, and the object references are set to nothing. Again, this code does not close the Excel workbook. In production code, it is likely that I would have set the code to save and print the worksheet and then close Excel. Printing, page setup, and other formatting options are covered in detail in Chapter 8.
One note of caution: if you do not use the step where xlApp.Visible = True, the code will run, but you will see nothing. Excel will still be open in the background. You can see it happening if you go to the Processes tab on your task list (Ctrl+Shift+Esc in Windows XP).
When you run this code, you will see an Excel document that looks similar to Figure 5-10. You can change the colors, fonts, number formats, etc. Again, to code a formatting change, the easiest way is to use the Macro Recorder in Excel and then review the code.
Figure 5-10. The Excel document that results from the created crosstab query code when summarizing by product category
After you have run the code and gained an understanding of how it works, play around with it to see how easy it is to change or add additional columns. Try to do the same thing with joined regular crosstab queries. You'll find that the extra coding now will be worth it to not have to maintain the application. The advantage is even more evident when changes happen infrequently. With staff turnover or just fading memories, you may end up with an application that does not tie to the real numbers, and people won't know how to fix it. Using this method ensures that each time there are new records, they will be put into the result set.
|< Day Day Up >|