If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab report repeats last record for all values
Hello,
I've run into a rather strange problem. I have a crosstab query that shows me exactly what I want (all the assignments for all students in all classes with their given mark). My problem is that when I make a dynamic Crosstab Report out of this crosstab query (by following the example in the Solutions.mdb db provided by MSDN), every record in the report displays the same data - which is always the last record in the crosstab query. For example, if in my crosstab query I have: Class Student Quiz Homework Exam English John Doe 5 7 55 English Jane Doe 8 11 68 English Mary Joe 9 8 59 Then in my crosstab report, it would show: Class Student Quiz Homework Exam English John Doe 9 8 59 English Jane Doe 9 8 59 English Mary Joe 9 8 59 My crosstab query is correct. My code for generating the report looks like: code Option Compare Database ' Use database order for string comparisons. Option Explicit ' Constant for maximum number of columns FinalCheckMarks query would create Const conTotalColumns = 12 ' 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 Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ' Place values in text boxes and hide unused text boxes. Dim intX As Integer ' Verify that not at end of recordset. If Not rstReport.EOF Then ' If FormatCount is 1, place values from recordset into text boxes ' in detail section. If Me.FormatCount = 1 Then For intX = 7 To intColumnCount 'I start at 7 because there are 6 row headers before the data starts Me("Col" + Format$(intX)) = rstReport(intX - 1) Next intX ' Hide unused text boxes in detail section. For intX = intColumnCount + 1 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_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 = 7 To intColumnCount 'I start at 7 because there are 6 row headers before the data starts Me("Head" + Format$(intX)) = rstReport(intX - 1).Name Next intX ' Hide unused text boxes in page header. For intX = (intColumnCount + 1) To conTotalColumns Me("Head" + Format$(intX)).Visible = False Next intX End Sub Private Sub Report_Open(Cancel As Integer) Dim intX As Integer Dim qdf As DAO.QueryDef ' Set database variable to current database. Set dbsReport = CurrentDb ' Open QueryDef object. Set qdf = dbsReport.QueryDefs("qryFinalCheckMarks_Crosstab2" ) 'this is the crosstab query ' 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 Report_Close() On Error Resume Next ' Close recordset. rstReport.Close Set rstReport = Nothing 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 ReportHeader_Format(Cancel As Integer, FormatCount As Integer) ' Move to first record in recordset at beginning of report ' or when 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.) If Not rstReport.EOF And Not rstReport.BOF Then rstReport.MoveFirst 'Initialize variables. InitVars End Sub /code I've went through this code line by line as it has run and watched every piece of data (i.e. every mark) come into the report via the Detail_Format subroutine. For some reason though, when it finishes looping through every student in every class and then goes to print preview, it displays the marks in the last record for every student in every class. Anyone out there have any ideas? Thanks! JK |
#2
|
|||
|
|||
Crosstab report repeats last record for all values
Might not address your exact question but there are other (and I think
better) solutions at http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The crosstab.mdb has a solution that is much more flexible and efficient than the "solutions.mdb". -- Duane Hookom MS Access MVP -- "Jason Kearns" wrote in message oups.com... Hello, I've run into a rather strange problem. I have a crosstab query that shows me exactly what I want (all the assignments for all students in all classes with their given mark). My problem is that when I make a dynamic Crosstab Report out of this crosstab query (by following the example in the Solutions.mdb db provided by MSDN), every record in the report displays the same data - which is always the last record in the crosstab query. For example, if in my crosstab query I have: Class Student Quiz Homework Exam English John Doe 5 7 55 English Jane Doe 8 11 68 English Mary Joe 9 8 59 Then in my crosstab report, it would show: Class Student Quiz Homework Exam English John Doe 9 8 59 English Jane Doe 9 8 59 English Mary Joe 9 8 59 My crosstab query is correct. My code for generating the report looks like: code Option Compare Database ' Use database order for string comparisons. Option Explicit ' Constant for maximum number of columns FinalCheckMarks query would create Const conTotalColumns = 12 ' 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 Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ' Place values in text boxes and hide unused text boxes. Dim intX As Integer ' Verify that not at end of recordset. If Not rstReport.EOF Then ' If FormatCount is 1, place values from recordset into text boxes ' in detail section. If Me.FormatCount = 1 Then For intX = 7 To intColumnCount 'I start at 7 because there are 6 row headers before the data starts Me("Col" + Format$(intX)) = rstReport(intX - 1) Next intX ' Hide unused text boxes in detail section. For intX = intColumnCount + 1 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_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 = 7 To intColumnCount 'I start at 7 because there are 6 row headers before the data starts Me("Head" + Format$(intX)) = rstReport(intX - 1).Name Next intX ' Hide unused text boxes in page header. For intX = (intColumnCount + 1) To conTotalColumns Me("Head" + Format$(intX)).Visible = False Next intX End Sub Private Sub Report_Open(Cancel As Integer) Dim intX As Integer Dim qdf As DAO.QueryDef ' Set database variable to current database. Set dbsReport = CurrentDb ' Open QueryDef object. Set qdf = dbsReport.QueryDefs("qryFinalCheckMarks_Crosstab2" ) 'this is the crosstab query ' 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 Report_Close() On Error Resume Next ' Close recordset. rstReport.Close Set rstReport = Nothing 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 ReportHeader_Format(Cancel As Integer, FormatCount As Integer) ' Move to first record in recordset at beginning of report ' or when 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.) If Not rstReport.EOF And Not rstReport.BOF Then rstReport.MoveFirst 'Initialize variables. InitVars End Sub /code I've went through this code line by line as it has run and watched every piece of data (i.e. every mark) come into the report via the Detail_Format subroutine. For some reason though, when it finishes looping through every student in every class and then goes to print preview, it displays the marks in the last record for every student in every class. Anyone out there have any ideas? Thanks! JK |
#3
|
|||
|
|||
Crosstab report repeats last record for all values
Duane,
I have looked at that database and have tried implementing its way of doing a crosstab report. Unfortunately, I have not had any success in replicating what they did. My data structure is somewhat complex in that I have Students Classes and Assignments all joined together with a many to many to many table. Because of this complexity, I was unable to find a way to make it work. In the crosstab reports provided in the Invisible Inc. database, they are querying all of their information from one table. I had read many previous threads where you and others mentioned using this database. Do you have any other suggestions? Please let me know. Thanks! JK |
#4
|
|||
|
|||
Crosstab report repeats last record for all values
I actually created all of those samples about 5 years ago when I worked for
Invisible, Inc. There is really not much difference between a query and a report when it comes to creating reports. -- Duane Hookom MS Access MVP "Jason Kearns" wrote in message oups.com... Duane, I have looked at that database and have tried implementing its way of doing a crosstab report. Unfortunately, I have not had any success in replicating what they did. My data structure is somewhat complex in that I have Students Classes and Assignments all joined together with a many to many to many table. Because of this complexity, I was unable to find a way to make it work. In the crosstab reports provided in the Invisible Inc. database, they are querying all of their information from one table. I had read many previous threads where you and others mentioned using this database. Do you have any other suggestions? Please let me know. Thanks! JK |
#5
|
|||
|
|||
Crosstab report repeats last record for all values
Duane,
The difference between what you did and what I'm trying to do (I think), is that you are grouping on only one variable (the date went with only a user), where I'm trying to group on two (the mark goes with a student in a class). For example, in your CrossTabWithMultiColumn (the same thing I'm trying to do), you are just finding all the dates that go with a certain user. I was able to do this but every mark for each student (i.e. their marks in every class) was put into each class. My subreport in the detail section was linked to the student, but since all the marks for every class are in that subreport, they are all listed. Any suggestion on how to link a subreport to two fields (i.e. have two parents and two children)? Thanks for your help! JK |
#6
|
|||
|
|||
Crosstab report repeats last record for all values
You can enter two field names into your Link Master/Child properties.
-- Duane Hookom MS Access MVP "Jason Kearns" wrote in message oups.com... Duane, The difference between what you did and what I'm trying to do (I think), is that you are grouping on only one variable (the date went with only a user), where I'm trying to group on two (the mark goes with a student in a class). For example, in your CrossTabWithMultiColumn (the same thing I'm trying to do), you are just finding all the dates that go with a certain user. I was able to do this but every mark for each student (i.e. their marks in every class) was put into each class. My subreport in the detail section was linked to the student, but since all the marks for every class are in that subreport, they are all listed. Any suggestion on how to link a subreport to two fields (i.e. have two parents and two children)? Thanks for your help! JK |
#7
|
|||
|
|||
Crosstab report repeats last record for all values
Damn. You're right. And even worked! Thanks Duane!
JK |
#8
|
|||
|
|||
Crosstab report repeats last record for all values
Don't act so surprised!
-- Duane Hookom MS Access MVP "Jason Kearns" wrote in message oups.com... Damn. You're right. And even worked! Thanks Duane! JK |
#9
|
|||
|
|||
Crosstab report repeats last record for all values
Duane,
I don't know if you're still watching this thread, but I was hoping you might know if I can improve the performance on opening this report. Currently, the report is drawing its information from a query that joins 7 tables and results with a little over 5000 records. The subreports (one in the detail and one in the page header to show the labels), are drawing from a query that joins 4 tables and holds over 25000 records. Currently, this report takes about 45 seconds to load. I'm using a dual P4 machine running at 3.0GHz and the data is coming from a datasource on our server across a 10MBit network. Any advice on how I could improve the load time would be appreciated. Thanks! JK |
#10
|
|||
|
|||
Crosstab report repeats last record for all values
There are times when it makes sense to create temporary tables for reporting
purposes. If you have exhausted creating efficient queries with properly set indexes, my next step would be to create a temporary, local mdb with your tables. -- Duane Hookom MS Access MVP -- "Jason Kearns" wrote in message oups.com... Duane, I don't know if you're still watching this thread, but I was hoping you might know if I can improve the performance on opening this report. Currently, the report is drawing its information from a query that joins 7 tables and results with a little over 5000 records. The subreports (one in the detail and one in the page header to show the labels), are drawing from a query that joins 4 tables and holds over 25000 records. Currently, this report takes about 45 seconds to load. I'm using a dual P4 machine running at 3.0GHz and the data is coming from a datasource on our server across a 10MBit network. Any advice on how I could improve the load time would be appreciated. Thanks! JK |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Record sets-changing null values | salmonella | General Discussion | 13 | October 25th, 2005 03:27 PM |
No current record 3021 | hughess7 | General Discussion | 30 | August 19th, 2005 04:39 PM |
Subtotals in Crosstab Report | PeekProp | Setting Up & Running Reports | 0 | August 3rd, 2005 11:36 PM |
Restrict Report To Current Record | Katherine R | Setting Up & Running Reports | 1 | July 15th, 2004 07:23 PM |
Restrict Report to Current Record | Katherine R | Setting Up & Running Reports | 1 | July 15th, 2004 07:23 PM |