Relationships and Tables

[Carolyn]

I’m trying to help a non-profit organization in my spare time (of which I
have none). I volunteered to set up a database having no idea it would be
this never-ending maze and that learning ACCESS is nothing like the other MS
Office programs. After numerous ACCESS tutorials, I have come to the
conclusion that although forms and queries all seem relatively understandable
once your tables are set up, I am not really sure how to set up my tables and
their relationships! The real problem is that you have to follow the logic
of why you’re doing it in the first place and I haven’t quite got that yet.

Basically the information I’m working with can be characterized as a contact
list. There are new queries that come in and older contacts. Some of these
contacts also turn out to be donors, although at this point I’m more
interested in sorting out who is interested in which type of program. Some
of the programs are educational and have prerequisites. Some are purely
social. We would also invite people to different types of programs based on
their expressed interests, location, gender, age, and so forth. We would
also track people so that we could know when they were last seen at a program
or what kinds of programs they have come to in the past. We would also keep
a list of their interests in case we come up with new programs. We would
also keep a list of people’s skills that we might access.

I came up with one really big table with about a kazillion fields. But for
the new people I felt I should have a second table although some of the
information is the same and later they might end up on the not so new
people’s table. I am in doubt about this. For example, with new people we
want to know where they heard about it – was it the website, a flyer, word of
mouth, etc. We also want to track our followup carefully if they express
interest in certain ongoing programs. If they express interest in YTT, we
want to know when they contacted us, how they contacted us (phone, email,
etc.), when we called them back, if they are signed up for the next program
and if not why. Also I was wondering if I shouldn’t split the big table
because you can’t follow the data when you get to the end of it. Or I guess
that wouldn’t matter if you’re actually inputting in forms and taking data
out with queries. I’m still thinking Excel I guess.

Any suggestions to get me going?

 

[Mike Painter]

I'm trying to help a non-profit organization in my spare time (of
which I have none). I volunteered to set up a database having no
idea it would be this never-ending maze and that learning ACCESS is
nothing like the other MS Office programs. After numerous ACCESS
tutorials, I have come to the conclusion that although forms and
queries all seem relatively understandable once your tables are set
up, I am not really sure how to set up my tables and their
relationships! The real problem is that you have to follow the logic
of why you're doing it in the first place and I haven't quite got
that yet.

Basically the information I'm working with can be characterized as a
contact list.

There is a contact list that can be built by the Access wizard that might
get you started.

I came up with one really big table with about a kazillion fields.

Tables with large numbers of fields are almost always poorly designed.

 

[Tom Wickerath]

Hi Carolyn,

In addition to Mike Painter's statement about the kazillion fields, which is
true, I recommend that you spend some time reading up on database design.
Here are some links to get you started. Don't underestimate the importance of
gaining a good understanding of database design. Brew a good pot of tea or
coffee and enjoy reading!

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization"

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)

As you are designing your database (on paper first), you should make an
effort to use naming conventions and avoid using any reserved words:

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp

Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

You indicated: "We also want to track our followup carefully if they express
interest in certain ongoing programs." Does your current database design
require that a new field be added to the table if a new program is offered?
If so, this should be a red flag that the design has a problem. Just like
paragraphs should represent a single subject in English writing, a table
should represent a single subject. The fields (attributes) in the table
should describe this subject, similar to pronouns describing a noun.
Something to keep in mind while designing your database: "Adding fields is
expensive; adding records is cheap". In other words, your design should be
such that as new programs are offered, you simply need to add a new record to
a table. If you need to add a new field to the table, then you'll need to
modify existing queries, forms and reports. That's why it is considered
expensive.

Good luck, and feel free to post back with database design questions. Also,
there is a newsgroup dedicated exclusively to table design issues. I think it
is microsoft.public.access.tabledesign (?).

Tom
______________________________________

:

I’m trying to help a non-profit organization in my spare time (of which I
have none). I volunteered to set up a database having no idea it would be
this never-ending maze and that learning ACCESS is nothing like the other MS
Office programs. After numerous ACCESS tutorials, I have come to the
conclusion that although forms and queries all seem relatively understandable
once your tables are set up, I am not really sure how to set up my tables and
their relationships! The real problem is that you have to follow the logic
of why you’re doing it in the first place and I haven’t quite got that yet.

Basically the information I’m working with can be characterized as a contact
list. There are new queries that come in and older contacts. Some of these
contacts also turn out to be donors, although at this point I’m more
interested in sorting out who is interested in which type of program. Some
of the programs are educational and have prerequisites. Some are purely
social. We would also invite people to different types of programs based on
their expressed interests, location, gender, age, and so forth. We would
also track people so that we could know when they were last seen at a program
or what kinds of programs they have come to in the past. We would also keep
a list of their interests in case we come up with new programs. We would
also keep a list of people’s skills that we might access.

I came up with one really big table with about a kazillion fields. But for
the new people I felt I should have a second table although some of the
information is the same and later they might end up on the not so new
people’s table. I am in doubt about this. For example, with new people we
want to know where they heard about it – was it the website, a flyer, word of
mouth, etc. We also want to track our followup carefully if they express
interest in certain ongoing programs. If they express interest in YTT, we
want to know when they contacted us, how they contacted us (phone, email,
etc.), when we called them back, if they are signed up for the next program
and if not why. Also I was wondering if I shouldn’t split the big table
because you can’t follow the data when you get to the end of it. Or I guess
that wouldn’t matter if you’re actually inputting in forms and taking data
out with queries. I’m still thinking Excel I guess.

Any suggestions to get me going?