extracting data from table for title of report

[leahf via AccessMonster.com]

An existing report for teachers' monthly hours consists basically of a
heading with the teacher's name and the month requested, and a detail section
of the date of each day the teacher worked along with the classes & hours
that the teacher taught that day.

An example would be:

Jane Smith 6/2009
----------------------------
Monday June 8 Class 101 2 hours
Wednesday June 10 Class 101 1 hour
Class 200 2 hours
Friday June 12 Class 200 1 hour
Class 201 1 hour


The data comes from a table that is filled (using vba script) when the report
is requested.

The present request is to 1)add the address of the teacher in the title (no
problem, just change the table definition and add the address) and 2)add the
address of the classes that the teacher taught that month (in the title of
the report). Not every class is in the same building.

I can, obviously, change the table definition and add the address of each of
the classes, but is there any way that I can have the list of classes taught
along with the addresses in the title of the report? I would somehow have to
go through the records of the teacher and extract the distinct classes taught
that month.

Any ideas how I can get that information?

Thanks.
Leah

 

[Duane Hookom]

There is a generic concatenate function with demo at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16. This
function will aggregate the child records into a single expression that can
be used in a query or report.

 

[leahf via AccessMonster.com]

Hi Duane,

I am not at work now but I read the link. While it is good for future
reference, I doubt that I will use it now because from what I understand,
that would change the whole building of the report. This was supposed to be
a "simple" change. Am I correct in my assumption?

In either case, I will take a closer look at it when I am at work tomorrow.

Thanks.
Leah

There is a generic concatenate function with demo at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16. This
function will aggregate the child records into a single expression that can
be used in a query or report.

An existing report for teachers' monthly hours consists basically of a
heading with the teacher's name and the month requested, and a detail section

[quoted text clipped - 29 lines]

Thanks.
Leah

 

[Duane Hookom]

"list of classes taught along with the addresses in the title of the report"
I still think the function could do this. If you don't wan to try the code,
you might be able to use a subreport of classes and addresses in the report
header.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I am not at work now but I read the link. While it is good for future
reference, I doubt that I will use it now because from what I understand,
that would change the whole building of the report. This was supposed to be
a "simple" change. Am I correct in my assumption?

In either case, I will take a closer look at it when I am at work tomorrow.

Thanks.
Leah

There is a generic concatenate function with demo at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16. This
function will aggregate the child records into a single expression that can
be used in a query or report.

An existing report for teachers' monthly hours consists basically of a
heading with the teacher's name and the month requested, and a detail section

[quoted text clipped - 29 lines]

Thanks.
Leah

 

[leahf via AccessMonster.com]

Hi,

I am again looking at the explanation of the function. Let's say that I
build the new table with one long string (of varying length) for each teacher.
I am not quite sure how to work with the data. The detail section of the
report comes from each record of the teacher. It is built automatically.
How do I identify the data for each line? Do I need to code this in the
report itself? I hardly ever use code in the report because the previous
form that calls the report has all the script.

Just to remind you, this change was supposed to be a "small change in the
report". I don't know if they would allow me the time to work on the changes.


By the way, if I would be building the report now, it would be perfect (and
this possibility was something that I hadn't known). However, as a "small
change" I can't see it, mainly because of the design of the report.

With all that, however, what sounds like a strong possibility is using a
subreport in the title. I am going to work on that. In either case, if you
can give me a brief explanation as to how to use the generic function, I
would be interested.

Thanks again.
Leah

"list of classes taught along with the addresses in the title of the report"
I still think the function could do this. If you don't wan to try the code,
you might be able to use a subreport of classes and addresses in the report
header.

Hi Duane,

[quoted text clipped - 17 lines]

 

[leahf via AccessMonster.com]

Hi Duane,

Just to tell you thank you. The subform worked beautifully.

Leah

"list of classes taught along with the addresses in the title of the report"
I still think the function could do this. If you don't wan to try the code,
you might be able to use a subreport of classes and addresses in the report
header.

Hi Duane,

[quoted text clipped - 17 lines]