Merge multiple records?

[albegadeep]

I'm working on a database to track the contents of a library. Here's a simplified version of the table structure. * indicates primary key, ___FK is Foreign Key (referring to the similar key in another table).

Author table:
*AuthorNum
LastName
FirstName

BookAuthor table:
BookNumFK
AuthorNumFK
*AutoNumber

Book table:
*BookNum
Title


I want to be able to print out the library cards in a particular format (probably through Writer). I figure I need a query that collects the disparate information. The Author line is giving me trouble; if the authors of a particular book are Dr. Bob Jones and Jill Smith, then the book's card should say:
A Discussion of ooBase
Jones, Dr. Bob and Jill Smith

But that's a concatenation of multiple Author entries referenced from one Book entry. My relationships are good (only one-to-many), and my forms and subforms work. Where to go from here?

[eremmel]

SQL approach: You need a feature were you can concatenate over authors with a group by so you end up with one row per book again. Only a few databases support this like MySQL.
An other solution is to join from book multiple times the author table. But then you should be able to get the first, second and third author. Your data model does not have such a field. There is a unique number, but that is not so easy to use.
When you decide to limit the number of authors to e.g. three per book at most, you might slightly denormalize your data model and add first-, second-, third-author FK to your book-table. It is not nice, but will make things easy.

Report approach: When you use ORB to make your document you might introduce a group level at book and a sublevel at author. At the author level you can collect the results to one entry and then show then at one line.

[Villeroy]

The GROUP_CONCAT function is availlable in HSQLDB 2.3 so you may convert your embedded HSQL 1.8 into a true database connection.

I wrote a simple Calc macro to do this trick in Calc so you may dump a query in Calc starting with the field to be grouped and the field to be concatenated. Then select the imported table, call the macro. The resulting table can be found below the source range.

[albegadeep]

I've been scratching my head over how to do this, even with your helpful hints. I'm fine with using macros, if I can just figure out where! I thought of using one in the ooBase query (not the SQL query), but couldn't figure out how. I'd like to do something like this (pardon the pseudocode):

authors[] = getAuthors(???);
int counter = 1;
String accumulator = authors[0];
for ( ;counter<authors.length;counter++)
accumulator = accumulator&", "&authors[counter];

Or possibly something fancier. But how would I insert e.g. a macro call into a query, or intercept the data before it gets inserted into my Writer mail merge?

[Villeroy]

The macro is for Calc only. It reads the first 2 columns of a selected range of spreadsheet cells and dumps a resulting table below the selected range. This has nothing to do with Base, however you can dump any record set from Base into Calc (you can even link cell range to a record set) and then use my macro to generate the type of report you need.

IF you are going to use a true database, just make sure that the database you are going to use has the group_concat function built-in. I know that MySQL has it and HSQL2.x has it too. Then you get much more out of your database without writing a single line of macro code. Instead you control your database much more efficiently via SQL. The additional group_concat is just a minor improvement.