I know. Lousy title. Could not dream anything better up at 5:30 am on a Monday morning!
A reader wrote in to ask why they could not get their MS Access summary query working. They wanted a report like this.
Group Budget Actual 01 $ 34,000 $ 33,756 02 $ 75,000 $ 69,955 03 $ 12,000 $ 13,750 04 $ 25,000 $ 21,223
But their report looked like this.
Group Budget Actual 010250 $ 20,000 $ 20,000 010500 $ 14,000 $ 13,756 020722 $ 20,000 $ 13,455 020999 $ 55,000 $ 56,500 etc.
The thing is, that “Group” column is based on a BudgetCode field that has values like this.
The base query that gets us partway there is easily done using the Query Builder. (the steps that I followed below are based on Microsoft Access 2000, but easily translated to other versions)
- go to the query tab in Access
- click on “New”
- choose Design View and click OK
- select the table (or possibly another query) and click Add
- click Close
- drag the BudgetCode, BudgetAmount, and ActualAmount fields to the query grid
- click on the sum symbol (funny looking “E” in the toolbar)
- under the BudgetAmount and ActualAmount fields in the query grid, click on the Total cell
- from the drop down list choose Sum
- run the query (use the red exclamation mark in toolbar)
That gives us the report style that our reader initially created. With one minor change we will get the final result that we want, summarizing based on just the first 2 characters of the BudgetCode field.
- go back to design view (a little blue triangle symbol at the far left of the toolbar, or under the “View” menu)
- change the BudgetCode field name to this (without quotes) – “left([BudgetCode],2)”
- rerun the query
As a final note, let’s see what the actual “code” looks like. Under the View menu, choose the “SQL View”.
SELECT Left([BudgetCode],2) AS Expr1, Sum(data.BudgetAmount) AS SumOfBudgetAmount, Sum(data.ActualAmount) AS SumOfActualAmount FROM data GROUP BY Left([BudgetCode],2);
As usual, leave comments about your success or questions. Thanks.