Help - Primary key questions

[Lisa - NH]

Hi. I'm in the process of setting up a membership database for our Legion,
Sons & Auxiliary. I bought the Access 2007 all in one reference for Dummies
as I had never working in Access or a true database program before.

The book says you don't have to have a primary key but they do recommend it.
I do not have the auto-number field or a primary key at this point. Some of
the fields I have are Member #, Last Name, First Name, etc.

Note: Originally had the Legion, Sons, Auxiliary & Courtesy copies (we send
out a monthly newsletter) in their own tables. There is a person here who
does a lot of work in Access 2003 (I have 2007 and it confused him). Had
looked at my setup (it has changed a lot since then). He suggested that all
the information go into one table. Then he said that because we had some
dual members (members of more than one group with different member #'s) that
I should put the member #'s into another table....(and maybe some other
information like birthdates (which are only used in the Auxiliary) and notes.

Should the primary key be the member # field? Should I just add the
auto-number field? Do I really need a primary key at all? (Currently there
are just under 1000 records.)
Lisa

 

[Jackie L]

I agree, you should have a member table (with member number as key) and then
another table which would have the member number and then group (Legion,
Sons, etc) as key. The member table would include all of the name and
address info for the member and the group table would carry the membership
organization. This would be a one to many relationship.

I would not recommend using the Auto Number for a field type or primary key.
It is too unreliable.

Also, I do not know if you were given the exact field names but avoid
special characters and spaces in any of your object naming (MemberNumber
instead of Member #, FirstName, LastName, etc).

 

[Lisa - NH]

Hi Jackie,

I'll address field names first. I wasn't told anything and at this point I
hadn't seen anything in the book (haven't done much looking through it yet)
that said not to use spaces. I did read something about that today. All the
field names are already set with spaces between them (Last Name, First Name,
Member #, Zip Code, etc). The table names have spaces between them (Post 3
Family is the main table, Deceased Members is another table that won't be
linked to anything else) as do the query names (Auxiliary Roster, Legion
Roster, Legion Honorary Life, etc). It doesn't seem to be causing any issues
but if it's something that should be changed, I can do that.

When I change the fields on the main table, will that update the queries or
do I have to re-do them all?

As far as the tables, now I'm a little confused. I orignally had all the
groups in their own table. Not only did the guy who offered to help say that
all that should be in one table but someone responded to my first post a few
days ago saying that there was no reason why everything couldn't be in one
table. The helper said that that's what the queries would allow you to view
the individual rosters along with the mailing for the newsletter, etc. But
then he said the member #'s should be in another table.

As far as using the auto-number field as the primary key. The book says to
use that when nothing else makes sense or works. I looked at our "helpers"
file and he did add the auto-number field and make it a primary key.

So you suggest a member table with the member number as the key. What other
info goes into this table? How do I get this information from the main table
into another table without screwing up the relationship of numbers to names?

A second table should have the member # & group as key? Don't know how to
do that? I currently have check boxes for the groups (Auxiliary, Legion, SAL
& Courtesy) which is working fine as far as running the query.

Sorry for being so confused. Is this 3 tables you are talking about or 2?

I want this setup to be througough but also be simple to use. I'm going to
be very detailed and tell you exactly what my fiels are and what I want.

Main table from left to right: Paid, Member #, Last Name, First Name, M,
Suf, Address, City, State, Zip Code, Phone, E-mail Address, Honorary (check
box), Paid Life (check box), 50+ (check box), 70+/20+ (check box), JR (check
box), Mail (check box), Birthdate, Note, Aux (check box), Leg (check box),
SAL (check box) and Ctsy (check box).

Yes I know that's a lot of check boxes. We needed a way to keep track of
certain types of members and the last 4 check boxes were setup so that all
groups could be in the one table.

I want to be able to have fast access (via the query) for eatch roster, for
the newsletter mailing list, the 50+ year members, the Honorary life members,
the Paid life members, and the 70+/20+ members for the Auxiliary (70 & older
with 20+ years).

I'm just wondering why it's better to split this up into more than one table
and exactly what would go in each table and how I'd relate them to each
other. Then I assume all my queries would have to be re-done and I'm not
exactly sure what's involved in using more than one table for a query.

If you are still with me.....bless your heart and thank you.
Lisa

 

[John Spencer]

Yes, you should have a primary key. Member # may be a good candidate to be
the primary key for the member table as long as
1) It is unique - no other record gets the same Member#
2) It is stable - it does not change (for instance the member gets a new
number next year or the organization decides to use a new format for the
member number
3) It is always available. There are no members without a member #.

From you comments, I see that a member may have more than one member #, so
it looks as if Member # is not a good candidate to be the primary key.

The simplest method to get a unique primary key is to use an autonumber
field and designate that as the primary key.

It is rarely true that a database has only one table. Tables should store
information unique to one thing. For instance, since a member could have
more than one member number, you might want a table of membership numbers
that is linked to the table Members.

Are Auxiliaries members or are they associated with a member? Both members
and auxiliaries are people, so you might want a table for persons with a
field that tells you whether the person is an auxiliary or a member or you
can have two tables Members and Auxilaries. The two table approach is more
valid if you store different informaton about the two classes of people.
For instance, Members might have contact information - address, phone,
email - gender, dob, date of service, and other information while you may
only store the First name, Last Name, relationship, gender, and dob for
Auxiliaries.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lisa - NH]

John,

The member # in each group is definately unique. I'm not sure if the
Legion, SAL & Auxiliary use the same group of numbers but since this is
nationwide I would find it hard to imagine that we'd have a Legion member &
an Auxiliary member or SAL member have the same number.

The member # doesn't change unless someone hasn't paid their dues for many
years and gets pulled from the roster...then they come back and for one
reason or another their old member # is taken by someone else. But that's
not a problem as once they have been pulled from the roster on the national
level, we remove them.

The 3rd item is the problem one. I didn't even think about it. We send out
courtesy copies to TV stations, the Dept Headquarters and some others that
aren't members. So they have no member #. Also when we add a new member,
they don't get a member # until national issues them one and we don't get
that information until we get a suplemental roster which we get every 3
months.

Yes a member can be a dual member of either the Legion & Auxiliary or the
Legion & SAL. They would have 2 different member #'s in that case and of
course be listed twice in the table.

So it looks like the only option is to add that autonumber field. That's
what our "helper" did on his test file.

In another post, Jackie mentioned the same thing about having a 2nd table
for the member #'s.

Auxiliary members are not necessarily related to a Legion member. Auxiliary
members can join under various relations. If the person they are joining
under is alive, then that person must be a Legion member before they can join
under them. If the person they want to join under is deceased, they just
have to show proof of that person's military service. The roster shows no
relationship to who they joined under. Also the Auxiliary (American Legion
Auxiliary) can stand on it's own without the Legion. The SAL (Sons of the
American Legion), is however a program of the American Legion....but they
have the same joining requirements the Auxiliary does as far as alive &
deceased and again there's nothing that's associated with them in the roster
to a Legion member.

So it is 3 distinctive groups (American Legion, American Legion Auxiliary &
Sons of the American Legion). (Also we have the courtesy copy information
included in the same table.)

The only reason why I put these all into one talbe in the first place
(originally it was one table for Auxiliary, one for Legion, one for SAL and
one for courtesy copies) was because our "helper" said they should be all
together.

There is information that is currently only used in the Auxiliary and that's
the birthdate field. the 70+/20+ field and the JR field. Our helper said to
put all the same fields in all the tables whether used or not and then put
everything into one table. But then later he did suggest putting member #'s
into a seperate table.

We don't keep track of gender as that would only apply to the legion. Only
women can join the Auxiliary and only men the SAL...but both can join the
Legion if they served.

If you are still with me, I say the same thing I said in another
post....bless you and thank you.
Lisa

 

[Tom Wickerath]

Hi Jackie,

I would not recommend using the Auto Number for a field type or primary key.
It is too unreliable.

Say what? Who told you this information? It is simply not true, as long as
one has installed the latest service packs for the JET database engine. The
current SP level is SP-8. There was an earlier problem many years ago with an
earlier SP level--I think it was SP-3 or perhaps SP-4.

The Autonumber data type is actually quite reliable.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Lisa,

There is a person here who does a lot of work in Access 2003 (I have 2007
and it confused him).

I can certainly identify with this person. Access 2007 confuses me a lot too!

He suggested that all the information go into one table.

No--no--no! Your friend needs to spend some time reading up on database
normalization. Tables include fields (attributes) that should describe a
single subject, much like a written paragraph in English should cover a
single subject. A common mistake of beginners is to import a flattened
structure from Excel. At that point, they essentially have an "Access
spreadsheet". Poor database design can make it a lot more difficult in the
long run.

Do I really need a primary key at all?

All tables should have a primary key or uniquely indexed field. This allows
you to identify a unique records amongst hundreds or thousands or records. A
primary key is uniquely indexed, by definition. A primary key must be unique,
and cannot be null (unknown). However, a uniquely indexed field is not a
primary key until one actully sets it as the primary key. As you will start
to learn the more you explore, Access allows users to create designs that are
not optimum.

I'll address field names first. I wasn't told anything and at this point I
hadn't seen anything in the book (haven't done much looking through it yet)
that said not to use spaces. I did read something about that today.

Access allows one to use spaces and special characters in field names, but
it is really not adviseable. It will cause you extra work in the long run, as
often times you will need to remember to add square brackets around a name
that includes special characters and/or reserved words. At other times, the
use of such names can produce errors that are difficult for beginners to
track down. As an example, a hyphen (-) in a field name can later be treated
as subtraction by Access. A pound (#) sign is a special character reserved
for creating hyperlinks in Access. Many of the Microsoft samples available
for download include spaces and special characters in the field names, names
of objects (tables, queries, forms, reports, etc.) and controls on forms and
reports. That doesn't mean that it's a practice that you should adopt. In
fact, it flies in the face of their own advice in this KB (Knowledge Base)
article:

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

Family is the main table, Deceased Members is another table...

Why? Why not just have a field named something like DateOfDeath in the
Members table? Leave the field null for those members who are still pink and
breathing.

When I change the fields on the main table, will that update the queries or
do I have to re-do them all?

This can be a hit and miss proposition. As long as you have Name Autocorrect
enabled, and the Name Maps are updated, then this feature will generally do a
good job of updating changes in the field names. However, there are many good
reasons to avoid using this feature, which I like to call "Name Autocorrupt".
That name should give you a good clue as to why this "feature" may not be so
desireable.

Access 2003 will automatically update name maps when one enables Name
Autocorrect. Prior versions will not. In Access 2002 and earlier, you would
have to open each object in design view and then save it in order to update
the name maps. If ULS (User Level Security) was applied, you would need to
have the correct permissions to save these design changes. Name Autocorrect
will not update any references to fieldnames, table or query names, etc. in
VBA code. There are tools available to assist users with renaming tasks:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora: http://www3.bc.sympatico.ca/starthere/findandreplace/ (Free)
V-Tools: http://www.skrol29.com/us/vtools.php (Free)

As far as using the auto-number field as the primary key. The book says to
use that when nothing else makes sense or works.

The decision to use an Autonumber key versus a candidate key is one that
each developer needs to make for themselves. There have been great debates in
the various newsgroups, with staunch advocates on both sides of the fence.
These debates sometimes degenerate into arguments as ferocious as religous
wars. With that said, I prefer the ease of use of Autonumbers myself.

Member Numbers
I think you've already established that Member number is not a suitable
primary key, so I won't comment further on that.

They would have 2 different member #'s in that case and of
course be listed twice in the table.

They may have two different member numbers, but that doesn't mean that the
person's name should be listed twice in the database. Instead, it suggests
that you should have a related child table for memberships, where you can
establish a one-to-many (1:M) relationship betweens Members and Memberships.
When you start repeating data, you multiply your chances of making mistakes.
Also, think about a mailing list report later on. If you duplicate members
names, are you going to want to send out duplicate mailings?

The only reason why I put these all into one talbe in the first place
(originally it was one table for Auxiliary, one for Legion, one for SAL and
one for courtesy copies) was because our "helper" said they should be all
together.

Again, your helper needs to spend some time studying database design
(normalization).

I have a Word document I call Access Links.doc, that I think you will find
quite helpful. You can download a zipped copy from here:

http://www.accessmvp.com/TWickerath/

For the present time, concentrate on just the first couple of pages. This
includes information on special characters to avoid, reserved words, database
design, best practices (make sure that you have the latest updates), Name
Autocorrect, Relationships, along with a lot of useful links to other web
sites. Also, head on over to Access MVP Crystal's new site and download her
Access Basics tutorials:

http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lisa - NH]

Hi Tom,

First let me say...I'm not a happy camper. I just spent the past half hour
going through and responding to your post with very detailed info. I went to
post and it popped up saying that it was busy or something. I went back to
the box I was typing in and everything I had just did was gone! So I'll try
to do this again as best I can.

I did some research yesterday. A lot of it I just didn't understand. I did
find a web site that had a lot of information that led me to a online book by
Cyrstal Long. I actually understand some of what I read and it's starting to
make more sense. I have saved those 2 web sites as favorites and have
printed two chapters from her book. One on Database objects (which had some
good info in it) and one on Normalization. Will be reading this more later
today.

I definately see that a primary key is needed. As mentioned, member # field
will not work for us. So it will be the auto-number field.

Yes I can see that Access does allow you to do things that I've been reading
that you shouldn't do. Don't know why they do that.

I found a program called Access Database Issue Checker. I ran it and it did
say that some of my names where bad or had bad characters. Also it showed
that a lot of my fields allow zero length. I hope that's not a real problem
because not everyong has a middle name, suffix, e-mail address, phone number,
etc.

As far as the deceased list....Our original setup is in Excel. My husband &
I decided a few years back to start keeping track of those who passed. We
created a seperate sheet for them so as to not to get confused. I had a huge
pile of old Auxiliary stuff and was able to find a lot of names of members
who had passed years ago. I however couldn't find some of their member #'s
or dates of death. My husband was able to get some of the old names, again
not all information is there. Some of the names we have we aren't sure if
they were Legion members or SAL members. We don't keep the address for them
either. In Excel it was easier because if we left them in the main Legion
Roster, our count of current members would have been off. If they were
included into the main table & marked as deceased, how would I keep them from
showing up in the queries for the current membership. Also since we aren't
keeping all the information for them, isn't it better to keep them seperate?

As far as the newsletter (I know I did post this info before, but not sure
if it was under this question or another one)....Not everyone gets the
newsletter. There are a few that don't want it at all. There are quite a
few who receive it via e-mail. Out of the remaining members, we only send
one per household. I created a check box for those that get the actual paper
copy. I do have everything set up in the old Excel list but we had stopped
updated it once we had all the data into the Access file as I didn't want to
update two seperate files. I exported everything out into a new Excel file.
Did a few different sorts, going through the list each time to be sure that
everything was right. Then I sorted again by the mail column and then the
zipcode column. I copied and pasted info out into two seperate files to
create the lables from for the last newsletter. The reason for two is
because the post office requires us to sort the mailing into two boxes. One
box is for all the 030's and the other for everything else. The people who
do the labeling say it's easier if they having them printed this way. Not
sure if this is something possible in Access. As I only tested getting the
basic mailing list into a query sorted by zip code.

I downloaded the Access Links.doc. I'll look at it later.

So my thinking now.....is definately do a primary key using auto-number.
I'm looking at setting up a "group table" for Auxiliary, Legion, SAL &
Courtesy. If I read correct, I put two fields in this table...one for the
group and a ID field? Is that a auto-number field that's only for that
table? Then I'm thinking another table would include the types of membership
(honorary life, paid life, 50+, 70+20+, JR). Again I assume two
fields....one for type and a number field. Another suggestion was that the
member #'s be in a seperate talbe in the group table. Which is best and how
do I set it up?

The other fields....like the date of birth field, notes, mailing and
paid.....should those be in a seperate table?

Any other suggestions are appreciated. Thanks.
Lisa

 

[Tom Wickerath]

Hi Lisa,

First let me say...I'm not a happy camper. I just spent the past half hour
going through and responding to your post with very detailed info. I went to
post and it popped up saying that it was busy or something. I went back to
the box I was typing in and everything I had just did was gone! So I'll try
to do this again as best I can.

This is a bug of the web interface. I've been bitten by the same bug on many
occasions. Usually I'm pretty mild mannered, but you don't want to hear my
words of disapproval after having lost an extensive reply. Since I choose to
continue using the web interface, I have since learned to *always* select all
of the text first (use <Ctrl><A>) and then copy it into the clipboard (use
<Ctrl><C>) BEFORE clicking on that Post button. At least once, and sometimes
twice per day, a post attempt will blow up with that server error message. At
this point, I can right-click to get the shortcut menu, click on Back, and
end up with a lost message. Press <Ctrl><A> to select the original unedited
reply in it's entirety. Then press <Ctrl><V> to overwrite the selected text
with your complete reply that you still have saved in the clipboard. This
trick has saved me countless times. It's really too bad that the team
responsible for creating this web interface hasn't had the foresight to
consider "gotchas" like this that real people routinely experience.

You are certainly not limited to using the web interface. Outlook Express
includes a newsreader, and there are many free newsreader applications
available that one can download and use for free. Another alternative is to
use Word to compose a reply. Then copy it when finished composing, and paste
it into the reply box. I still continue to use the web interface directly
(without using Word or some other temporary document, even NotePad). I joke
to other Access MVPs (most of whom abhor the web interface) that I apparently

I did some research yesterday. A lot of it I just didn't understand. I did
find a web site that had a lot of information that led me to a online book by
Cyrstal Long.

If this is the online book that I'm thinking of (approx. 30-some pages), her
new Access Basics is the updated version of the online book. As for how much
you understand, this is quite natural for everyone. However, as you work with
the product more, and then come back at a later time and re-read some
information you may not have understood the first time, more of it will seem
to make sense to you.

Yes I can see that Access does allow you to do things that I've been reading
that you shouldn't do. Don't know why they do that.

The Access Team at Microsoft is clearly trying to lower the barriers for
folks to start using Access. Too many people might be put off at their first
experience if they had to worry about special characters, reserved words, etc.

I found a program called Access Database Issue Checker. I ran it and it did
say that some of my names where bad or had bad characters. Also it showed
that a lot of my fields allow zero length. I hope that's not a real problem
because not everyong has a middle name, suffix, e-mail address, phone number,
etc.

This is an excellent tool that Access MVP Allen Browne makes available. In
fact, he just released a new update at the end of last month. You should
definately bookmark Allen's Index of Tips page. Spend time every now and then
perusing through his site to see what's available. On the topic of ZLS (zero
length strings), see Allen's write-up here:

Problem Properties
http://allenbrowne.com/bug-09.html


Deceased Members

If they were included into the main table & marked as deceased, how would
I keep them from showing up in the queries for the current membership.

Simple. Use a date/time data time for Deceased. If the field is null, it is
assumed that the person is alive. If there is a date entered, this is the
date that these members met Peter at the Holy Gate. Use a query, something
like this, to show current members:

SELECT FirstName, LastName, Address, City, StateOrRegion, PostalCode
FROM Members
WHERE DateDeceased IS NULL;

or
WHERE DateDeceased IS NOT NULL;

to get a listing of deceased members.

Also since we aren't keeping all the information for them, isn't it better to
keep them seperate?

Keeping them separate imposes an additional burden upon the DBA (you) to
*move* a record from one table to another, as current members "expire"
permanently, as we all will one day.

I however couldn't find some of their member #'s or dates of death.

Not having the dates of death could be a problem with the above strategy
that I just outlined. I suppose you could use a Yes/No data type, or a Number
/ Byte to indicate dead or alive, but I would not use both a date and a
checkbox. That imposes too much burden on a DBA to make sure to enter both a
date and check the checkbox when a person dies. And if only the check box is
checked, are you really sure this person is dead, or did someone entering
data accidently check this option? Let me ask you this question: How much
real value is there in entering data for people who are long since gone?

So my thinking now.....is definately do a primary key using auto-number.
I'm looking at setting up a "group table" for Auxiliary, Legion, SAL &
Courtesy.

I need to stop at this point, since it is 1:20 AM for me right now. Do read
the two Michael Hernandez docs on database design. The second paper at Jeff's
site is a very easy read--it is only (4) pages. The first paper is quite a
bit more involved.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Lisa - NH]

Hi Tom,

This is a bug of the web interface. I've been bitten by the same bug on many
"clipped"

I'm glad to hear that it wasn't just me. I have Vista on my computer but I
haven't used Windows Mail. I use Outlook. The main reason why I started
using it a few years back was because it was included with Office and I
figured I spent good money for it, I was going to use it. I do have Xnews
but don't use it often. I'll have to go into it and see if I can find this
group to subscribe that way. It would be easier. I had never thought about
it.

If this is the online book that I'm thinking of (approx. 30-some pages), her
"clipped"

It is her Access Basics that was updated in February.

The Access Team at Microsoft is clearly trying to lower the barriers for
folks to start using Access. Too many people might be put off at their first
experience if they had to worry about special characters, reserved words, etc.

After spending the money for the program I am committed to it. I would have
rather known about these issues right from the begining.

This is an excellent tool that Access MVP Allen Browne makes available. In
Problem Properties
http://allenbrowne.com/bug-09.html

I have bookmarked his site and have been looking through the info. I just
looked at that page and I don't fully understand it. What exactly does that
code do and where would it be placed?

Simple. Use a date/time data time for Deceased. If the field is null, it is
assumed that the person is alive. If there is a date entered, this is the

There's a problem with this. A lot of the members in my deceased list, do
NOT have a date of death....or even a year because we just couldn't find that
information.

data accidently check this option? Let me ask you this question: How much
real value is there in entering data for people who are long since gone?

Good question. It was originally my husbands idea to go back farther to
find out what we could. A few others he talked to agreed it was a nice idea.
To kind of make a history. This requires more thinking & talking with my
husband & others. We will keep the information we have in an Excel file for
now and decide what we want to do about future deceased memebers.

I need to stop at this point, since it is 1:20 AM for me right now. Do read
the two Michael Hernandez docs on database design. The second paper at Jeff's
site is a very easy read--it is only (4) pages. The first paper is quite a
bit more involved.

I will do that.
Lisa

 

[Tom Wickerath]

The code in Allen's bug-09.html page is used to "automajically" change the
Allow Zero Length property for all text and memo fields from the default of
Yes, to No. This saves the developer the chore of doing this task manually.

As a beginner to Access, my advice to you is to not focus your efforts at
attempting to understand the code. Just run it. The easiest way to do this is
to create a new stand-alone module. Copy the code and paste it into the
module. With your mouse cursor blinking anywhere within the module, press the
F5 key to run it. You might want to insert a message box statement, so that
you'll get some feedback that it has run:

Set tdf = Nothing
Set db = Nothing
End Function

Insert one line here:

Set tdf = Nothing
Set db = Nothing
MsgBox "All Done!" '<----
End Function

Before you run this code, open several tables in design view. Look for any
text and memo fields. As you find each one, click on it to select it. Jot a
quick note indicating those fields that show Allow Zero Length: Yes.

Note:
If you cannot find any, for whatever reason, then create some new text
fields in a table. You should see how the Allow ZLS setting defaults to Yes.

Close all tables. Now run the procedure as I described above. Check the same
tables once more. This time, you should see that the text and memo fields all
have an Allow Zero Length setting set to No.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,


I have bookmarked his site and have been looking through the info. I just
looked at that page and I don't fully understand it. What exactly does that
code do and where would it be placed?

<snip>

 

[Lisa - NH]

Hi Tom,

In the current setup (which I will be restarting)...almost all of the fields
are set to allow zero length. I will keep this info in mind for after I
finish re-doing things. I am just wondering about something though......If
the field is changed to not allow a zero length....what do I do for those
fields that are empty for some records? For instance, right now the date of
birth field is only used in the Auxiliary. So all the other members don't
have anything there. There are a lot of members we don't have phone number
or e-mail address for, etc.
Lisa

 

[John W. Vinson]

Hi Tom,

In the current setup (which I will be restarting)...almost all of the fields
are set to allow zero length. I will keep this info in mind for after I
finish re-doing things. I am just wondering about something though......If
the field is changed to not allow a zero length....what do I do for those
fields that are empty for some records? For instance, right now the date of
birth field is only used in the Auxiliary. So all the other members don't
have anything there. There are a lot of members we don't have phone number
or e-mail address for, etc.
Lisa

Just leave those fields Null. If the field's Required property isn't set (it
won't be by default) you don't need to do anything.

NULL is a somewhat confusing concept! It is emphatically NOT the same as a
"Zero Length String". For one thing, a zero length string *only* applies to
Text fields; a Date, Number, Currency, Yes/No field cannot be set to allow
zero length string *because it's not a string!!*

A NULL value in a field means "this value is unknown, undefined, unspecified".
This can lead to some complexity in queries, since NULL is not equal to
*anything* - it's not even UNequal to anything! The only criterion which will
retrieve a null value is

IS NULL

Using =Null or <> Null or the like will simply not retrieve anything; the
result of any such expression is neither true nor false, it's NULL. So nulls
can be a pain but used correctly they are reasonable. If a birthdate isn't
known to you then Null is perfectly appropriate: "this value is unknown" after
all!

 

[Lisa - NH]

Hi John,

Thanks for the info. I will keep it in mind. I've been playing around in
here since first thing this morning and I haven't had anything to eat. It's
1:30pm, I think it's time to get out of here for a while, eat something, let
the brain recharge and start again later.
Lisa