Multiple address for a Customer/Vendor

gots

Registered User.
Local time
Today, 04:53
Joined
May 2, 2008
Messages
20
I am a newbie to Access and learning the same.

I am stuck in a problem and not able to get through. Please advice

I have made tables as below but am not able to get through the relationships an form for input of data

tblcustomers
- CustomerID - Autonumber - PK
- CustomerType
- BoutiqueType
- Customer Zone
- Customer Number
- Company
- FirstName
- LastName
- Phone1
- Phone2
- Fax
- Mobile
- Email
- notes

tbladdresstype
- Addresstype (eg: Billing, shipping, office, factory, godown, residence etc.)

tblcustomeraddress
- Street
- City
- Pin
- State
- Country
- Phone
- Fax

Now I want to have multiple address for each customer. Please let me know what all the fields to link in relationship and what type of relationship.

What fields to include on form frmCustomerDetails.

Thanks in advance
 
Use the Customer ID in tblcustomeraddress as a foreign key. Set the field to required, no default value and index it. If you have a Customer Maintenance form place the address in a subform and link it (if you drag the form onto the the main form Access may correclty link it you. Then when you add an address the foreign key is automatically populated for you.
 
Unless you have many people at the same address. In that case, you would create a CustomerAddresses table with a foreign key from Customers and a foreign key from Addresses. Under such a scenario, when you change the address of any person at a certain address, it changes that address for all people at that address. This can cause some problems so use it with caution!
 
I have modified the tables as below,

tblcustomers
- CustomerID - Autonumber - PK
- CustomerType
- BoutiqueType
- Customer Zone
- Customer Number
- Company
- FirstName
- LastName
- Phone1
- Phone2
- Fax
- Mobile
- Email
- notes

tbladdresstype
- AddressTypeID - Autonumber - PK
- CustomerID - FK (linked to tblcustomer)
- Addresstype (eg: Billing, shipping, office, factory, godown, residence etc.)

tblcustomeraddress
- CustomerAddressID - Autonumber - PK
- CustomerID (linked to tblcustomer)
- Street
- City
- Pin
- State
- Country
- Phone
- Fax

Is this correct. And please let me know how to get this tables to input the details.

I want to make customer address input form just as it is in Outlook contacts.
 
I would have thought the address type was an attribute of the address and not a seperate table.

i.e.
tbladdresstype
- AddressTypeID - Autonumber - PK
- Addresstype (eg: Billing, shipping, office, factory, godown, residence etc.)


tblcustomeraddress
- CustomerAddressID - Autonumber - PK
- CustomerID (linked to tblcustomer)
- AddressTypeID (linked to tblAddressType)
- Street
- City
- Pin
- State
- Country
- Phone
- Fax
 
And AddressType should not have an FK from Customer.

By the way, what is a "godown" address type? That is terminology I am not familiar with.
 
Thank you for the assitance

The "Godown" Address type is a "Warehouse" and is referred to as godown in INDIA.
 

Users who are viewing this thread

Back
Top Bottom