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!!
