473,897 Members | 3,192 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

I need a non-example-specific description of how to do dynamic crosstab reports

Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently overwhelmed by useless examples
across the web on how to make "dynamic crosstab reports" without myself
having a basic understanding about how to retrieve and assign recordsources,
etc., from fields in a query to fields in the report. I see all these
examples and I don't know what I can modify and what is essential. I need a
stripped-down, down to basics, tutorial (without a bunch of examples.) or
just simple explanation.

Looking all over the Internet I find numerous examples of "dynamic crosstab
reports" based on a crosstab query. Unfortunately, everything I found,
including Microsoft's stuff, is usually based on a specific example and
doesn't explain the basics of what's really going on. Usually it is based
on a form that prompts the user to enter a beginning and ending date. I
don't care about dates. That's not what my query is about. I just need the
bare-bones code to get the field name and data from a column and plug it
into the form. I do need to filter the results, but that's not my problem
right now. I'm not asking anybody to do my work for me. I just need a
clue. I'm asking, in general terms, how to define and open a datasource
(recordsource?) in a report, search through the column names and assign them
to the labels or text boxes in the header, and search through the fields
(columns)each record, and assign them to textboxes for each student (row).
I think the query will supply the rest of the data with little other
formatting. I wonder why there isn't an easily understood description "out
there" on how to do this without having to revise an example that isn't in
any way applicable to my situation. I know about inserting unbounded text
boxes and all that, but then the examples are so example-specific, that I
don't get the general idea.

Thanks! Rich Hollenbeck

my situation:
I will have 40+plus columns to print, so I will want to display
10-at-a-time, print them, get the next 10, etc. while keeping the same page
formatting for each set of ten columns.

The columns will be activities in a course. The rows will be students
within a course. The data intersecting the rows and columns will be the
score (or grade).

Title of Report: Table of Grades

DataSource: qryTableOfGrade s (cross-tab query)

Grouped by: [courses].[courseDescripti on]
(or the query's representation of it)

Sorted (within each course) by: [students].[student name]
(or the query's representation of it)

Each course will perhaps have different activities, so I could filter the
report by the course displayed in the form from which the report is called.
Nov 13 '05 #1
1 3345
Thank you. Here's how I got it to work:

Option Compare Database
Option Explicit

Private Sub PageHeaderSecti on_Format(Cance l As Integer, FormatCount As
Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set rst = db.OpenRecordse t("select * from qryTableOfGrade s")

rst.MoveFirst
j = -1
i = 0

For i = 0 To rst.Fields.Coun t - 1
If rst.Fields(i).N ame Like "*ID" Then GoTo skip_it
j = j + 1

Select Case j
Case 0
Me.Label0.Capti on = rst.Fields(i).N ame
Case 1
Me.Label1.Capti on = rst.Fields(i).N ame
Case 2
Me.Label2.Capti on = rst.Fields(i).N ame
Case 3
Me.Label3.Capti on = rst.Fields(i).N ame
Case 4
Me.Label4.Capti on = rst.Fields(i).N ame
Case 5
Me.Label5.Capti on = rst.Fields(i).N ame
Case 6
Me.Label6.Capti on = rst.Fields(i).N ame
Case 7
Me.Label7.Capti on = rst.Fields(i).N ame
Case 8
Me.Label8.Capti on = rst.Fields(i).N ame
Case 9
Me.Label9.Capti on = rst.Fields(i).N ame
Case 10
Me.Label10.Capt ion = rst.Fields(i).N ame
Case 11
Me.Label11.Capt ion = rst.Fields(i).N ame
Case 12
Me.Label12.Capt ion = rst.Fields(i).N ame
End Select

skip_it:
Next
rst.Clone
Set rst = Nothing
End Sub
Private Sub Report_Open(Can cel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordse t("select * from qryTableOfGrade s")
rst.MoveFirst

j = -1
i = 0

For i = 0 To rst.Fields.Coun t - 1
If rst.Fields(i).N ame Like "*ID" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.field0.Contr olSource = rst.Fields(i).N ame
Case 1
Me.field1.Contr olSource = rst.Fields(i).N ame
Case 2
Me.field2.Contr olSource = rst.Fields(i).N ame
Case 3
Me.field3.Contr olSource = rst.Fields(i).N ame
Case 4
Me.field4.Contr olSource = rst.Fields(i).N ame
Case 5
Me.field5.Contr olSource = rst.Fields(i).N ame
Case 6
Me.field6.Contr olSource = rst.Fields(i).N ame
Case 7
Me.field7.Contr olSource = rst.Fields(i).N ame
Case 8
Me.field8.Contr olSource = rst.Fields(i).N ame
Case 9
Me.field9.Contr olSource = rst.Fields(i).N ame
Case 10
Me.field10.Cont rolSource = rst.Fields(i).N ame
Case 11
Me.field11.Cont rolSource = rst.Fields(i).N ame
Case 12
Me.field12.Cont rolSource = rst.Fields(i).N ame
End Select

skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub

"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:jN******** ***********@nwr ddc01.gnilink.n et...
Hello Newsgroup. You have all been very helpful in the past and I thank
you. I try to ask relevant questions so that they don't just benefit me,
but also benefit the group. I'm currently overwhelmed by useless examples
across the web on how to make "dynamic crosstab reports" without myself
having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these
examples and I don't know what I can modify and what is essential. I need a stripped-down, down to basics, tutorial (without a bunch of examples.) or
just simple explanation.

Looking all over the Internet I find numerous examples of "dynamic crosstab reports" based on a crosstab query. Unfortunately, everything I found,
including Microsoft's stuff, is usually based on a specific example and
doesn't explain the basics of what's really going on. Usually it is based
on a form that prompts the user to enter a beginning and ending date. I
don't care about dates. That's not what my query is about. I just need the bare-bones code to get the field name and data from a column and plug it
into the form. I do need to filter the results, but that's not my problem
right now. I'm not asking anybody to do my work for me. I just need a
clue. I'm asking, in general terms, how to define and open a datasource
(recordsource?) in a report, search through the column names and assign them to the labels or text boxes in the header, and search through the fields
(columns)each record, and assign them to textboxes for each student (row).
I think the query will supply the rest of the data with little other
formatting. I wonder why there isn't an easily understood description "out there" on how to do this without having to revise an example that isn't in
any way applicable to my situation. I know about inserting unbounded text
boxes and all that, but then the examples are so example-specific, that I
don't get the general idea.

Thanks! Rich Hollenbeck

my situation:
I will have 40+plus columns to print, so I will want to display
10-at-a-time, print them, get the next 10, etc. while keeping the same page formatting for each set of ten columns.

The columns will be activities in a course. The rows will be students
within a course. The data intersecting the rows and columns will be the
score (or grade).

Title of Report: Table of Grades

DataSource: qryTableOfGrade s (cross-tab query)

Grouped by: [courses].[courseDescripti on]
(or the query's representation of it)

Sorted (within each course) by: [students].[student name]
(or the query's representation of it)

Each course will perhaps have different activities, so I could filter the
report by the course displayed in the form from which the report is called.

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2495
by: Sofia | last post by:
My name is Sofia and I have for many years been running a personals site, together with my partner, on a non-profit basis. The site is currently not running due to us emigrating, but during its last year we got traffic of between 2000 - 2500 unique visitors per day. We are now about to re-launch the site from Sweden and we need to purchase a script to run it. Having looked at what is available on the net I have realised that we need a...
6
9130
by: Chris Foster | last post by:
I am trying to implement a very fast queue using SQL Server. The queue table will contain tens of millions of records. The problem I have is the more records completed, the the slower it gets. I don't want to remove data from the queue because I use the same table to store results. The queue handles concurrent requests. The status field will contain the following values: 0 = Waiting
3
1806
by: Jack A | last post by:
OK Guys. I'm fed up of the query below taking too much time. I CANT change the query since it is generated by a 3rd party product. I can change indexes and add new indexes though. The schema of the tables is given below. The most expensive operation is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login index as a covering index to cover the query but that has not seemed to help. Any ideas, suggestions are most welcome...
2
1886
by: Oliver Burnett-Hall | last post by:
I'm trying to move to using tableless page layouts, but I've come across what appears to be a bug in IE5's rendering that I can't find a way to overcome. The page has a sidebar to the left of the main content area. The main content are has several subsections, each of which starts with a mini-menu of four links. I want to have these laid out across the full width of the column. Here's an ASCII attempt to show the desired page layout:
9
2948
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with a device. The definition of the table is as follows: CREATE TABLE devicedata ( device_id int NOT NULL REFERENCES devices(id), -- id in the device
1
2184
by: Peggy Go | last post by:
Hi! I've downloaded postgreqsql-base-7.2.4.tar.gz but it says in the readme file that "This distribution also contains several language bindings, including C, Perl, Python, and Tcl, as well as a JDBC driver. The ODBC and C++ interfaces have been moved to the PostgreSQL Projects Web Site at http://gborg.postgresql.org for separate maintenance. "
5
2962
by: HotRod | last post by:
I am new to this so please go easy. We currently have some students doing some work on some web based tracking documents for us. They are currently using VB .net to develop what we requested. Anyway I've been calling my local ISP's and no one supports .net it seems to be all apache and MySQL. I'm wondering if everyone here can answer a few questions. 1) Can I run vb .net web pages on a regular IIS server without the .net extensions? 2)...
13
6993
by: Alex Vinokur | last post by:
Why do we need non-virtual destructor? Because of vtable? Alex Vinokur email: alex DOT vinokur AT gmail DOT com http://mathforum.org/library/view/10978.html http://sourceforge.net/users/alexvn
3
2650
by: Dean Craig | last post by:
I'm working with the new ASP.NET AJAX Control Toolkit. I have a map that has several key areas (hot spots) where when the user hovers over them, I want to pop up a small window with information in it (text, graphics, whatever). I am using the asp:ImageMap control and I can use the asp:circlehotspot control, but that only allows me to have a single hotspot. What I want to do is create a new custom control that derives everything from...
43
4958
by: Frodo Baggins | last post by:
Hi all, We are using strcpy to copy strings in our app. This gave us problems when the destination buffer is not large enough. As a workaround, we wanted to replace calls to strcpy with strncpy. That is, replace calls to strcpy with say, my_strcpy(dest,src) which will internally find the destination buffer length. For this we need to know the destination buffer size. For statically allocated strings sizeof is returning the length of the...
0
9990
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9837
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10939
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
8031
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7183
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5872
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4697
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4292
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3299
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.