Creating multiple records from a single form

[secretary]

I'm in the process of developing a membership database wherein a Membership# is given to either a single person, or shared by a couple. I am wondering if it is possible for each record in a single form to inform up to two records in a table. i.e. filling in a single form will record values in two consecutive table records.



I realize it is probably poor practice to start with a form and work towards linking it with table data (instead of the other way around). In any case, I feel like my only other option would be to create separate "1st Member" and "2nd Member" tables to link to the form, but since each table would contain the same fields, I don't see this as the best way to go.

I feel like the root of my problem deals with starting with properly normalized tables; when conceptualizing the database, I struggled with how I would organize the data. My main issue is that the Membership# is not necessarily unique between two people, since they may be a couple. And yet I need to be able to treat couples both as a SINGLE UNIT (sharing mailing address, phone number etc.) and as SEPARATE entities (with unique attributes like birthdays and names), while keeping in mind that not all members are couples.

[June7]

Here is one approach, holds to normalization principles.

tblMembNums
MembNum (PK)
MembType
DateIssued
Address (assumes only one address per member number)

tblMembers
MembNum (FK)
LastName
FirstName
Birthdate
Phone

If each membership cannot have more than 2 individuals, then maybe don't need strict normalization.
tblMembers
MemberNum
MemberAddress
Memb1LastName
Memb1FirstName
Memb1Birthdate
Memb1Phone
repeat for Memb2

I know this could mean a lot of empty fields. Balancing normalization with ease of data entry is a delimma faced in any db design. I have tables that could probably go another step or two for normalization but I decided 'enough already', I can get what I want with tables as is, even if I do end up with quite a few empty fields. Works best for the primary report output I have to do. I did have to resort to some UNION queries to rearrange data so could be graphed, otherwise no real issues.

[rpeare]

I would probably do it slightly differently than June but a good rule of thumb is to break your data down into tables that will leave the least possible number of items blank and have the least possible repetition (ideally a database should have no duplication of information at all).

So for instance do you want everyone on an account to receive any special notices, offers, bills or do you just want to send it to the primary account holder. in theory the most efficient way to do this would be

Tbl_Members
MemberID
OtherMemberFields...

Tbl_Primaries
MemberID
Primary (yes/no)

but in reality most people would put the primary account holder field in the member table but it's something to consider when you design your table.

[secretary]

@rpeare

I'm trying to figure out how that would work. What sort of fields would go in each table? In addition, the case would be that only one notice, bill, etc. would be associated with each MemberID (couple or single). What would be the purpose of the Primary (yes/no) field?

[rpeare]

You are talking about a membership database, typically with a membership system you don't send out a bill once, you have a recurring monthly charge.

So you would not want a bill to go to every single person in your member table you would only want it to go to certain people (the primary account holder). Likewise, let's say your membership database was at a store (like costco) if you were sending out a coupon booklet you wouldn't want to send out a coupon booklet to everyone on an account, you'd want to send it to 1 person, the primary account holder.

Secondly not knowing the specifics of your membership system I can't really comment on the rest of it.

Let's say for example your membership system is for a YMCA, you may have a family that has 3 or 4 foster kids and they have memberships for all the kids. Now let's say that one of those foster kids is removed from one home and either adopted or put in another foster home, and that family also has a membership. You wouldn't create a new member record you'd just want to transfer a member from one family to another.

There are all kinds of things like this to consider but if you follow the basic rule I said earlier (keeping the minimum possible data without storing a lot of empty fields) you should be ok. If you want to be a lot more detailed about how your membership system works (don't just comment on what I wrote) and what you want to be able to do with it it'd be a lot easier to give you advice.

[secretary]

So far I've developed the following:

Tbl_Members
Membership# (PK)
MailingAddress
City
Province
PostalCode
HomePhone
MemberSince
MemberType
Zone#

Tbl_MemberDetails
BirthDate (PK)
Salutation
FirstName
LastName
Membership# (FK)

There are other tables but these two are what I consider to be the vital ones, and managing the others will be easy after I get these in place.

My dilemma is:

I want to use the FORM as I've designed it to enter membership information (attached first post).

A SINGLE form can receive up to two entries (1st and 2nd member) that both relate to the SAME Membership#, MemberType, Address, etc.

However, the way the tables are now (with sample data) I have 6 records in Tbl_Members and 9 records in Tbl_MemberDetails i.e. Six unique "members" but nine individuals. I would expect to have 6 forms (not 9) because each "member" should have their own form.

How should I organize the tables if I want to use the form the way it is?

[rpeare]

You're using June's data structure so I'll let him answer but I would really recommend you not us any spaces or special characters (#, $, @, etc) marks in your field or object names. They can really make things difficult on you in the long run.

[secretary]

The data structure is free to be altered. I have simply told you what I've been working with. It just so happens that June's proposal was similar to my original design. If you have any suggestions, I'll take any help I can get. You see, I created my tables and then proceeded to form design without really using my table data to create my forms directly.

My concern is data entry, as I've discussed it.

I'll keep the object name suggestion in mind!

[June7]

Don't use birthdate as PK. Surely more than one member will have the same birthdate. I would never use a date field as a PK.

Unless there are other tables dependent on MemberDetails, no PK is needed. But can have an Autonumber field just in case, won't hurt.

Yes, spaces, punctuation, and special characters in names can cause problems, especially in code. Exception is the underscore but I don't even use that.

If you stay with multiple tables, use a form/subform arrangement. Main form bound to Members table and subform bound to MemberDetails.