One main form to update several tables?

[nuk]

Hello all,

I've been working on a membership database for a local sports club. The
previous database was previously kept in M$ Works as one big flat-file
database (big in that you had to scroll from side to side to see
everything on the main datasheet - actual file size was a few hundred kb
for 600+ members) with one data-entry form designed to look like our
membership application cards. Even so, the 'normal' way to enter the
data was straight into the main table, basically like a spreadsheet.

The old computer crashed (again), etc. and we have moved to Access 2007.
Someone made up a flat-file every-thing-in-one-table to drag-n-drop the
fields from the most recent backup into the new table, so we have
*something*.

I have actually been puttering around w/ Access before on this same
issue, but it had gotten put on the back burner until now. Now I have
to get something working relatively soon. What I have so far is a
series of tables joined via relationships to cover most of the areas
that we keep track of now, along with more tables and relationships to
expand into other areas once I get the basic membership portion hammered
into shape.

The first thing I wanted to get working is a sort of 'all-in-one' form
for entering all the data we normally pull from a membership
application: name, address, contact, membership, and volunteer info.
Each one of those items now constitutes (at least) one separate table
like this:

<tblName>:ID(pk),LastName,FirstName,MidInit,Title,Suffix

<tblAddress>:ID(pk),NameID(fk),Street1,Street2,City(fk),State(fk),ZIP(fk)

<tblContact>:ID(pk),NameID(fk),Phone1,Phone2,Email

....etc. and so on.

Where I'm running into problems is if I go to 'Create' -> 'More Forms'
-> 'Form Wizard' and select fields from each of the tables so I have a
form to enter data the way it comes off the membership applications like
this:

<Title><FirstName><MidInit><LastName><Suffix>
<Street1>
<Street2>
<City><State><ZIP>
<Phone1>
<Phone2>
<Email>

etc.

....when I save the form and switch to Form view, I can view existing
data that I typed into the tables, but I can't create a new record (the
'New (blank) Record' button at the bottom of the form is greyed out and
doesn't function.

I've gone through the same process to create forms that only use fields
from one table and they work as expected. What am I missing here? What
is keeping this from working?

TIA,

Monte

 

[Allen Browne]

Bind the main form to one table only.
Use subforms to enter the information into the related tables.

 

[nuk]

Bind the main form to one table only.
Use subforms to enter the information into the related tables.

Hello Allen,

Thank you for the response! I did some more reading in that direction,
and I think I get what you're saying. That certainly does work, but it
doesn't give the 'look' that I would like.

Hopefully I'm not wanting something that is terribly unconventional or
unreasonable - I wouldn't think so, but then again I am fairly new at
this. On the one hand, almost all the sample databases and templates
that I find that have forms for entering customer or member name,
address, contact info, etc. almost all have one table that has all that
information in one place - which would appear to make creating a form
such as what I have in mind (all the basic member information in one
form, like I would see it on a hard-copy application, instead of
'trapped' inside a sub-form frame). But on the other hand, when I've
asked about table design on forums and such, I get lectures about how
everything must be 3NF at a minimum, no matter how small or informal the
project or how inexperienced the person creating it - including breaking
down the user information into name, street address, city/state/zip,
phone/phone-type, email/email-type, etc. - which seems to make putting
things into one user-friendly main form kind of awkward.

Maybe I need to just go back and put all the user information
(name/address/contact) in one table, so that displays in the main form
as desired, and then have the other stuff such as memberships, etc. in
subforms as you suggested? Or would writing some sort of query to pull
together the information from the respective tables into one place in
the format of title, firstname, midinit, lastname, suffix, street1,
street2, city, state, zip, phone1, phone2, email? Wouldn't that then be
viewable through various types of forms, including a datasheet view
(which is about what we had in the past in the old database)? Would we
then be able to add new records back through that form to the query to
the respective tables? Sorry if that sounds naive; I have only worked
with very basic single-table queries thus far.

Thanks,

Monte

 

[Allen Browne]

Okay, so the underlying question is how to normalize client data. The short
answer to that question is to use the simplest possible structure that copes
with every situation you need to handle.

In many databases, you don't need to worry about households (many people at
one address), and you only need one address per client (not postal + home +
work.) After double-checking that this handles everything, I'm really happy
to just stick all the address fields into the client table, which gives you
the simple form you want.

In other cases, you do need to handle multiple addresses for a client, but
you don't care about defining households. This means a simple one-to-many
between clients and client addresses. You don't need a separate address
table and then a junction table between clients and addresses. Again, this
makes for a pretty simple interface: the client form has an address panel
that's actually a subform. The subform is in Form view, with its own
navigation buttons. The user can enter address information there just as if
it were all one client form, but they can then click the subform's
nav.buttons to enter a 2nd address. Again, I use this a great deal in my
databases.

The option where you need a true many-to-many between clients and addresses
is rarer IME. You need a separate form for entering the addresses. Typically
you display the clients addresses on the client form, and provide a command
button that pops up the form where a new address can be entered (and you
execute an append query to populate the junction table with the ClientID and
new AddressID.)

Hopefully that helps clarify how to decide on what's the simplest structure
that will handle your needs. Beyond these 3 simple cases (one client with
address fields in the same table, one client with many addresses, and
junction table), is a 4th one where you may need to consider whether the
corporate entity (household or company) could be a client in its own right.
This leads to nesting of clients, and the possibility that clients inherit
addresses in particular contexts (e.g. if you work for IBM New York, you
have IBM's New York address as your work address when we consider you in
that context.) If that sounds interesting, here's an introduction to the
idea of nesting clients like that:
http://allenbrowne.com/AppHuman.html