separate records for a couple and mail merge

Discuss the database features

separate records for a couple and mail merge

Postby shalom » Sun Aug 04, 2019 8:59 pm

new to openoffice

I'm setting up a database to track volunteers for a children's ministry. A couple needs to have separate records because their skills and training would likely be different, but I need to combine the names for mailouts. Also, some of them have separate email addresses, but sometimes they used the same one. How would I handle this?

Member table           Address table
mkey akey
Lname address
Fname city
addressKey state
email zip
phone
training
bkgrdck
postiion

note: position options are: board, teacher, volunteer, etc; bkgrdck has the date of the back ground check; training lists the level of training achieved
A husband and wife would have separate records (to track their individual training & position), but would be tied to the same address record. A single person would only have one record tied to the address record.
They may each have different phone numbers or the same number; the same with the email.
For mailouts I need to have one letter for both with the following printed: husband & wife lname

I was thinking about moving the last name field to the address table. What would be the best way to handle this?
Last edited by MrProgrammer on Mon Aug 05, 2019 5:54 pm, edited 1 time in total.
Reason: Added [pre][/pre] tags to the tables to improve readability
Win10; OpenOffice 4.1.6
shalom
 
Posts: 10
Joined: Sun Aug 04, 2019 4:34 pm

Re: separate records for a couple and mail merge

Postby UnklDonald418 » Mon Aug 05, 2019 5:54 pm

Welcome to the Forum.
You will need to consider the trade-offs of different design options. Sometimes a design that looks promising turns out to have problematic side effects so be aware you that may have a false start or two.

From a strict normalization standpoint you would need a separate table for addresses and the record for each person would have an integer field (foreign key) linking that person to a particular address.
One problem with this approach would occur on the data entry form. When entering the data for a person you would probably need a listbox control to allow you to select a matching address. But listbox controls only allow you to choose from existing addresses so if the address doesn't appear on the list you would need a separate form for entering addresses. It would be possible to have both forms on a single form document but you would probably need a two push buttons:
one to save a newly entered address
another to refresh the person entry form so a newly entered address would appear on the listbox list.
When it comes time to do a mail merge you would need a query that for each address would concatenate any matching names in the persons table.

Alternatively, you could include the address in the persons table, resulting in a normalization violation because each address would likely be stored twice. That would simplify the data entry form.
When it comes time to do a mail merge you would need a query that would first group the persons by address and then concatenate names for each address group.

Similarly, you could have a separate table for email addresses and each person record would also have
an integer field (foreign key) linking that person to a particular email address. Again adding complexity to the data entry form.
Or each person could have an email address field, simplifying the data entry form.
To avoid duplicating emails would require a query to group the output by email addresses.

In any case, once you have your tables in working order you should consider upgrading the Embedded Base database connection to a JDBC (split) database connection.
[Wizard] Create a new 'split' HSQL 2.x database
more trade-offs,
increased data integrity and a more powerful database engine but losing some of the functionality of the table design GUI and backups require multiple files in a directory tree rather than a single file.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: separate records for a couple and mail merge

Postby shalom » Tue Aug 06, 2019 1:18 pm

This going to require a bit more thought and some experimenting.
I have a home ec class to teach to teenagers Saturday and need to finish getting ready for it, so I will be back to working on this after that.

As for upgrading the database, that is a suggestion I might consider later. Right now I kind of need the wizards some. I did DBase programming in the 80's (before Windows) which was much different than this. I need to figure out how this works a little better before I consider an option that will remove some functionality.

I have two apps I need to write, the one I referred to here which tracks volunteers and board members for Good News clubs and one to track the achievements for kids from 3 years old through 12th grade (a much more complex app than the other one). So I will have plenty to do to figure this out.
Win10; OpenOffice 4.1.6
shalom
 
Posts: 10
Joined: Sun Aug 04, 2019 4:34 pm

Re: separate records for a couple and mail merge

Postby dreamquartz » Tue Aug 13, 2019 6:18 pm

I concur with UnklDonald418, but for addresses, you might have to consider also different types of address, like Mailing and Physical.
Also Appartments/Unit Numbers, and even floors might be relevant.
There are Street names that make all a bit more complicated, like "123 Alpha Street S.E.", and "123 Alpha Street N.W." in the same town.

The principle of 'many to many' will be present in for sure Addresses, Training, Background, and Position.
The 'many to many' relationship could be in Phone Numbers and Email Addresses.
See for info on 'many to many' relationships between tables the many topics in this forum.

For mail merge there is a add-on to i.e. Thunderbird, called Mail Merge, by Alexander Bergmann, I use.
I create a csv- output of the data I need, and import/open it into the the add-on.
I do this regularly, and it works great.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 726
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests