Trouble with keys and nulls!

[Susan]

I have two tables and I tried to create a relationship between them:
Table1 :
Join Date:
Last Name
First Name
membership info

Table 2:
Last Name
First Name
address info etc.

I tried to create a relationship by using Last Name and First Name but ran
into problems when a few businesses names were just used as the last name
and therefore the First Name field was nulls. What is the best way to
handle fixing this. I don't want to use last name only because of those
Jones, Smiths and Williams, etc.

Thanks for suggestions!
Susan

 

[Beetle]

If a Member can have more than one Membership, then
you should have tables like this, joined on MemberID

tblMembers
*********
MemberID (Primary Key)
LastName
FirstName
Address
other attributes of each member

tblMemberships
***********
MembershipID (PK)
MemberID(Foreign Key to tblMembers)
JoinDate
other attributes of each membership

Attributes on an entity (i.e. LastName and FirstName are attributes
of the entity "Members") should be stored in one place, and only one place,
in your application. You only store the key value in related tables.

 

[Susan]

There is not any Member ID--wish there was like companies use employee
numbers. We just use First Name and Last Name. We are small non-profit
organization helping the local public library.

 

[Susan]

I started out that way but there were a couple of people that sent in
information -- dues and donations more than once a year. That blew it if I
want to be proper with this structure stuff. My problem seems to be that
First Name and Last Name is the only way to identify the information
uniquely or I am not understanding things properly. If I created a Member
ID, that would have to be based on the Name and I would have my Null problem
again? I must be trying to make things too difficult!

 

[Pete D.]

For your purpose...create you own number. They don't even need to know it
exists.

 

[tina]

you are, hon. the purpose of the primary key is to identify the record
uniquely *to the system*. if you want to make sure you only enter each
first/last name combination once, that's a separate issue from using
meaningless value (Autonumber is probably easiest in your case) as the
table's primary key. to ensure each member is listed only once, just set a
unique index on the first/last name fields (together, *not* a unique index
on each field separately). if you want to understand the role of the primary
key in tables/relationships better, see the following for one good
perspective:
http://www.dbpd.com/vault/9805xtra.htm

hth

 

[Arvin Meyer [MVP]]

Yes there is. The primary key, while indexed, is normally a meaningless long
integer, use to connect your data. Every table should have a primary key and
dependent tables should have a foreign key. Access has an autonumber
datatype which is a special long integer that won't ever repeat. For
reference, use the Northwind sample database and look at the relationships.
Then look at the data in the tables.

An autonumber is automatically generated went you add a field with that type
to your table. Other than saving you need to do nothing except manually add
the matching foreign key to your related table(s) Using a form/subform will
generate a foreign key if you include that field in your subform.

 

[Susan]

Thank you very much for your reply!
I have First Name and Last Name and the First Name may contain nulls if the
Last Name just happens to be the name of a company. First Name and Last
Name are what uniquely identifies the data. So if I generate some sort of
Member Id, I still have to relate it to Name data to identify my data. I
created a Full Name field by concatenating the First Name and Last Name
(using EXCEL with the concatenation formula) and created my data sheet in
Access with the Full name. I thought I could use Full Name as the unique
field not containing nulls to tie my tables together. Now is there a way
within Access to set the Full Name field to contain the values of the First
Name field and Last Name field concatenated together so that the Full Name
does not have to be keyed into a record but is automatically generated when
adding new records?

 

[Beetle]

No one here is advocating that you need to institute some type of
membership ID number that you need to keep track of as part of your
normal business operations. What we are suggesting is that you add
a field to your table named MemberID (or whatever you want to
call it). You should make it an an Autonumber field as Arvin suggested.
This ID number would serve two simple purposes;

1) Allow Access to uniquely identify each record in the table.

2) Allow you a simple, effective way to create relationships.

You don't need to display the number on your forms. In fact, you don't
even need to know what the number is. It is "not for human consumption"
as they say.

Any tables that need to be related to your Members table would also have
a field named MemberID (it does not have to have the same name, but
it typically would). This field would be a Number data type (it cannot be
an Autonumber if it is a foreign key in a Child table) and it would store
the PK value from the Parent (Members) table. You would then create
the relationship on this field.

What you should definitely *not* do is attempt to use LastName, FirstName
or any combination thereof to create a Primary Key. One of the basic rules
of a PK is that it must be unique. There is no way to guarantee that a
person's name will be unique. Another thing you should not do is store
a concantenated first and last name in the table. Values that can be derived
from existing data should not be stored, only calculated in queries or
displayed on forms and reports.

You may be wondering, if you don't know the PK value how will you know
what to put in the foreign key field in related tables? The answer is that
if you are doing all of your data entry through forms/subforms (as you should
be, data entry should never be done directly in the tables), then the
Master/Child link property of the forms will handle this for you
automatically.

--
_________

Sean Bailey

 

[Susan]

Thank you very much. I am the membership chairman of the organization. I
am making this application to help me with keeping up with the tasks like
dues, mailing flyers for events, etc. I will pick up the mail and and look
at the paper form which has the information checked on it, record the
information and pass the checks to the treasurer. If someone has the same
Full Name, the only way I would know it by looking would be to view the
address information--maybe the phone number only if it matched. Therefore I
can only determine whether the Full Name is unique by looking at the address
information and I have to hope that the person did not move to a new
address. Otherwise I would have two records portraying two people when
there is only one person with an old address and the same person with a new
address. I do not understand with my limited information, how I could make
an accurate determination about duplicate Full Names. Viewing the Full Name
and address would give me the best clue as to whether a duplicate Full Name
is the same person or not. Do I generate some sort of PK based on that?

 

[Arvin Meyer [MVP]]

Susan, you cannot guarantee that full name will always be unique. How about
an apartment building with 2 John Smith's or a father and son with the same
first and last names, or Arvin Meyer and my wife Ms. Arvin Meyer.

To generate the PK, just add an autonumber field to your table and save it.
You can delete it if you want, or just use a copy of the database to test.
If you have 1 record for each member now, I suggest that you do it as soon
as possible before you do get duplicates.

 

[Beetle]

You have enough work to do, don't you? Why do you want to spend
your time looking through a table trying to find duplicate names
when Access can do it for you in the blink of an eye?

Access can retrieve, sort, calculate, categorize, group, display and report
on your data in almost any way you can imagine, but it's important to
remember two things;

1) NONE of this is done at the table level. It is done at the query, form,
report and code level. Tables are for storing raw data only.

2) Access can only do all this efficiently if your give it properly normalized
data.

Let's take your example of needing to check for duplicate names. As I said
before, all data entry should be done through forms. When you enter a
new name in a form, it would be relatively simple to have Access check to
see if that name already exists in the table before you commit the new
record. If it does, Access could display a message box showing the existing
name, address and phone number and asking you if you want to continue
adding the new customer or not.

Now, having said that, I understand that you may not yet be at the stage
where you understand how to do these things. As you progress through
the Access learning curve (which is somewhat steep), there are many
knowledgeable people in the newsgroups who are more than willing to
give you advice and instruction on how to do these things. Right now,
however, you are still at the table design stage, and the advice you are
getting is about how to properly structure your tables. So far, we don't
know much about your specific circumstances, so it's hard to give you much
other than general guidelines about what you should and shouldn't do.

If you want to provide more specifics about the nature of all the data
you need to keep track of, someone may be able to offer a more
specific example of an appropriate table strucure.

 

[Susan]

Yes, you are right! I have learned a lot. I do this to try to stimulate my
mind and also to provide a useful function as a volunteer. I appreciate all
of you who reply and provide valuable insight. You have provided some very
useful information that I will study.
Thanks again!
Susan