If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
A better method
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 |
#3
|
|||
|
|||
A better method
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 |
#4
|
|||
|
|||
A better method
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 |
#5
|
|||
|
|||
A better method
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Line Method Explanation? | Joanthan | Setting Up & Running Reports | 5 | April 9th, 2006 05:12 PM |
onkey method | freekrill | General Discussion | 1 | October 12th, 2005 01:31 PM |
Most Efficient Calculation Method? | MikeC | Setting Up & Running Reports | 1 | June 21st, 2005 03:27 PM |
Listbox.additem error "Method Or Data Member Not Found" | Kelv | General Discussion | 1 | April 26th, 2005 01:52 AM |
code method to switch views | scott | Using Forms | 4 | July 7th, 2004 11:19 PM |