By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Exporting into Excel

P: n/a
Hi

First of all my level = basic!

My question= I have a report containing student performance data with a
sub-report showing the subjects they have taken that year. I run the
report group by group, therefore returning many records, page by page,
within the report. This works fine! BUT I have been unable to develop a
dynamic crosstab query/report for the sub-report so I can't show the
subjects in a table form as the subjcet columns vary between students,
instead they currently show as a list- taking up too much space. I've
tried going through the example on the MS website and it I just get
lost everytime.

Trying to overcome this I have thought about exporting the two queries
the report/sub-report uses into an excel file and then use a template
worksheet to bring all the data together into the desired outcome.
Where I am stuck is- how can I consolidate the two queries into one
worksheet and how can I find a way in looping through the whole group
of students (records) so that my excel file consists of many template
reports, emulating what I was doing in Access?

The result would enable me to overcome my dead-end with the dynamic
crosstab query/report.

Your help is appreciated- thanks

David

May 20 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Where I am stuck is- how can I consolidate the two queries into one
worksheet and how can I find a way in looping through the whole group
of students (records) so that my excel file consists of many template
reports, emulating what I was doing in Access?


If you could present some hypothetical data with explanation underneath
it, someone might be able to help you. Just a suggestion.

Chirag Shukla.

May 21 '06 #2

P: n/a
OK.

qryFinalReport is the query that the current report- RptFinalReport is
sourced from. It contains key fields from several related tables,
including a GroupMemberID (unique ID), a GroupCodeID, Name and Surname
Field, Class and Year Fields and fields for Attainment and Effort.

qryReportActivities is the query that the sub-report is sourced from
for the final report. It contains key fields from two related tables,
including the GroupMemberID (unique ID) as above, and a field-
Activity- which is the subject and a Grade field.

The database reports- showing 1 group member having done many
activities in the year, which can be different in number and type
according to their options.

Also, each group member is part of a group, defined by GroupCodeID, in
my database I can choose to produce a report on a group member by
member basis or as a batch according to a group code.

My relationships are:

tblGroupCode (1) to tblGroupMember (many)
tblGroupMember(1) to tblActivityAssessmentProfile (many)

I can produce all of this in the rptFinalReport but the activities show
like this:

Football
Swimming
Tennis etc

Rather than going across the page and I've learnt that I would need to
nest in the main report a dynamic crosstab report due to the variation
in activities. I've tried and failed many times on this as my
programming just isn't up to it.

So my workaround- would be to export qryFinalReport into excel- giving
a header and row on a worksheet, to have a template worksheet on top to
refer to the row and produce the emulated Final Report. But my
sticking point is how to consolidate the qryReportActivities into the
workbook, which will show several rows per group member (1 for each
activity taken) and how to report as a batch if I wanted to view an
entire group??

Many thanks

David

May 21 '06 #3

P: n/a
de********@gmail.com wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:
I can produce all of this in the rptFinalReport but the
activities show like this:

Football
Swimming
Tennis etc

Rather than going across the page
If you want the subreport to do this
Football Swimming Tennis
you can do it with a "labels type" sub-report. No Crosstab needed.

Here's how.
create a blank report. set it's dimension to 1 inch wide by 1/4ths
tall..

From the Menu, File->Page Setup->Columns
set the number of columns to 6
set the Column Layout option to "Across, then Down."

add your fields, insert as a sub report.

View!

and I've learnt that I would need to nest in the main report a dynamic crosstab report due
to the variation in activities. I've tried and failed many
times on this as my programming just isn't up to it.


Today, you learnt a better way.
--
Bob Quintal

PA is y I've altered my email address.
May 21 '06 #4

P: n/a
Bob

I didn't believe it would be that simple but with a bit of
experimenting it worked (albeit the label sizes are a bit erratic as I
have to accommodate the longest string) but you've helped me overcome
something I've been stuck on for over a year! I was certain I needed
to make a dynamic crosstab query...!

Many, many thanks and to anyone else that read my post and was giving
it a thought.

David

May 21 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.