473,396 Members | 1,703 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Dynamic Crosstab + Sub Report

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 'dynamic crosstab query'
but I don't know how to!! I've read the "How to..." on the Microsoft
site but it mainly gives an example rather than explain the basics,
which I can't work out.

My context is:

I'm developing an end of year 'Group Member' report for school students
in PE.

Crosstab Query "qryTest" shows [Group Member ID] as the row and then
[Activity] as columns with an [Activity Attainment] as the value. The
activities vary for each group member and can be more or less.

I wish to include this as a sub-report "rptTest" in my main report
linked to the [Group Member ID]. My main report includes other
performance information such as Effort Grades etc and is working fine.
The main report will be previewed from a "frmStudentPerformance" form
and is linked from the [Group Member ID] field on that form. Like I
said- I can get this to work fine as a normal cross-tab report but once
the [Activity] changes between Group Members it falls down.

Regards

David

Nov 13 '05 #1
3 3569
David:

What you trying to do is quite complex and I do not know the specifics of
your implementation, so my comments here are meant in a general sense.

In the KB "how to" you refer to, the recordset for the dynamic crosstab is
generated in the Report Open event, which unless you are opening and closing
the report/subreport for each group member, the recordset will be generated
the first time the subreport is open and the activities associated with that
particular group member will remain for subsequent group members.

What needs to happen is to regenerate the recordset (which will update the
field names for Activities) for each group member. Depending on how your
report/subreport is setup, you may be able to do this in a PageHeader or
GroupHeader event. You may, although I have not tested this, simply be able
to call the Report_Open event procedure for the subreport from one of these
event procedures, but the general idea is to update the recordset behind the
dynamic crosstab subreport for each group member.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<de********@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
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 'dynamic crosstab query'
but I don't know how to!! I've read the "How to..." on the Microsoft
site but it mainly gives an example rather than explain the basics,
which I can't work out.

My context is:

I'm developing an end of year 'Group Member' report for school students
in PE.

Crosstab Query "qryTest" shows [Group Member ID] as the row and then
[Activity] as columns with an [Activity Attainment] as the value. The
activities vary for each group member and can be more or less.

I wish to include this as a sub-report "rptTest" in my main report
linked to the [Group Member ID]. My main report includes other
performance information such as Effort Grades etc and is working fine.
The main report will be previewed from a "frmStudentPerformance" form
and is linked from the [Group Member ID] field on that form. Like I
said- I can get this to work fine as a normal cross-tab report but once
the [Activity] changes between Group Members it falls down.

Regards

David
Nov 13 '05 #2
Thanks David but I don't quite understand your advice- I'm pretty basic
at this apologies. Are you suggesting that I should set an instruction
up in the event for the page header for the sub-report to referesh the
data? If so what is the syntax?

Regards

David

Nov 13 '05 #3
David:

Yes, the Page Header or another event procedure that represents the point at
which you are switching group members. The purpose is to regenerate the
recordset every time the group member changes. What event you do this in
depends on exactly how your report and subreport are setup, which I do not
have all of the specifics.

The syntax would or should be very similar to the syntax that is in the
Report_Open event procedure of your subreport, or at least very similar to
the Report_Open procedure that is in the following KB article:

http://support.microsoft.com/default...b;en-us;328320

The recordset, which is based on a crosstab query, needs to be regenerated
for each group member, because as I understand you, the activities differ
between individual group members. Therefore the pivot columns will change
with each group member, and you want those changes reflected in the
subreport. Regenerating the recordset is the only way to achieve this since
the recordset fields collection is used to determine the column headings in
the dynamic crosstab report (see same KB article).

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<de********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks David but I don't quite understand your advice- I'm pretty basic
at this apologies. Are you suggesting that I should set an instruction
up in the event for the page header for the sub-report to referesh the
data? If so what is the syntax?

Regards

David
Nov 13 '05 #4

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

Similar topics

1
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...
1
by: Richard Hollenbeck | last post by:
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...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
2
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...
4
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...
1
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...
13
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually...
3
by: Niranjan | last post by:
I want create a report based on the crosstab query which normally returns about 50 - 60 columns. The columns have names of the counties and they keep changing for every session. Is there a way to...
14
ollyb303
by: ollyb303 | last post by:
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...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
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...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.