Hi,
I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor.
I have one crosstab query (Query1) which has the following fields:
SPID (supervisor ID), total:group by, as row heading
Date, total:group by, as column heading
Calls handled, total:sum, as value
Date, total:where, criteria between [StartDate] and [EndDate] - this is taken from a form, [SelectDate]
When I run the query (start and end dates entered in the form), it returns the correct data (42 rows).
I have created a report based on this MS article:
http://support.microsoft.com/kb/328320
It has 9 headings in the page header, 9 columns in the detail section and 9 total boxes in the report footer, all set up as suggested in the article.
Here is the VBA behind the report: - Option Compare Database
-
-
' Constant for maximum number of columns
-
Const conTotalColumns = 9
-
-
' Variables for Database object and Recordset.
-
Dim dbsReport As DAO.Database
-
Dim rstReport As DAO.Recordset
-
-
' Variables for number of columns and row and report totals.
-
Dim intColumnCount As Integer
-
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
-
Dim lngReportTotal As Long
-
-
Private Sub InitVars()
-
-
Dim intX As Integer
-
-
' Initialize lngReportTotal variable.
-
lngReportTotal = 0
-
-
' Initialize array that stores column totals.
-
For intX = 1 To conTotalColumns
-
lngRgColumnTotal(intX) = 0
-
Next intX
-
-
End Sub
-
-
-
Private Function xtabCnulls(varX As Variant)
-
-
' Test if a value is null.
-
If IsNull(varX) Then
-
' If varX is null, set varX to 0.
-
xtabCnulls = 0
-
Else
-
' Otherwise, return varX.
-
xtabCnulls = varX
-
End If
-
-
End Function
-
-
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
-
' Put values in text boxes and hide unused text boxes.
-
-
Dim intX As Integer
-
' Verify that you are not at end of recordset.
-
If Not rstReport.EOF Then
-
' If FormatCount is 1, put values from recordset into text boxes
-
' in "Detail" section.
-
If Me.FormatCount = 1 Then
-
For intX = 1 To intColumnCount
-
' Convert Null values to 0.
-
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
-
Next intX
-
-
' Hide unused text boxes in the "Detail" section.
-
For intX = intColumnCount + 2 To conTotalColumns
-
Me("Col" + Format(intX)).Visible = False
-
Next intX
-
-
' Move to next record in recordset.
-
rstReport.MoveNext
-
End If
-
End If
-
-
End Sub
-
-
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
-
-
Dim intX As Integer
-
Dim lngRowTotal As Long
-
-
' If PrintCount is 1, initialize rowTotal variable.
-
' Add to column totals.
-
If Me.PrintCount = 1 Then
-
lngRowTotal = 0
-
-
For intX = 2 To intColumnCount
-
' Starting at column 2 (first text box with crosstab value),
-
' compute total for current row in the "Detail" section.
-
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
-
-
' Add crosstab value to total for current column.
-
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
-
Next intX
-
-
' Put row total in text box in the "Detail" section.
-
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
-
' Add row total for current row to grand total.
-
lngReportTotal = lngReportTotal + lngRowTotal
-
End If
-
End Sub
-
-
-
Private Sub Detail_Retreat()
-
-
' Always back up to previous record when "Detail" section retreats.
-
rstReport.MovePrevious
-
-
End Sub
-
-
-
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
-
-
Dim intX As Integer
-
-
' Put column headings into text boxes in page header.
-
For intX = 1 To intColumnCount
-
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
-
Next intX
-
-
' Make next available text box Totals heading.
-
Me("Head" + Format(intColumnCount + 1)) = "Totals"
-
-
' Hide unused text boxes in page header.
-
For intX = (intColumnCount + 2) To conTotalColumns
-
Me("Head" + Format(intX)).Visible = False
-
Next intX
-
-
End Sub
-
-
-
Private Sub Report_Close()
-
-
On Error Resume Next
-
-
' Close recordset.
-
rstReport.Close
-
-
End Sub
-
-
-
Private Sub Report_NoData(Cancel As Integer)
-
-
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
-
rstReport.Close
-
Cancel = True
-
-
End Sub
-
-
Private Sub Report_Open(Cancel As Integer)
-
-
' Create underlying recordset for report
-
-
Dim intX As Integer
-
Dim qdf As QueryDef
-
Dim frm As Form
-
-
' Set database variable to current database.
-
Set dbsReport = CurrentDb
-
Set frm = Forms!SelectDate
-
' Open QueryDef object.
-
Set qdf = dbsReport.QueryDefs("Query1")
-
' Set parameters for query based on values entered
-
-
qdf.Parameters("Forms!SelectDate!StartDate") = frm!StartDate
-
qdf.Parameters("Forms!SelectDate!EndDate") = frm!EndDate
-
-
' Open Recordset object.
-
Set rstReport = qdf.OpenRecordset()
-
-
' Set a variable to hold number of columns in crosstab query.
-
intColumnCount = rstReport.Fields.Count
-
-
End Sub
-
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
-
-
Dim intX As Integer
-
-
' Put column totals in text boxes in report footer.
-
' Start at column 2 (first text box with crosstab value).
-
For intX = 2 To intColumnCount
-
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
-
Next intX
-
-
' Put grand total in text box in report footer.
-
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal
-
-
' Hide unused text boxes in report footer.
-
For intX = intColumnCount + 2 To conTotalColumns
-
Me("Tot" + Format(intX)).Visible = False
-
Next intX
-
-
End Sub
-
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
-
-
' Move to first record in recordset at the beginning of the report
-
' or when the report is restarted. (A report is restarted when
-
' you print a report from Print Preview window, or when you return
-
' to a previous page while previewing.)
-
rstReport.MoveFirst
-
-
'Initialize variables.
-
InitVars
-
-
End Sub
As you can see, I have not altered it much from the article.
When I run the report, it displays only the 1st row of data from the query.
I did test the code using northwind following the instructions from the MS article and it worked fine.
Can anyone help with this, I'm tearing my hair out here!!
Cheers,
Olly
14 7756
Olly,
Your crosstab query appears to be a totals query, and you need the details for the report, Make a copy of your query, place it in design view;then click the Greek symbol (Sigma) Off on your toolbar and change your query to a detail crosstab, and use this for your report.
Thanks for the reply, however this doesn't work.
Sigma is greyed out in design view for this query - I don't see how it would help anyway as the query is working exactly as I want it to and displays all of the data - it's the report based on the query which is not displaying all of the rows.
Query gives me 42 rows, report is showing only 1!
Anyone else?
Thanks for the reply, however this doesn't work.
Sigma is greyed out in design view for this query - I don't see how it would help anyway as the query is working exactly as I want it to and displays all of the data - it's the report based on the query which is not displaying all of the rows.
Query gives me 42 rows, report is showing only 1!
Anyone else?
1. Are you using the same exact query (without modification) as the record source of your report?
2. Are you using the same grouping levels in both, the query and the report?
3. Have you reconciled the difference in total amounts between the query and the report? Can you tie that difference to anything?
There is only one query in my database at the moment and that is the one used for the report.
However, all textboxes on the report are unbound and are populated by the VBA which I've posted. - SPID 11/10/2008 12/10/2008 13/10/2008 14/10/2008 15/10/2008 16/10/2008 17/10/2008
-
220 374 159 75 52 504 412
-
900000002 200
-
900000003 86 26 49 31
-
900000004 16 1 50
-
900000006 2720 869 973 421 1881 1567
-
900000010 36 1
-
900000011 163 421 1829 1169 328
-
900000012 90 80 670 612 80
-
900000015 1024 818 436 1385 1 140
-
900000019 2470 276 143 409 1123 802
-
900000020 5124 2371 1664 801 2091 1608
-
900000021 2436 1817 1813 216 123 2504 2452
-
900000022 331 909 541 819 1286
-
900000025 66 18 879 1303 411 152 12
-
900000029 2602 1046 1082 653 323 1111 1077
-
900000033 12 15 65 247 582
-
900000036 4436 1681 2369 286 2126 1831
-
900000044 300 1691 991 500 253
-
900000045 1664 1107 1055 516 531 877
-
900000046 1960 1169 1472 322 1393 1180
-
900000049 3096 1777 1808 1503 516 1248 843
-
900000051 20 4
-
900000068 3524 1814 1889 500 239 980 700
-
900000069 542 349 179 357 667 251 347
-
900000070 370 1101 1025 526 369
-
900000073 287 1191 1568 273 899 591
-
900000074 4554 1842 2231 272 2196 2596
-
900000077 2406 668 192 1555 733 151
-
900000079 4712 1207 1511 301 2049 1700
-
900000082 67 1 224 104
-
900000084 124 37
-
900000085 5388 2357 2549 353 2521 2288
-
900000086 6040 2762 2540 364 1783 2424
-
900000087 4188 2192 1972 1119 999 1559 2332
-
900000088 5186 3336 2136 1204 1051 3290 3870
-
900000089 4218 1384 1678 381 342 2577 1994
-
900000090 3250 2058 2156 1222 1856 1461
-
900000091 3022 1493 3007 3090 2273
-
900000092 3272 2505 2770 1977 941 1797 2090
-
900000093 1236 486 723 972 758 466 389
-
900000096 4123 3326
-
900000098 1424 729
Apologies, that doesn't look great!
That's what the query returns, the report shows only the headings, the first row of data and the total row (which is the same as the first row as this is all there is to total). Could it be related to the fact that the first row has no row heading (as these are calls handled by people who are not assigned to a supervisor)?
Yes, not having a header row could pre sent some problems if you are grouping the data. Try assigning to a supervisor id = "None" or some other means of grouping the"unassigned" in your query and see what happens with your report.
That didn't work either! I ran an update query on the underlying table and updated all null SPIDs to a value (900000023) and ran the report again. Now it still shows only one row (the 900000002 row!)
I don't get this.
That didn't work either! I ran an update query on the underlying table and updated all null SPIDs to a value (900000023) and ran the report again. Now it still shows only one row (the 900000002 row!)
I don't get this.
If the query works and the report does not, the problem is the report.
Did you set the required vb library reference to DAO? Does your code compile without error? Are you passing any parameters that are limiting the report to the 90000002 row? Put some breaks in your report code and examine the values of variables at different breakpoints.
Ok, so I've made some progress....
(I did have the right object library referenced, yes)
By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals.
But now I have a new problem:
The last row of data is repeated over and over again for 155 pages(!) and then it totals at the bottom.
I don't understand:
a) Why the last row is repeating like this as the code specifys only to populate the text boxes if it is not the end of the recordset - surely if it is the end, it should stop;
b) Why it stops (seemingly arbitrarily) after 155 pages!
Any ideas?
By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals.
a) Why the last row is repeating like this as the code specifys only to populate the text boxes if it is not the end of the recordset - surely if it is the end, it should stop;
b) Why it stops (seemingly arbitrarily) after 155 pages!
Any ideas?
I have no idea based on the info you provided. I don't understand what you mean by this statement:
"By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals."
Weren't you already using the query as the record source of your report?
Looking at the data, what is different about the last row, compared to the other rows?
Did you add any code of your own to the report (other than what MS provided you in the kb article)? Does your recordset loop check for EOF?
"By adding the underlying table as the recordsource for the report (same as the query) I can now see all of the data from the query on the report, and the totals."
Weren't you already using the query as the record source of your report?
No. Originally there was nothing in the recordsource property for the report - the recordset for the report was (and still is) created in code: - Set qdf = dbsReport.QueryDefs("Query1")
- Set rstReport = qdf.OpenRecordset()
I was advised by someone else to add the table which the query is based on as the recordsource property for the report. I did this and now I can see all of the data from the query on the report.
Nothing is different about the last row, other than the fact that it is at the bottom! As this is a dynamic crosstab query, the last row of data is not always the same as it changes as the date range I'm using changes - it is not always the same Supervisor ID which comes last depending on which teams were working during the specified date range.
The only things I changed in the code were names of the query, form, controls, etc and the number of columns.
Check for EOF? - Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
-
' Put values in text boxes and hide unused text boxes.
-
-
Dim intX As Integer
-
' Verify that you are not at end of recordset.
-
If Not rstReport.EOF Then
-
' If FormatCount is 1, put values from recordset into text boxes
-
' in "Detail" section.
-
If Me.FormatCount = 1 Then
-
For intX = 1 To intColumnCount
-
' Convert Null values to 0.
-
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
-
Next intX
-
-
' Hide unused text boxes in the "Detail" section.
-
For intX = intColumnCount + 2 To conTotalColumns
-
Me("Col" + Format(intX)).Visible = False
-
Next intX
-
-
' Move to next record in recordset.
-
rstReport.MoveNext
-
-
End If
-
End If
-
-
End Sub
Seems to!
Did you set any breakpoints and look at the values of variables at the breakpoint?
Are all of the following event procedures set for the report?
Report/Section Property Setting
------------------------------------------------------------
Report ................ OnOpen........... [Event Procedure]
Report ................ OnClose........... [Event Procedure]
Report ................ OnNoData......... [Event Procedure]
Report Header...... OnFormat.......... [Event Procedure]
Page Header........ OnFormat...........[Event Procedure]
Detail Section....... OnFormat...........[Event Procedure]
Detail Section....... OnPrint.............. [Event Procedure]
Detail Section....... OnRetreat...........[Event Procedure]
Report footer......... OnPrint...............[Event procedure]
Are you executing the following code? Try commenting it out and see what happens. -
Private Sub Detail_Retreat()
-
-
' Always back up to previous record when "Detail" section retreats.
-
rstReport.MovePrevious
-
-
End Sub
Not sure how to look at variable values at breakpoints, sorry.
All of those event procedures are covered yes.
I have tried commenting out that code and it doesn't do anything!
Thanks for this. Very useful : )
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: deejayquai |
last post by:
Hi
I've created a crosstab query and displayed it as a sub-report in my
main report. This is fine until the data changes and the column names
become incorrect. I know I have to create a...
|
by: mtech1 |
last post by:
Access 2002
I am trying to create a dynamic crosstab report that parameters come
from 3 different forms.
I get runtime error 3070 - The Microsoft Jet database engine does
not recognize...
|
by: jkearns |
last post by:
Hello,
I made a report from a crosstab query following the steps onlined in
MSDN's Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get...
|
by: deejayquai |
last post by:
Hi
I'm trying to produce a report based on a dynamic crosstab. Ultimately
i'd like the report to actually become a sub report within a student
end of year record of achievement. The dynamic...
|
by: deejayquai |
last post by:
I've worked through the MS KB example and it doesn't really help me as
I missing the basics of what the code is doing. I've posted a couple
of times here in thsi group but with no success. Could...
|
by: Brad |
last post by:
Thanks for taking the time to read my question.
I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own...
|
by: praful pathak |
last post by:
i
i am praful pathak,porbandar
i want to develop my own cross tab report in visual basic 6 i know what
developed query from ms access but how to coded in visual basic in
designing time and how to...
|
by: ollyb303 |
last post by:
Hello,
I am using a dynamic crosstab report to track performance statistics for my company and I have hit a problem.
I would like the option to track stats daily (for the last 7 complete...
|
by: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
| |