Handling addresses

[Mariah]

I am reformatting a database that is relatively small (about 500 members) and
am seeking guidance regarding handling member addresses.

I inherited the database that was nothing more than a spreadsheet but this
couldn't handle the type of reports I was being asked to generate. I was
asked to learn how to make the database relational and make it more usable.
So far the trip has been fun and enlightening.

At this point I now have:
1. The old "spreadsheet" (I never delete until something is running 100%)
2. Address tables including a MailAddress table with mailing addresses each
having a key, a HomeAddress table with keys, and a CustomerNames table with
keys.
3. I have junction tables to match the customer names with the addresses,
for instance tblJunctionMailAddress, tblJunctionHomeAddress. Each customer
name appears once and the address usually appears multiple times.

The advantage of this new setup is that I can generate order forms or
letters that list names based on the address (had some guidance from a user
here which started this change in the database) which is handy because many
of our members are roomates and they hop around independantly of each other.
However, I am having trouble easily updating addresses, specifically I can't
get seem to figure out how to get forms to work with this new setup. I
personally don't need the forms to update the addresses, but it would be nice
for whoever inherites the database from me.

I am wondering if I am making this more complicated than it needs to be, or
if I should keep heading in this direction. The last table I would generate
would be "work address" and then a junction table to combine the members with
their work addresses.

So I guess I am seeking ideas on a couple issues:
1. Should I keep heading in the direction of the addresses having their own
keys in separate tables with junction tables to combine them together with
member names. (Would this be considered the database standard?)
2. Is there a way to have a form update the address and junction tables for
me, or no? If this is possible, could you point me in the right direction?
3. Any other words of wisdom from experienced database creators and users.

Any advice is very much appreciated!

 

[John W. Vinson]

I am reformatting a database that is relatively small (about 500 members) and
am seeking guidance regarding handling member addresses.

I inherited the database that was nothing more than a spreadsheet but this
couldn't handle the type of reports I was being asked to generate. I was
asked to learn how to make the database relational and make it more usable.
So far the trip has been fun and enlightening.

At this point I now have:
1. The old "spreadsheet" (I never delete until something is running 100%)
2. Address tables including a MailAddress table with mailing addresses each
having a key, a HomeAddress table with keys, and a CustomerNames table with
keys.
3. I have junction tables to match the customer names with the addresses,
for instance tblJunctionMailAddress, tblJunctionHomeAddress. Each customer
name appears once and the address usually appears multiple times.

The advantage of this new setup is that I can generate order forms or
letters that list names based on the address (had some guidance from a user
here which started this change in the database) which is handy because many
of our members are roomates and they hop around independantly of each other.
However, I am having trouble easily updating addresses, specifically I can't
get seem to figure out how to get forms to work with this new setup. I
personally don't need the forms to update the addresses, but it would be nice
for whoever inherites the database from me.

I am wondering if I am making this more complicated than it needs to be, or
if I should keep heading in this direction. The last table I would generate
would be "work address" and then a junction table to combine the members with
their work addresses.

So I guess I am seeking ideas on a couple issues:
1. Should I keep heading in the direction of the addresses having their own
keys in separate tables with junction tables to combine them together with
member names. (Would this be considered the database standard?)
2. Is there a way to have a form update the address and junction tables for
me, or no? If this is possible, could you point me in the right direction?
3. Any other words of wisdom from experienced database creators and users.

Any advice is very much appreciated!

I'd actually suggest a couple of changes. Separate tables for home and work
addresses is probably neither necessary nor wise - it will make your queries
much more complex! Instead, have an AddressType field in a single Addresses
table, with values Home and Work (and perhaps Vacation as well).

Forms aren't just for naive users. Expert developers use them too, just
because they make life SO much easier! If you have a Form based on
CustomerNames with a Subform based on tblJunctionAddress - or a query joining
tblJunctionAddress to the (single) Address table - you will be able to see at
a glance all of the addresses for a member, update or recategorize them,
reassign an address to a member, etc.

John W. Vinson [MVP]

 

[Mariah]

That's very helpful information. What is your advice regarding phone numbers
and email addresses, as a members mail address may be the same but not the
phone number. Is a separate table of phone numbers better alonig with a
field that identifies the number as work, home or cell? Same true for email
addresses?

Thanks again for your assistance. I can tell you my co-worker is already
grateful that there will be only one address table...

 

[Dale Fye]

Yes,

I generally include tables for member_email and member_phone in my contact
databases, where each member may have multiple phone or email addresses. I
generally include a PhoneType or EmailType field as well as a SortOrder
field. The SortOrder field lets me prioritize these values (for display
purposes), as well as letting me select all the email addresses where
SortOrder = 1 to get the email addresses for a mass mailout. Occassionally,
I have a member who wants to get email at both his home and work email
addresses, so I can put 1 in the SortOrder field for both of these records.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[John W. Vinson]

That's very helpful information. What is your advice regarding phone numbers
and email addresses, as a members mail address may be the same but not the
phone number. Is a separate table of phone numbers better alonig with a
field that identifies the number as work, home or cell? Same true for email
addresses?

That's exactly how I routinely do it. I've got a modular set of tables -
CONtblPeople, CONtblAddresses, CONtblPhones, CONtblEmail - and a little
utility table of CONTypes with values such as Home, Work, Fax, etc.

John W. Vinson [MVP]

 

[Mariah]

Excellent, thank you all for your assistance. I am now equipped to take the
next steps.

 

[Mariah]

I am running into some difficulties and am hoping for some advice.

I now have two main tables: the first one is tblAddresses with the fields
Address1, Address2, City, State, Zip, Country, key. The second one is
tblCustomerNames with fields Title, FirstName, LastName, key.
I have a junction table to connect the two pieces of information together.
There are two lookup columns in this table. In order to see all the address
and name information I had to create two queries: one to combine the address
information and the second to combine the name information. This is because
the lookup column was only showing me the first column of information which
in some cases is blank and in other cases may be just the title such as Ms.
or Mr. The database records the numeric key from both tables as the tracking
information.

I was hoping to create a form for entering information but am running into
trouble, as the subform with address information isn't allowing edits.

Perhaps I went off in the wrong direction. At this time if an address is
used as both mailing and home then I have it listed as two records, with a
field that indicates that one is "home" and the other "mail", then if several
people share an address I have them both linking to the record. Perhaps
those queries to combine the information are also causing havoc where forms
are concerned?

Again, any advice appreciated!

 

[Armen Stein]

That's exactly how I routinely do it. I've got a modular set of tables -
CONtblPeople, CONtblAddresses, CONtblPhones, CONtblEmail - and a little
utility table of CONTypes with values such as Home, Work, Fax, etc.

John W. Vinson [MVP]

We take it even a step further. All emails, phones, and web addresses
are in one table called ContactMethods. A ContactMethodType lookup
table determines each type (Work Email, Home Phone, Personal URL). A
ContactMethodSuperType table contains the basic categories of
ContactMethodTypes (Phone, Email, Web, Other) and is not configurable
by the user.

Using the supertype table, we can do automated processing (like
checking for a valid email address format, sending out mass emails, or
linking to a web site), while still staying flexible to store whatever
new contact methods come up in the future.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Beetle]

Before you get too involved in creating your queries and forms, you should
take another look at your table structure. Your address table should not have
Address1 and Address2 fields. Anytime you have table fields that repeat the
same type of information like this, that's a sign that you have a design flaw.

Likewise, whenever you duplicate the same record within your table (which
you are doing in the case of a home and mailing address) that's an indication
that you should re-think what you are doing.

For your situation, where you have a many-to-many relationship between
customers and addresses, the table structure should look similar to the
following;

tblCustomers
**********
CustomerID (Primary Key)
Title
LastName
FirstName
Other fields related specifically to the customer

tblAddresses
*********
AddressID (PK)
Address
City
StateID (FK to tblStates)
ZipCode
AddressTypeID (FK to tblAddressTypes)
SortOrder

tblCustomerAddresses
****************
CustAddressID (PK)
CustomerID (FK to tblCustomers)
AddressID (FK to tblAddresses)

tblStates
*******
StateID
StateCode

tblAddressTypes
************
TypeID
TypeName

This is just an example. Depending on your situation, you may not need a
separate table for the states (maybe all your customers are in the same
state, in which case you could just have a default value). The main things to
look at are the junction table (tblCustomerAddresses) which will define the
relationship between the customers and their addresses, and the additional
field for sort order (Dale Fye's idea, but I thought it was a good one) in
tblAddresses. This could be a numeric field as Dale suggested, or a text
field, but the point is that this is what you would use to determine if an
Address was a mailing address or not. Whether an address is home/vacation
etc. is a separate piece of information from mailing/non-mailing so it should
be treated as such.

The junction table would typically just store ID numbers that relate back to
the other tables

HTH

--
_________

Sean Bailey

 

[Pat Hartman]

Beetle, despite the numeric ending, Address1 and Address2 are not a
repeating group. The separation of street address into two fields is for
the convenience of printing address labels or envelopes. Rather than have
the computer figure out where the line should break, we prefer that people
do it. The vast majority of applications are fine with 2 or 3 fields for
address and separate fields for city, state, and zip and possibly country.
Only companies that are involved in mass mailings would break down the three
address fields into individual components and they do this so that they can
more easily identify duplicate addresses.

 

[Pat Hartman]

I didn't notice that any one specifically mentioned that address type
actually goes in the junction table when you use this type of setup and it
needs to become part of the PK or unique index. That allows you to use the
same row in the address table for both mailing and residence for example.
You also mentioned that your list contains people that share a single
address. You need to be careful of this in your address maintenance form.
Make sure you ask the user if the change applies to all residents at that
address or only to a particular person. So if Joe is moving out, you don't
want to change Sam's address also. You will need to create a new address
record for Joe.

Your update issue may be caused because you selected the FK field from the
wrong side of the join.

 

[Beetle]

I see your point, and you're right. I probably mis-interpreted what the OP
was doing with the Address1 and Address2 fields. However, I do still think
that storing the same address once as a home address and a second time as a
mailing address is incorrect, but I could be missing something there also.
--
_________

Sean Bailey

 

[Pat Hartman]

I agree and I addressed that issue with my next post. The OP is using a
relation table to implement a m-m between people and addresses. As long as
the address type is in the relation table, where it belongs, and is part of
the key, the same address can be used for multiple purposes and the same
address can also be used by multiple people.

 

[Beetle]

Once again Pat, you are right (I hadn't looked at you other post at the
time). I had the AddressType in the wrong table, so thanks for correcting me.
I gained a little knowledge, which is a good thing (most of the time).
--
_________

Sean Bailey