A better method

[Joe Cilinceon]

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)

 

[tina]

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]

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

 

[Joe Cilinceon]

It worked perfectly and thanks again

 

[tina]

you're welcome

It worked perfectly and thanks again