Subreport Madness

  • Thread starter Thread starter jschping
  • Start date Start date
J

jschping

Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
 
I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.
 
I want to have a report that looks like a datasheet as follows:

Name:
Grade:

9th Grade 10th Grade 11th Grade
12th Grade

Clubs: Art Computer Chess
Film
Film Magic Film
Checkers


Volunteer: Nursing Meals on Garbage
Nursing
Home Wheels Pickup
Home


Where each row lists all the clubs, volunteer activites, etc that they did
each grade.

There could be multiple values for each one, so a simple query for the whole
report won't do it. I have a separate query to get all the clubs for 9th
grade, then separately the ones for 10th grade. If I do a normal query, I
only can get one field value. (I think. If I'm wrong, please correct me.)

Is there a way to pass the student's primary key to the subreport so the
query it runs will ONLY get back that student's records? That might make it
easier on the computer.

If I used a SQL server free back end would that help?

Thanks!!

Duane Hookom said:
I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.

--
Duane Hookom
Microsoft Access MVP


jschping said:
Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
Click to expand...
Click to expand...
 
I would create one subreport of clubs and volunteering details. Then create a
report of Student and club/volunteer. Add 4 text boxes across the main report
for the grade values. Add 4 copies of the same subreport and set teh link
master child to the student, club/volunteer, and 4 text boxes for the grade.

--
Duane Hookom
Microsoft Access MVP


jschping said:
I want to have a report that looks like a datasheet as follows:

Name:
Grade:

9th Grade 10th Grade 11th Grade
12th Grade

Clubs: Art Computer Chess
Film
Film Magic Film
Checkers


Volunteer: Nursing Meals on Garbage
Nursing
Home Wheels Pickup
Home


Where each row lists all the clubs, volunteer activites, etc that they did
each grade.

There could be multiple values for each one, so a simple query for the whole
report won't do it. I have a separate query to get all the clubs for 9th
grade, then separately the ones for 10th grade. If I do a normal query, I
only can get one field value. (I think. If I'm wrong, please correct me.)

Is there a way to pass the student's primary key to the subreport so the
query it runs will ONLY get back that student's records? That might make it
easier on the computer.

If I used a SQL server free back end would that help?

Thanks!!

Duane Hookom said:
I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.

--
Duane Hookom
Microsoft Access MVP


jschping said:
Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I've tried that, but I have about 17 different aspects, each for all 4
grades. So it still overloads my comp.

I have started using your old vb code for Contcatenate, to make it fewer
queries. I use your function to combine all the rows for 9th grade, etc, into
one field in the query.

Is there a way I can change the delimeter to be a carriage return instead of
the "," ? I tried inserting a vbCrLf and also Chr$(13) & Chr$(10), but in
the query it still turns up with commas only! I don't get it!

Thanks.

Duane Hookom said:
I would create one subreport of clubs and volunteering details. Then create a
report of Student and club/volunteer. Add 4 text boxes across the main report
for the grade values. Add 4 copies of the same subreport and set teh link
master child to the student, club/volunteer, and 4 text boxes for the grade.

--
Duane Hookom
Microsoft Access MVP


jschping said:
I want to have a report that looks like a datasheet as follows:

Name:
Grade:

9th Grade 10th Grade 11th Grade
12th Grade

Clubs: Art Computer Chess
Film
Film Magic Film
Checkers


Volunteer: Nursing Meals on Garbage
Nursing
Home Wheels Pickup
Home


Where each row lists all the clubs, volunteer activites, etc that they did
each grade.

There could be multiple values for each one, so a simple query for the whole
report won't do it. I have a separate query to get all the clubs for 9th
grade, then separately the ones for 10th grade. If I do a normal query, I
only can get one field value. (I think. If I'm wrong, please correct me.)

Is there a way to pass the student's primary key to the subreport so the
query it runs will ONLY get back that student's records? That might make it
easier on the computer.

If I used a SQL server free back end would that help?

Thanks!!

Duane Hookom said:
I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
I don't know what you mean by "17 different aspects."

My solution would involve a single query for the single subreport with
fields like:
StudentID, Grade, ActivityType, and Activity. Activity type would have
values like Clubs and Volunteer.
The Activity field would have values like Computer, Magic, Meals On Wheels,
Garbage Pickup, etc.
The subreport would display the single value of Activity.

The main report would have distinct records of Student and ActivityType (one
record per student per ActivityType).

Add four text boxes across in the Student group header with control sources
the match the unique Grade values. Let's assume:
Name: txt9
Control Source: =9 (or whatever is stored in your grade field)

Name: txt10
Control Source: =10

Name: txt11
Control Source: =11

Name: txt12
Control Source: =12

Place 4 copies of the same subreport in the detail section of the main
report under the four text boxes.
Set the link master child properties

First Subreport (9th Grade)
Link Master: StudentID, ActivityType, txt9
Link Child: StudentID, ActivityType, Grade

Second Subreport (10th Grade)
Link Master: StudentID, ActivityType, txt10
Link Child: StudentID, ActivityType, Grade

--- etc ---

I wouldn't use the concatenate function but you should be able to call it
like:
Concatenate("Your SQL statement",Chr(13) & Chr(10))

--
Duane Hookom
Microsoft Access MVP


jschping said:
Hi,

I've tried that, but I have about 17 different aspects, each for all 4
grades. So it still overloads my comp.

I have started using your old vb code for Contcatenate, to make it fewer
queries. I use your function to combine all the rows for 9th grade, etc, into
one field in the query.

Is there a way I can change the delimeter to be a carriage return instead of
the "," ? I tried inserting a vbCrLf and also Chr$(13) & Chr$(10), but in
the query it still turns up with commas only! I don't get it!

Thanks.

Duane Hookom said:
I would create one subreport of clubs and volunteering details. Then create a
report of Student and club/volunteer. Add 4 text boxes across the main report
for the grade values. Add 4 copies of the same subreport and set teh link
master child to the student, club/volunteer, and 4 text boxes for the grade.

--
Duane Hookom
Microsoft Access MVP


jschping said:
I want to have a report that looks like a datasheet as follows:

Name:
Grade:

9th Grade 10th Grade 11th Grade
12th Grade

Clubs: Art Computer Chess
Film
Film Magic Film
Checkers


Volunteer: Nursing Meals on Garbage
Nursing
Home Wheels Pickup
Home


Where each row lists all the clubs, volunteer activites, etc that they did
each grade.

There could be multiple values for each one, so a simple query for the whole
report won't do it. I have a separate query to get all the clubs for 9th
grade, then separately the ones for 10th grade. If I do a normal query, I
only can get one field value. (I think. If I'm wrong, please correct me.)

Is there a way to pass the student's primary key to the subreport so the
query it runs will ONLY get back that student's records? That might make it
easier on the computer.

If I used a SQL server free back end would that help?

Thanks!!

:

I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Am I correct in assuming that I should use one huge table for this? I have
22 categories of "Activities" some of them numbers (like GPA, SAT scores)
etc. Would all that work?

Also, if I wanted to order the categories in a certain way, how would I? I
wouldn't want to sort it by A-Z or any fixed method. The only thing I can
think of would be to add another field to category type to make it be in
order (number them.)

Does this sound like what you mean?

Thanks!

Duane Hookom said:
I don't know what you mean by "17 different aspects."

My solution would involve a single query for the single subreport with
fields like:
StudentID, Grade, ActivityType, and Activity. Activity type would have
values like Clubs and Volunteer.
The Activity field would have values like Computer, Magic, Meals On Wheels,
Garbage Pickup, etc.
The subreport would display the single value of Activity.

The main report would have distinct records of Student and ActivityType (one
record per student per ActivityType).

Add four text boxes across in the Student group header with control sources
the match the unique Grade values. Let's assume:
Name: txt9
Control Source: =9 (or whatever is stored in your grade field)

Name: txt10
Control Source: =10

Name: txt11
Control Source: =11

Name: txt12
Control Source: =12

Place 4 copies of the same subreport in the detail section of the main
report under the four text boxes.
Set the link master child properties

First Subreport (9th Grade)
Link Master: StudentID, ActivityType, txt9
Link Child: StudentID, ActivityType, Grade

Second Subreport (10th Grade)
Link Master: StudentID, ActivityType, txt10
Link Child: StudentID, ActivityType, Grade

--- etc ---

I wouldn't use the concatenate function but you should be able to call it
like:
Concatenate("Your SQL statement",Chr(13) & Chr(10))

--
Duane Hookom
Microsoft Access MVP


jschping said:
Hi,

I've tried that, but I have about 17 different aspects, each for all 4
grades. So it still overloads my comp.

I have started using your old vb code for Contcatenate, to make it fewer
queries. I use your function to combine all the rows for 9th grade, etc, into
one field in the query.

Is there a way I can change the delimeter to be a carriage return instead of
the "," ? I tried inserting a vbCrLf and also Chr$(13) & Chr$(10), but in
the query it still turns up with commas only! I don't get it!

Thanks.

Duane Hookom said:
I would create one subreport of clubs and volunteering details. Then create a
report of Student and club/volunteer. Add 4 text boxes across the main report
for the grade values. Add 4 copies of the same subreport and set teh link
master child to the student, club/volunteer, and 4 text boxes for the grade.

--
Duane Hookom
Microsoft Access MVP


:

I want to have a report that looks like a datasheet as follows:

Name:
Grade:

9th Grade 10th Grade 11th Grade
12th Grade

Clubs: Art Computer Chess
Film
Film Magic Film
Checkers


Volunteer: Nursing Meals on Garbage
Nursing
Home Wheels Pickup
Home


Where each row lists all the clubs, volunteer activites, etc that they did
each grade.

There could be multiple values for each one, so a simple query for the whole
report won't do it. I have a separate query to get all the clubs for 9th
grade, then separately the ones for 10th grade. If I do a normal query, I
only can get one field value. (I think. If I'm wrong, please correct me.)

Is there a way to pass the student's primary key to the subreport so the
query it runs will ONLY get back that student's records? That might make it
easier on the computer.

If I used a SQL server free back end would that help?

Thanks!!

:

I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
If you want to sort the categories by something other than alpha, you must
add a field to the categories table that stores the desired order.

Does your definition of "category" match my "ActivityType"? Are the
categories like Clubs, Voluteer, etc?

Do you understand how you can use multiple copies of one subreport?

What are the values in your "grade" field? Do you understand how/why to use
4 text boxes for the grades?


--
Duane Hookom
Microsoft Access MVP


jschping said:
Hi,

Am I correct in assuming that I should use one huge table for this? I have
22 categories of "Activities" some of them numbers (like GPA, SAT scores)
etc. Would all that work?

Also, if I wanted to order the categories in a certain way, how would I? I
wouldn't want to sort it by A-Z or any fixed method. The only thing I can
think of would be to add another field to category type to make it be in
order (number them.)

Does this sound like what you mean?

Thanks!

Duane Hookom said:
I don't know what you mean by "17 different aspects."

My solution would involve a single query for the single subreport with
fields like:
StudentID, Grade, ActivityType, and Activity. Activity type would have
values like Clubs and Volunteer.
The Activity field would have values like Computer, Magic, Meals On Wheels,
Garbage Pickup, etc.
The subreport would display the single value of Activity.

The main report would have distinct records of Student and ActivityType (one
record per student per ActivityType).

Add four text boxes across in the Student group header with control sources
the match the unique Grade values. Let's assume:
Name: txt9
Control Source: =9 (or whatever is stored in your grade field)

Name: txt10
Control Source: =10

Name: txt11
Control Source: =11

Name: txt12
Control Source: =12

Place 4 copies of the same subreport in the detail section of the main
report under the four text boxes.
Set the link master child properties

First Subreport (9th Grade)
Link Master: StudentID, ActivityType, txt9
Link Child: StudentID, ActivityType, Grade

Second Subreport (10th Grade)
Link Master: StudentID, ActivityType, txt10
Link Child: StudentID, ActivityType, Grade

--- etc ---

I wouldn't use the concatenate function but you should be able to call it
like:
Concatenate("Your SQL statement",Chr(13) & Chr(10))

--
Duane Hookom
Microsoft Access MVP


jschping said:
Hi,

I've tried that, but I have about 17 different aspects, each for all 4
grades. So it still overloads my comp.

I have started using your old vb code for Contcatenate, to make it fewer
queries. I use your function to combine all the rows for 9th grade, etc, into
one field in the query.

Is there a way I can change the delimeter to be a carriage return instead of
the "," ? I tried inserting a vbCrLf and also Chr$(13) & Chr$(10), but in
the query it still turns up with commas only! I don't get it!

Thanks.

:

I would create one subreport of clubs and volunteering details. Then create a
report of Student and club/volunteer. Add 4 text boxes across the main report
for the grade values. Add 4 copies of the same subreport and set teh link
master child to the student, club/volunteer, and 4 text boxes for the grade.

--
Duane Hookom
Microsoft Access MVP


:

I want to have a report that looks like a datasheet as follows:

Name:
Grade:

9th Grade 10th Grade 11th Grade
12th Grade

Clubs: Art Computer Chess
Film
Film Magic Film
Checkers


Volunteer: Nursing Meals on Garbage
Nursing
Home Wheels Pickup
Home


Where each row lists all the clubs, volunteer activites, etc that they did
each grade.

There could be multiple values for each one, so a simple query for the whole
report won't do it. I have a separate query to get all the clubs for 9th
grade, then separately the ones for 10th grade. If I do a normal query, I
only can get one field value. (I think. If I'm wrong, please correct me.)

Is there a way to pass the student's primary key to the subreport so the
query it runs will ONLY get back that student's records? That might make it
easier on the computer.

If I used a SQL server free back end would that help?

Thanks!!

:

I don't understand why you need multiple queries. You might be able to use a
crosstab query or something else. I can't picture exactly what your data
looks like or what you want your report to look like.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am trying to design a report that lists student's hobbies, clubs, GPA,
etc, for EACH year of high school, all on one report.

I have normalized tables for each category. For example, I have a Clubs
table. Each row has a foreign key to the Students table, and the name of the
club, and what grade he was in that club. So each student will have multiple
fields in each table.

I created a separate query for each grade, so that I could put it all on one
line like this:

CLUBS [grade 9 club] [grade 10 club ] etc.

Each of those fields is from a different query. But since I can't link a
text control to a single query (at least I don't think I can), I had to make
separate subreport for each one of those fields, linked to its own query.

So I have a report with over 120 subreports!

When I go to Print Preview the report shows up totally fine. When I try to
print it (even one page of it) I get memory errors.

Is there a simpler way to have designed this report?

And if there is, is there an easy way to convert it over? Or do I need to do
tons of work over again?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top