Saving client addresses

[ngan]

We need to save the addresses of our clients when we send out letters This
way, we have a static history, in case our client wonder where we sent the
letters.

we have a tblClient that house the current addresses of the client.

We have several tables where we will send out letters, using the client's
address. For instance, the Complaints and NoShow tables contain clientID,
name, address and phone fields.

Every time a client has a complaint or a noshow, we have to have a copy of
the client's current address in the two tables. We use the information to
send out letters (using a report template). We can't use the current info in
tblClient because that is always changing. We need to know what address we
used to send out a letter in case the client questions it.

This means having the information copied in several tables. Seems
redundant, but I can't see how else to do it if we need a historic record of
the letter.

Am I setting up the tables correctly? There could be 3-4 tables that
require letters and therefore require saving the address info.

Thanks.

 

[Brian]

You could set up a ClientAddresses table and allow each ClientID to have
multiple addresses here. Have an AutoNumber primary key (along with ClientID
& all the address fields), and use that as the referenced field in the other
table instead of recording the entire address in the other table. This would
at least allow you to record each unique address for the client only once
instead of multiple times.

Example: ClientID 1 is Acme Industries, ClientID 2 is MyCompany

AddressID 1 is for ClientID 1 and is 123 4th St...
AddressID 2 is for ClientID 2 and is 2344 7th Ave
AddressID 3 is for ClientID 1 and is 795 Evergreen....

Using filters correctly, when you send out a letter to Acme (ClientID 1),
you could choose to pick Address #1 or #3 (or create a new one). If you send
a letter to MyCompany (ClientID 2), you would be limited to AddressID 2 (or
create a new address for MyCompany)

 

[tina]

Brian gives you a good table design solution in his post, ngan. just to add
my two cents worth:

when you create tblClientAddresses, you'll need to remove the address fields
from your tblClients. you may want to add a field to tblClientAddresses to
indicate which, of several addresses for a single client, is the *current*
address. a couple of different ways to handle this might be:
1) add a Yes/No field to tblClientAddresses, called Current. you can set a
unique index on the table, composed of the ClientID field AND the Current
field, so only one client address may be marked as "current" at any given
time. that's a simple solution at the table/query level, but may take some
extra work at the form level to make the data entry smooth and simple for
the user.
OR
2) add a Date/Time field to tblClientAddresses, called DateAdded. set the
default value of the field to Now(). each time a new record is added, it
will be automatically date/time stamped. this is a simple solution at the
table/form level, requiring no attention during data entry. but it will take
a little extra work at the query level, to select the "most recently added"
address for each client.

hth

 

[peregenem]

Brian gives you a good table design solution in his post, ngan. just to add
my two cents worth:

And just to add mine ....

1) add a Yes/No field to tblClientAddresses, called Current.

Too difficult to maintain, IMO.

2) add a Date/Time field to tblClientAddresses, called DateAdded. set the
default value of the field to Now().

Yes, call it start_date and constrain with NOT NULL, then make the
primary key for the table be (client_ID, start_date).

it will take
a little extra work at the query level, to select the "most recently added"
address for each client.

Easier to achieve if you also add a nullable end_date column i.e. a
NULL end_date means the row is current for the given client_ID. Anyhow,
any complexity may be hidden in a VIEW (SELECT query persisted in a
Query object).

 

[ngan]

All of your suggestions sounds great....my problem is the ease for the user.

I created an asp webpage that links to the tblClient in the SQL server.
This is the only place where all the client info and addresses can be
modified. So I would have to have a few more steps (and code) for the user
to go through to add or modify an address.

The Complaints and NoShow forms are in Access XP and I would also have to
have more steps for the user to choose the rider and then the address they
want.

Here is another stickler:

Let's say the client calls for a complaint. We select the client and his
current address. He then says: I want the response to be sent to my mother's
address. He never uses his mother's address any other time. Am I to add
this address to the tblClientAddress just for this one time use?

We get alot of these one time addresses with the complaints. Or else it
could be a non-client who calls in a complaint so we use the ClientID of 1
(N/A) and then override the name and address so we can send them the response
letter. How would the tblClientAddress work in case of non-clients all with
the ID of N/A?

 

[peregenem]

Here is another stickler:

Let's say the client calls for a complaint. We select the client and his
current address. He then says: I want the response to be sent to my mother's
address. He never uses his mother's address any other time. Am I to add
this address to the tblClientAddress just for this one time use?

Put it in a separate 'anomalies' table. Create a view/proc/etc to
select from the main 'addresses' table only where the 'anomalies' table
turns up a null for the required interval.

 

[tina]

well, i can't address SQL server or asp issues, so i'll stick with the
relational table design issues. comments inline.

Let's say the client calls for a complaint. We select the client and his
current address. He then says: I want the response to be sent to my mother's
address. He never uses his mother's address any other time. Am I to add
this address to the tblClientAddress just for this one time use?

yes. in a relational database, "records are cheap". it doesn't matter if you
use a given address only one time. it still belongs in the address table,
where it can be related to a specific client record.

We get alot of these one time addresses with the complaints. Or else it
could be a non-client who calls in a complaint so we use the ClientID of 1
(N/A) and then override the name and address so we can send them the response
letter. How would the tblClientAddress work in case of non-clients all with
the ID of N/A?

well, i don't see how you can assign the same ClientID to multiple records -
unless the ClientID field is NOT the primary key field in tblClients. if
it's not, then whatever field IS the primary key field in tblClients, that's
the field that should be used as a foreign key in tblAddresses - and the two
tables linked on those matching primary/foreign key fields.

so you don't have a problem. every record in tblClients has a unique primary
key, whether that record is a client or non-client. so when you enter a
non-client's address in tblAddresses, it is linked back directly to that
non-client's record in tblClients, via the primary/foreign key link.

hth

 

[ngan]

yes. in a relational database, "records are cheap". it doesn't matter if you
use a given address only one time. it still belongs in the address table,
where it can be related to a specific client record.

This is true even if it means more coding for me and more work/steps for the
user?

well, i don't see how you can assign the same ClientID to multiple records -
unless the ClientID field is NOT the primary key field in tblClients. if
it's not, then whatever field IS the primary key field in tblClients, that's
the field that should be used as a foreign key in tblAddresses - and the two
tables linked on those matching primary/foreign key fields.

Our table is for Clients and for non-clients, we usually don't care about
storing their names/addresses. so that's why all non-clients are clumped into
one ID.

 

[tina]

comments inline.

This is true even if it means more coding for me and more work/steps for the
user?

if your tables/relationships are designed according to standard
normalization rules, and relational data modeling principles,
finding/displaying/utilizing linked data should be relatively simple. at
least that's the case in Access; as noted earlier, i can't speak to SQL
Server and asp issues.

Our table is for Clients and for non-clients, we usually don't care about
storing their names/addresses. so that's why all non-clients are clumped into
one ID.

that doesn't change the fact that your Clients table, and every other table
in your database should have a primary key - which is by definition a unique
value identifying each record in a table. if you are assigning the same
ClientID value to multiple records, then the ClientID field *cannot* be the
table's primary key. if you follow standard relational design principles,
you will use the primary key field in tblClients, as a foreign key in
tblAddresses - so that, as i said before, each record in tblAddresses is
related to a single record in tblClients.

SQL Server is a relational database management system, so it's important you
understand the principles of relational data modeling in order to design
your tables/relationships correctly. one good text is Database Design for
Mere Mortals by Michael Hernandez. you can also find plenty of information
at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

hth

 

[peregenem]

it doesn't matter if you
use a given address only one time. it still belongs in the address table,
where it can be related to a specific client record.

So you'd have the 'addresses' table end up like this

ClientID=42
Address='Somewhere Downtown'
start_date=1994-08-01 09:00:00
end_date=2005-07-26 21:26:00

ClientID=42
Address='Moms house'
start_date=2005-07-26 21:26:01
end_date=2005-07-26 21:26:59

ClientID=42
Address='Somewhere Downtown'
start_date=2005-07-26 21:27:00
end_date=

because the client spent a minute out the last decade sending a card to
her mother?!