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

<deejayquai@gmail.com> wrote
[color=blue]
> 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.[/color]
[color=blue]
> 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?[/color]

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

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

<deejayquai@gmail.com> wrote
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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

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!