Setting up "a form", "List", "Mail Merg" ?

[lockhands]

I am one member of a Jazz Band and at the request of the Leader, I have been trying to sort out a method where he could provide all the members of the Band with a “playlist” for each of the up and coming Gigs we are to perform at.
At present we have a repertoire of about 150 tunes and these are currently stored as a database with 6 headings :- “Id” , “Tune Name”, “Key” “Misc”, “Page” and “Arrangement”. The “Id” field is a number and the rest are “text”.
The Leader would like to be able would like to be able to individually select Tunes from the Database list by using the “Id” number for each tune name to construct a new list, one tune at a time. That new list of about 10 tunes, making 10 rows each would show the complete data for each tune. I assume I would need to provide him with a Table of some form in which the first cell would call up the various Database “Id” numbers and the rest of the table row would be filled with the “Tune Name” etc. from the Database details.

I have tried several different methods but only managed to reproduce the whole Database List.
I would be most grateful for any help in this matter.

Lockhands.

Open Office V. 4.1.1
Microsoft Windows 7, Service Pack1 64bit

[Villeroy]

On this forum we can not teach database design; and we can not teach programming.

[thomasjk]

I suggest you read this old but excellent article on mail merge http://www.freesoftwaremagazine.com/art ... office_org. Also this blog psot which is also old but still applicable http://openoffice.blogs.com/openoffice/labels/.

[MTP]

Maybe something like this?

[Villeroy]

And a possible basis for a mail merge from MTP's database could be the following query:

Code: Select all

SELECT "Lists"."ListName", "Keys"."Key", "Tunes"."TuneName", "Tunes"."Misc", "Tunes"."Page", "Tunes"."Arrangement" 
FROM "ListTunes", "Lists", "Tunes", "Keys" 
WHERE "ListTunes"."ListID" = "Lists"."ListID" 
	AND "ListTunes"."TuneID" = "Tunes"."TuneID" 
	AND "Tunes"."KeyID" = "Keys"."KeyID"

which simply selects all fields from all related tables except for the meaningless ID numbers.

Same query in design view:

all fields of all linked tables except IDs

[lockhands]

Hello Villeroy,

Thanks for your offering. I will try that and see how it goes.
The main problem seems to be that although the information can be extracted from a database of whatever sort chosen, it does not seem possible to print/view the final page in the format required.

NOHS Tune Listc_Modified04.pdf

(75.27 KiB) Downloaded 120 times

Any ideas?

Cheers

CR.

[Villeroy]

Copy and paste the query into SQL view.
Save and close the query.
Right-click the query icon > Create Report

[lockhands]

Hello Villeroy,

And thanks again for your suggestion.
I have followed your rules as above, but only manage to print out a database type document - see attacments.

TuneList02.pdf

(50.93 KiB) Downloaded 133 times

.
I have also included the modified DB doc that was provided by the other provider.

TuneList02.pdf

(50.93 KiB) Downloaded 133 times

.
Please check these, if you don't mind and advise me on where I am going wrong.

Thanks,


CReilley

[Villeroy]

A report is just an ordinary Writer document with a table. When you open the report for editing (right-click>Edit) you see that the table is stored as a blue print with the header row and a second row of dummy values. You can resize rows and columns, you can format cells and outside the table you can do what you want.