A better method

I have a Tenant table that holds the tenant information including address. I
also have other tables linked to the tenant by the CustNo that handles
multiple contact, notes (documented conversations), and letters sent.

What I'm looking for are suggestions on the best way to keep multiple
address for a single tenant. All will have a home or business address but
might have a different mailing address for example. Also do to legal issues
if the tenant goes into lien I'm also require to keep all outdated address
on file.

Typical table
CustNo
Address1 (Main line)
Address2 (If needed for a longer address)
City
State
Zip
Country
Unknown fields : (Some kind of flag that will help to choose a record as the
mailing address or no longer valid)


--

Joe Cilinceon

well, pretty much looks like you've got it already. your "parent" table is
tblCustomers, with CustNo as the primary key field; it should not have any
address fields in it at all. tblCustomerAddresses is the "child" table, with
the address fields that you listed, and CustNo as the foreign key field
linking the records back to tblCustomers. you could create a
tblAddressTypes, as

tblAddressTypes
ATypeID (primary key)
ATypeName
(types would be Home, Business, Mailing, etc.)

add field ATypeID to tblTenantAddresses as a foreign key field, so you can
assign an address type to each address record. you might want to add a
DateTime field to tblTenantAddresses also, to record when the tenant
"acquired" the address, or perhaps when it was disclosed to you - whatever
may be appropriate to support those legal issues you mentioned. you may also
want to add a Yes/No field called Inactive, where Yes = the address is
"inactive", or outdated; or again, you may want a date/time field to track
this.

hth


"Joe Cilinceon" wrote in message
...
I have a Tenant table that holds the tenant information including address.
I
also have other tables linked to the tenant by the CustNo that handles
multiple contact, notes (documented conversations), and letters sent.

What I'm looking for are suggestions on the best way to keep multiple
address for a single tenant. All will have a home or business address but
might have a different mailing address for example. Also do to legal
issues
if the tenant goes into lien I'm also require to keep all outdated address
on file.

Typical table
CustNo
Address1 (Main line)
Address2 (If needed for a longer address)
City
State
Zip
Country
Unknown fields : (Some kind of flag that will help to choose a record as
the
mailing address or no longer valid)


--

Joe Cilinceon

Thanks you tina I will save this and give it a try, much appreciated.

--

Joe Cilinceon


tina wrote:
well, pretty much looks like you've got it already. your "parent"
table is tblCustomers, with CustNo as the primary key field; it
should not have any address fields in it at all. tblCustomerAddresses
is the "child" table, with the address fields that you listed, and
CustNo as the foreign key field linking the records back to
tblCustomers. you could create a tblAddressTypes, as

tblAddressTypes
ATypeID (primary key)
ATypeName
(types would be Home, Business, Mailing, etc.)

add field ATypeID to tblTenantAddresses as a foreign key field, so
you can assign an address type to each address record. you might want
to add a DateTime field to tblTenantAddresses also, to record when
the tenant "acquired" the address, or perhaps when it was disclosed
to you - whatever may be appropriate to support those legal issues
you mentioned. you may also want to add a Yes/No field called
Inactive, where Yes = the address is "inactive", or outdated; or
again, you may want a date/time field to track this.

hth


"Joe Cilinceon" wrote in message
...
I have a Tenant table that holds the tenant information including
address. I also have other tables linked to the tenant by the CustNo
that handles multiple contact, notes (documented conversations),
and letters sent.

What I'm looking for are suggestions on the best way to keep multiple
address for a single tenant. All will have a home or business
address but might have a different mailing address for example. Also
do to legal issues if the tenant goes into lien I'm also require to
keep all outdated address on file.

Typical table
CustNo
Address1 (Main line)
Address2 (If needed for a longer address)
City
State
Zip
Country
Unknown fields : (Some kind of flag that will help to choose a
record as the mailing address or no longer valid)


--

Joe Cilinceon

It worked perfectly and thanks again

--

Joe Cilinceon

tina wrote:
well, pretty much looks like you've got it already. your "parent"
table is tblCustomers, with CustNo as the primary key field; it
should not have any address fields in it at all. tblCustomerAddresses
is the "child" table, with the address fields that you listed, and
CustNo as the foreign key field linking the records back to
tblCustomers. you could create a tblAddressTypes, as

tblAddressTypes
ATypeID (primary key)
ATypeName
(types would be Home, Business, Mailing, etc.)

add field ATypeID to tblTenantAddresses as a foreign key field, so
you can assign an address type to each address record. you might want
to add a DateTime field to tblTenantAddresses also, to record when
the tenant "acquired" the address, or perhaps when it was disclosed
to you - whatever may be appropriate to support those legal issues
you mentioned. you may also want to add a Yes/No field called
Inactive, where Yes = the address is "inactive", or outdated; or
again, you may want a date/time field to track this.

hth

you're welcome


"Joe Cilinceon" wrote in message
...
It worked perfectly and thanks again

--

Joe Cilinceon

tina wrote:
well, pretty much looks like you've got it already. your "parent"
table is tblCustomers, with CustNo as the primary key field; it
should not have any address fields in it at all. tblCustomerAddresses
is the "child" table, with the address fields that you listed, and
CustNo as the foreign key field linking the records back to
tblCustomers. you could create a tblAddressTypes, as

tblAddressTypes
ATypeID (primary key)
ATypeName
(types would be Home, Business, Mailing, etc.)

add field ATypeID to tblTenantAddresses as a foreign key field, so
you can assign an address type to each address record. you might want
to add a DateTime field to tblTenantAddresses also, to record when
the tenant "acquired" the address, or perhaps when it was disclosed
to you - whatever may be appropriate to support those legal issues
you mentioned. you may also want to add a Yes/No field called
Inactive, where Yes = the address is "inactive", or outdated; or
again, you may want a date/time field to track this.

hth