Dynamic Crosstab + Reports

[deejayquai]

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

[Larry Linson]

<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

[deejayquai]

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

[Larry Linson]

<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

[deejayquai]

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!