Advice Needed on Designing a database

[moving the database]

Hello,

I'm trying to create a database for my church to store all the information
of each member: Lname, Fname, Age, Gender, Phone, Address, Email, ...
My objective for this is to be able to keep a record of each member and
their pledge amount. Also, avoid any members being entered twice (I cannot
use Social Security numbers). What would be your suggestion? Is there an
existing template or templates database that I can use? Thank you in advance.

John

 

[tina]

well, if the data is really that basic... for instance, do you need to store
just the amount pledged to give - and *not* the amount actually given each
week, month, year, whatever? do you need only a mailing address for each
member - or a physical address too? what if more than one member lives at
the same address, is it okay to list each separately with the address
essentially duplicated - or are you planning to use the database to generate
a mailing list, and don't want multiple mailings going to the same address?
and phone numbers / email addresses, are you sure you want to be able to
store only one of each, for each member - what about home phones, work
phones, cell phones, home email, work email?

as you can see, even seemingly simple data requires considerable thought
before creating a database. the only concrete suggestion i can give you,
based on the information in your post, is this: don't store a member's
age - unless you want to be constantly updating it! age is by definition a
calculated value, based on the time passed from date of birth to the present
(or any chosen date); as such it should not be stored as hard data in a
table. store each member's date of birth instead; you can calculate current
age whenever you need it, in a query, form, or report.

my only other suggestion is that you don't try to make the database itself
prevent "duplicate" member entries. instead suggest you use code on a data
entry form to check the first and last names and DOB of each new record
against existing records - and *warn* the user when it finds a match. then
leave it up to the user to decide if the new record should be added or not.
that will give you some flexibility just in case you actually have two John
Smiths born on the same date. if you have a very large membership, you can
add a middle-initial field, and include that in the data entry check, which
will provide even greater distinction between persons.

hth

 

[John]

Hi Tina,

You mentioned that there is a way to write a code to "Warn" the user who is
using the form when entering the data if there is any identical records
already exists. How can I do that? Can you please explain it in detail so I
can use this feature? Thanks.

John

 

[tina]

there are several ways you could set this up. since the user may in fact
decide to add a "duplicate" record, having decided that it is not actually a
duplicate, i'd favor opening a second form, read-only, which shows the
suspect record(s). here's how i set up my test:

a data entry form, i called it Form10, with the following code on the form's
BeforeUpdate event, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error Resume Next

Me.Tag = 0
DoCmd.OpenForm "Form10A", , , _
"aField= '" & Me!Text0 & "'", acFormReadOnly, acDialog
If Me.Tag Then
Cancel = True
Me.Undo
End If

End Sub

the WHERE clause controls what records are included in the "review" form (i
called it Form10A), so you set up the WHERE clause to pull records that
match the fields which identify a possible duplicate. make the form
read-only so records can't be changed in it, and finally set the mode as
Dialog, so the calling code pauses while the review form is open.

in the review form, add two command buttons; i called them cmdAdd and
cmdCancel. and add some kind of text next to the buttons to guide the user,
such as "Do you want to save the new/edited record, or cancel the changes?".
then add code to the command buttons, as

Private Sub cmdAdd_Click()

Forms("Form10").Tag = 0
DoCmd.Close , , acSaveNo
DoCmd.SelectObject acForm, "Form10"

End Sub

Private Sub cmdCancel_Click()

Forms("Form10").Tag = -1
DoCmd.Close , , acSaveNo
DoCmd.SelectObject acForm, "Form10"

End Sub

the command button sets the value of the Tag property in the first form
(Form10). and when the second form closes, the code in the first form
resumes executing, reads the value of Tag, and cancels the record update
where appropriate. note that this code runs when a new record is added AND
when an existing record is edited - which is perhaps a good way of
identifying duplicates that escaped the net first time around due to
spelling typos, which are now being corrected.

that said, you have to give some thought to how tightly you want the
comparison for duplicate records to be. one consideration is the condition
of the raw data as the user will see it: how likely is it that there will
be different spellings of names, for instance, or J Smith and James Smith
and Jim Smith. how likely is it the user will make typos, entering Jom
instead of Jim, for example. depending on the size of your data table, you
may want a broader comparison to return more possible matches for the user
to review. remember the use of wildcards, they work the same in the WHERE
clause as they do in a query's criteria.

none of this will be perfect, because of the many possible parameters you
may use to check for matching records, and because of the "user error"
factor. but i think having a *person* make the decision is still, in many
cases, a better option than being dependent on the rigid parameters of a
unique index in the data table itself.

and btw, John, i'd like to comment on your statement that you "cannot use
Social Security numbers", for others who may read this thread. that's good;
in my opinion, especially sensitive personal data that could be used, among
other things, for identity theft, doesn't belong in an Access database. the
security features are too-easily circumvented to make it a secure data
storage file. i've personally never used any database software besides
Access; but from my reading in these newsgroups and elsewhere, i've gathered
that a server-based solution, such as SQL Server or Oracle, is a better
option for that sort of data.

hth

 

[John]

Hi Tina,

Is there any way I can get a little bit more help on this. I like the
BeforeUpdate event precedure, but I don't know how to start this. All I want
to do is to check the following fields: Fname, Lname, Address, Phone, and to
warn me with a message if I enter a record record for the second time that
matchs all the above fields. I'm not an expert in Access, but I'm a hard
worker and I firmly beleive that I can do this.

Therefore, lets assume that my table is called "Members", and the above
fields are in that "Members" field. Where do I start from here? Thank you.

John

 

[tina]

i firmly believe you can do it, too, so let's get started.

let's back up a bit and start with the members table. you list your fields
as

Fname
Lname
Address
Phone

okay, except for "Address". any address has several components, which each
belong in their own field - following normalization rules which call for
"atomic" data in each field. recommend you substitute the following for
Address:

Street1
Street2
City
State (include this field even if all your members are currently in the same
state)
Zip
Country (include this field only if appropriate to your needs)

btw, make sure that the PhoneNumber field is a Text data type, not a Number
data type. also recommend you have a tblStates listing each state
abbreviation and full name, as

tblStates
StateCode (AK, AL, AZ, etc, one code per record.) (primary key)
StateName

and further recommend a tblCities, as

tblCities
CityID (autonumber, primary key)
CityName

having city and state tables will cut down on data entry time, and help
prevent typos in those two fields in tblMembers, at least - and every little
bit helps.

now, you need a form for data entry. create a form, and set its'
RecordSource property to tblMembers. add controls for the table fields you
want to see in the form; use a combobox control for the City field, and set
its' RowSource to tblCities; ditto that for the State field, tblStates. if
most or all of your members are currently in the same state, you can set the
DefaultValue of the state combobox to that state code, to save data entry
keystrokes. ditto that for the city combobox.

name the form, frmMembers, and save and close it. now copy it (Ctrl+c, then
Ctrl+v). name the copy frmMembersReview. open frmMembersReview in Design
view, and add the command buttons i described in my previous post, add the
code i posted, and replace the name "Form10" in that code with the name
"frmMembers". save and close.

open frmMembers in Design view, in the form's BeforeUpdate event, add the
code i posted previously, replacing the DoCmd.OpenForms lines with the
following, as

DoCmd.OpenForm "Form10A", , , _
"Fname = '" & Me!Fname & "' And " _
"Lname = '" & Me!Lname & "' And " _
"Address1 = '" & Me!Address1 & "' And " _
"Address2 = '" & Me!Address2 & "' And " _
"City = " & Me!City & " And " _
"State = '" & Me!State & "' And " _
"Phone = '" & Me!Phone & "'" _
, acFormReadOnly, acDialog

read up on the OpenForm action in VBA Help, so you understand how the
arguments work. the above WHERE clause looks for exact matches only in all
those fields. if you want more "fuzzy" matching, employ the use of wildcards
and the Like operator in place of the "=" sign. read up on wild cards in
Access Help so you'll understand how to use them.

hth

 

[John]

Hi Tina,

I have a few questions but I'm not sure if you can help me over the phone.
Can I give you my phone number?
Some of my questions are:

1. For the tbleState = I created a StateCode field (AK, AL, AZ, ...) , and
StateName field(Arkansas, Alaska, Arizona, ...) I assume that I want to make
the StateName primary key? Also, when I add this field on my form as a
combobox, how do I make sure that the default value is null or blank when I'm
entering a new record? currently, it pick up the last state I selected for
the previous record.

There are a couple of other questions I have but it would take a long time
for me to type them. Can I email you my phone, or can you email your phone so
we can go over these questions? I really appreciate it. My email is herat1 at
hotmail dot com. Thanks.

John

 

[tina]

i've been out of town, John, just now reading your last post to this thread.
see my comments below.

Hi Tina,

I have a few questions but I'm not sure if you can help me over the phone.
Can I give you my phone number?

no, let's keep it in the newsgroups, where the discussion may benefit
others, and/or the discussion may benefit from others' input.

Some of my questions are:

1. For the tbleState = I created a StateCode field (AK, AL, AZ, ...) , and
StateName field(Arkansas, Alaska, Arizona, ...) I assume that I want to make
the StateName primary key?

i'd make the StateCode field the primary key, as i said in my previous post
when describing the table.

Also, when I add this field on my form as a
combobox, how do I make sure that the default value is null or blank when I'm
entering a new record?

1) make sure the combobox's DefaultValue property is blank, and 2) make sure
the combobox's ControlSource property is set to the name of the "state"
field in the form's underlying RecordSource.

hth