Tips and Tricks
Simple Report With SubTotals and Grand Totals
Someone read the post from 2 days ago and took this to a slightly further point.
How can I list all the details, and also list subtotals by a subset of the itemID, and then a grand total. They were referring specifically to a report of the data.
Here is some fake data I created to illustrate.
itemid cost 01142 $1.00 01222 $7.00 01333 $2.00 01623 $4.00 01777 $7.00 01997 $4.00 02456 $8.00 02556 $5.00 02666 $9.00 02716 $8.00 02796 $9.00 02916 $2.00 03455 $5.00 03485 $4.00 03555 $7.00 03675 $7.00 05666 $6.00 05886 $1.00
The report should look like this. (plus appropriate headers)
itemid cost 01142 $1.00 01222 $7.00 01333 $2.00 01623 $4.00 01777 $7.00 01997 $4.00 01 subtotal = $25.00 02456 $8.00 02556 $5.00 02666 $9.00 02716 $8.00 02796 $9.00 02916 $2.00 02 subtotal = $41.00 03455 $5.00 03485 $4.00 03555 $7.00 03675 $7.00 03 subtotal = $23.00 05666 $6.00 05886 $1.00 05 subtotal = $7.00 grand total = $96.00
Here are the steps needed in Microsoft Access 2000. I am not putting in every small detail, as I believe you should think about what you are doing, and copying every tiny detail from me will not help you.
- start the report wizard, giving it the name of the table you are needing
- select the itemID and cost fields
- choose itemID as a grouping field
- click the Grouping Options button and choose a Grouping interval of “2 initial letters”
- no sorting is needed, but under Summary Options you want the Sum of the cost field
- pick a style and finish the report’s creation (Note: give it a proper name, like rptCosts, or similar)
The report is not quite what we wanted, but is close.
Next I went into the design view of the wizard created report and …
- deleted the “Summary for …” item from the itemID footer
- moved the “Left$ …” item from the itemID header to the itemID footer
- rearranged the objects in the itemID footer to better emulate the design I wanted
- went into the Sorting and Grouping section under the view menu
- changed the itemID Group Header to “No”
- continued to play with formatting until I had it the way I wanted it
See printouts, before and after changes, by clicking on the links in this sentence.
The key in my MS Access self-done education period was to use the wizard like we just did, and look at the design and especially properties of objects, and the Sorting and Grouping dialogue box. I experimented and discovered “stuff”.
Hopefully I can help you discover that “stuff” faster than I did, but in a way that still helps you “see” what is going on. Don’t ever depend solely on a recipe book. Discover the principles so that you can create your own recipes.
How To Do Group By Queries On Groups
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.
010250
010500
020722
020999
etc.
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);
Cool!
As usual, leave comments about your success or questions. Thanks.
A Reader Causes me to Drop Everything
A reader wrote to ask me the following.
Thank you very much for your sample databases. I did software for my hotel reservations and now I want to make SW for cash desk in my restaurant. Problem is that I do not know how to make matrix of buttons. Each buttom must have another name and another function. Names I have in my store software as PLU. For example I want to sell coffee then my staff will select category drinks and my SW will prepare matrix of buttons. There will be ice coffee, coffee, etc. How to do it. I have approximately 200 items in assorted categories.
I found this question quite interesting and it grabbed my curiosity. I actually dropped what I was doing to experiment. To see my full answer, check out Buttons: Now you see them, Now you don’t.
p.s. I’ve started including “Digg This” logos. If you appreciate all my hard work on these articles, please “Digg” my work. 
thanks
Table List
I noticed a posting on a forum the other day. The writer wanted to know how to get a simple list of all the tables in their database.
My initial research resulted in this query.
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>“~”) AND ((Left$([Name],4))<>“Msys”) AND
(((MsysObjects.Type)=1)))
ORDER BY MsysObjects.Name;
The writer was grateful but then re-posted a bit later to say that this did not list all the tables. A quick bit of research showed me that linked tables have a MsysObjects.Type=6, so …
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>“~”) AND ((Left$([Name],4))<>“Msys”) AND
(((MsysObjects.Type)=1) OR ((MsysObjects.Type)=6)))
ORDER BY MsysObjects.Name;
Looping Through Form Objects using the Tag Property
It is easy to loop through objects on a form that seem to be unrelated, and are not even connected to a recordset. We can just use the Tag property.
See the article at Looping Through Form Objects Using the Tag Property.