Re: [libreoffice-users] base + mailmerge difficulty

On 06/27/15 09:17, Ed Beroset wrote:
> Gabriele Ponzo wrote:
> > The list doesn't allow attachments. Could you keep them on a xloud and
> > link
> > here?
>
> The file I tried to attach is available here:
> http://www.beroset.com/school.zip
>
> > What i see is that you should prepare a query where you already have 14
> > results (even null) in a single record.
>
> Yes, I think that would work but I don't know how to do that.
>
> Ed

Hey Ed,

Mail Merge requires each single line to contain all the data for that
particular document.  You can not combine data from several lines onto
one mail merge document.

If you want to have 14 records per student you can either do that
manually in each table, or write some BASIC code to manage it all.  You
could do it if you create a new table with a course number column
numbered 1 - 14 for each student, then fill in each record with each
students course as they register.

Warning Will Robinson!  That can get very messy in a really big hurry.
Like, what will you do if your assumption that 14 classes is the maximum
that any student will take turns out to be incorrect?  What do you do
when hundreds of students only sign up for one or two classes?  Then you
have literally thousands of blank records taking up space and slowing
processing in your database.

What you really need is a report that will group records by student and
combine them all onto one or more report sheet(s).  You can do that by
creating a separate record set, by student, and printing a report for
that student.

I'm not up to speed on LO BASIC but in VBA your code would look
something like this:

create recordset rsStudent = "SELECT Students.StudentID FROM Students;"
with rsStudent
        until rsStudent = EOF
                intStudentID = !StudentID
                create recordset rsClassReport = "SELECT "Schedules"."StudentID"
"StudentID", "Students"."LastName" "LastName", "Students"."FirstName"
"FirstName", "Schedules"."ClassID" "ClassID", "Classes"."ClassTitle"
"ClassTitle", "Classes"."CreditHours" "CreditHours" FROM "Schedules"
"Schedules", "Students" "Students", "Classes" "Classes" WHERE
"Schedules"."StudentID" = & intStudentID &  AND "Schedules"."ClassID" =
"Classes"."ClassID" ORDER BY "StudentID", "ClassID"
                with rsClassReport
                        if not EOF
                                Call your report here.  Hand the query results to the report.
                        end if
                next
                end with
        next
        loop
end with

I left out a lot of details here but that's the general flow of the code
your need.

You need a dynamic query, rsClassReport, that will only pull the records
that pertain to the one student, intStudentID, that you are reporting on.

You will also need to add other columns for current/past student, passed
or failed course, course instructor, class room, class time, etc... to
make this database useful beyond a single semester.

-- 
    _
   °v°
  /(_)\
   ^ ^  Mark LaPierre
Registered Linux user No #267004
https://linuxcounter.net/
****

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted