Re your annual report:
From your example, it appears your data items are all based on counts. Since we don't have access to your data, here's an example that uses the Orders table in the Northwind Database. You should be able to adapt this technique to your situation. In this example, the goal is to display a report for all 12 months of 1995 and show the following monthly counts:
# of orders
# of orders shipped via shipper #3
# of orders with freight >= $15.00
You could attack this using either code or queries. In this instance, I believe that code is simpler, but I'll show both techniques.
Code: Function CountMake() show below creates table z_tblTempHold, and a recordset of Orders records with an Order year of 1995. It creates a record for each month and populates it with the month, number of orders, number of orders shipped via shipper #3 and number of orders with freight charges >= $15.00.
Queries: There may be a simpler way, but I found it was necessary to create three queries, each performing a different count (total orders, orders shipped via #3, and orders with freight charges….). Having done that, I created a master query the incorporates the results of the three subqueries.
Output: Whether you use Function CountMake() or xqryCountAll, you end up with essentially identical data, i.e.
Sort_ CountOf_ CountOf_ CountOf_
Month OrderID Shippers Freight
Jan 31 16 24
Feb 29 8 21
Mar 33 7 21
Apr 28 10 15
May 33 11 25
Jun 32 9 23
Jul 30 9 22
Aug 33 8 23
Sep 33 14 26
Oct 37 6 29
Nov 37 11 30
Dec 35 13 23
The trick now was to reverse the rows and columns to conform to your desired format. Not being a cross-tab whiz, I found the easiest and quickest way was to pull the table into Excel, copy the data, then move to a blank cell and Paste Special, selecting the Transform option. Once you get the data looking the way you want, save it, close Excel, return to Access and import the data. Now it's just a matter of using the Report Wizard to create the report for you, using the transformed table as the source.
While this may all look very complex, I think you'll find that if you copy/paste the routines into the Northwind database and test them, it'll be much clearer.
CODE EXAMPLE:
Function CountMake()
'*******************************************
'Name: CountMake (Function)
'Purpose: Counts 1995 orders, by month, also
' totalling # of orders shipped via
' shipper 3 and # of freight charges
' >= $15.00.
'Tables(s): Northwind (table) Orders
'Output: (table)z_tblTempHold - 12 records
'To Test: from debug window: ? CountMake <enter>
'*******************************************
Dim db As Database, strSQL As String
Dim n As Integer, test As Variant, tblName As String
Dim rs As Recordset, rs2 As Recordset
Dim orderhold As Integer, freighthold As Integer
Dim Monthhold As String, shipperhold As Integer
Set db = CurrentDb
' Trap for any errors.
On Error Resume Next
tblName = "z_tblTempHold"
'Does table tblname exist? If true, delete it;
test = db.TableDefs(tblName).Name
If Err <> 3265 Then 'the specified table exists
DoCmd.DeleteObject acTable, tblName
End If
'SQL string to create new table
strSQL = ""
strSQL = "CREATE TABLE " _
& tblName _
& "(TheMonth TEXT (3), Orders NUMBER, Shippers NUMBER, Freight Number); "
' either of these commands creates the table
'DoCmd.RunSQL strSQL
db.Execute strSQL
'SQL string to query (table) Orders
strSQL = "SELECT OrderDate, ShipVia, Freight FROM Orders " _
& "WHERE ((Year([OrderDate]) = 1995))ORDER BY OrderDate;"
Set rs = db.OpenRecordset(strSQL) 'the source table
Set rs2 = db.OpenRecordset(tblName) 'the newly created, empty table
' Get number of records in recordset
n = 0
If Not rs.BOF Then
rs.MoveLast
n = rs.RecordCount '391 if table is unaltered
rs.MoveFirst
End If
If n > 0 Then 'process will bomb on an empty recordset
'prepare to loop through table "Orders"
orderhold = 0
shipperhold = 0
freighthold = 0
Do While Not rs.EOF
Monthhold = Format(rs!OrderDate, "mmm"
Do While Format(rs!OrderDate, "mmm"
= Monthhold And Not rs.EOF
orderhold = orderhold + 1 'count each order
'counts orders Shipped Via shipper #3
shipperhold = shipperhold + IIf(rs!ShipVia = 3, 1, 0)
'counts orders with freight charges >=$15.00
freighthold = freighthold + IIf(rs!Freight >= 15#, 1, 0)
rs.MoveNext
If rs.EOF Then Exit Do
Loop
'add a new record recapping the captured data
rs2.AddNew
rs2!TheMonth = Monthhold
rs2!Orders = orderhold
rs2!Shippers = shipperhold
rs2!Freight = freighthold
rs2.Update
'reset the counters
orderhold = 0
shipperhold = 0
freighthold = 0
Loop
End If
rs.Close
rs2.Close
db.Close
Set db = Nothing
End Function
QUERY EXAMPLE:
Three subqueries:
(1) xqryCountOrders:
SELECT Format([OrderDate],"mmm"
AS SortMonth, Count(Orders.OrderID) AS CountOfOrderID, Month([OrderDate]) AS sortorder
FROM Orders
GROUP BY Format([OrderDate],"mmm"
, Year([OrderDate]), Month([OrderDate])
HAVING (((Year([OrderDate]))=1995))
ORDER BY Month([OrderDate]);
(2) xqryCountShippers:
SELECT Format([OrderDate],"mmm"
AS SortMonth, Count(Orders.OrderID) AS CountOfShippers
FROM Orders
GROUP BY Format([OrderDate],"mmm"
, Year([OrderDate]), Month([OrderDate]), Orders.ShipVia
HAVING (((Year([OrderDate]))=1995) AND ((Orders.ShipVia)=3))
ORDER BY Month([OrderDate]);
(3) xqryCountFreight:
SELECT Format([OrderDate],"mmm"
AS SortMonth, Count(Orders.OrderID) AS CountOfFreight
FROM Orders
WHERE (((Orders.Freight)>=15))
GROUP BY Format([OrderDate],"mmm"
, Year([OrderDate]), Month([OrderDate])
HAVING (((Year([OrderDate]))=1995))
ORDER BY Month([OrderDate]);
MAIN QUERY: xqryCountAll:
SELECT xqryCountOrders.SortMonth, xqryCountOrders.CountOfOrderID, xqryCountShippers.CountOfShippers, xqryCountFreight.CountOfFreight
FROM (xqryCountOrders LEFT JOIN xqryCountShippers ON xqryCountOrders.SortMonth = xqryCountShippers.SortMonth) LEFT JOIN xqryCountFreight ON xqryCountOrders.SortMonth = xqryCountFreight.SortMonth
ORDER BY xqryCountOrders.sortorder;
