473,396 Members | 2,013 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 + Reports

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 anyone
recommend a book or a website that goes through the basics of how to
build dynamic crosstab queries in order to create reports that allows
column fields to dynamicaly change according to the source data?

Regards

David, UK

Jan 23 '06 #1
4 2429
<de********@gmail.com> wrote
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 anyone recommend a book or a website that goes
through the basics of how to build dynamic crosstab
queries in order to create reports that allows column
fields to dynamicaly change according to the source data?


Could you, would you, explain in straightforward, simple language
(preferrably with a real-world example) what you mean by "dynamic crosstab
queries in order to create reports that allows column fields to dynamicaly
change according to the source data" ?

Whatever that definition turns out to be, in detail, I am certain that this
will require some significant amount of VBA code, so you may just have to
face up to expending necessary time and energy to climb at least partway up
the VBA learning curve.

Larry Linson
Microsoft Access MVP
Jan 23 '06 #2
Larry thanks for your reply.

I'm developing a student sports database. I'm capturing achievement in
sports subjects i.e. Swimming Grade A, Tennis Grade B etc. I would
like to include a summary of what a student has achieved in their
chosen sports subjects at the end of the year. I would like to be able
to do this in the format of a table with the sports subjects as column
headings and the grades as values, and then include this as a
sub-report in an overall year record of achievement. My problem is
that students choose different subjects.

I've tried going through the MS KB example and altering it but it is
just a bit beyond me at the moment. I've done a lot of searching of
the group and I'm currently looking at an idea someone had of forcing
the column names in the properties of the query and then hiding empty
column headings in the report through VBA; however I fall down when it
comes to trying to automatically format the size/column widths
according to hidden columns.

I hope that explains it in simple terms and thanks for your interest.

David

Jan 23 '06 #3
<de********@gmail.com> wrote
I'm developing a student sports database. I'm
capturing achievement in sports subjects i.e. Swimming
Grade A, Tennis Grade B etc. I would like to
include a summary of what a student has achieved
in their chosen sports subjects at the end of the year.
I would like to be able to do this in the format of
a table with the sports subjects as column headings
and the grades as values, and then include this as a
sub-report in an overall year record of achievement.
My problem is that students choose different subjects.
How are you storing your data? That is, what tables, what fields? Cross-tabs
are _for_ taking a Field and making it into a Column Heading.

If there's only going to be a single student in each CrossTab, then I don't
see a problem with them being enrolled in different subjects.
I've tried going through the MS KB example and
altering it but it is just a bit beyond me at the moment.
I've done a lot of searching of the group and I'm
currently looking at an idea someone had of forcing
the column names in the properties of the query and
then hiding empty column headings in the report
through VBA; however I fall down when it
comes to trying to automatically format the
size/column widths according to hidden columns.


What is the number of the KB article to which you refer? We'd just as soon
not have to search and hope we find the same one.

Larry Linson
Microsoft Access MVP


Jan 24 '06 #4
Hi Larry

Student data is stored in tblGroupMembers, capturing their overall
grades, with [Group Member ID] being the primary key. Sports
activities are captured in tblAssessmentActivityProfile, with
[Assessment Activity Profile ID], [Activity] and [Effort Grade] as the
relevant fields. There's a one-2-many relationship between
tblGroupMembers and tblAssessmentActivityProfile via the [Group Member
ID] field. There is only going to be a single student in each crosstab
but reports are previewed/printed for a whole group list of around 30
students in a single process.

Basically the report consists of a series of comments that are
automatically entered according to each students overall performance
achivement. Then at the moment I'm just having to list the activities
and efforts vertically as a sub report, which means the report takes up
too much room and looks messy. I can place in a crosstab sub-report of
the activites and efforts, but it ends up showing activities that
haven't been taken for some students and shows blank fields etc.
Usually a student will take around 8 activities a year but this could
be slightly more or less.

When I've posted before someone briefly mentioned that it could be the
case of re-querying the crosstab in a Page Header command in the
sub-report but I don't quite get it- sorry for being dense!

I've gone through the KB article- "How to create a dynamic crosstab
report in Access 2002"-
http://support.microsoft.com/default...b;en-us;328320

thanks again

David, UK
Microsoft Access Casualty!

Jan 24 '06 #5

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

Similar topics

7
by: SharkSYA | last post by:
After canvassing ideas it appears that the way I need to do it is not possible.There are 126 rooms, more to be added, and it needs to print a report or reports showing 76 days of bookings. There...
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...
3
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...
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...
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...
0
by: Peter Herath | last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row...
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...
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: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
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,...
0
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...
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...

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.