Contacts with and without companies

[Mary Ann]

I am creating a database for my business contacts. Some belong to a company,
where I would have several contacts from the same company. Some are private
individuals. How do I design the database so that the address fields for the
company contacts “belong†to the company while the address fields for the
private individuals “belong†to the individual?
I would eventually like to be able to see all my contacts together, whether
they are in a company or private individuals.
I am familiar with creating relationships between tables but am having
difficulty getting the fundamental design here.

 

[KARL DEWEY]

Use a one-to-many relationship --
Company ---
CompID – Autonumber – Primary key
Name – text
Addr1 – text
Addr2 – text
City – text
ST – text
ZIP – text
ZipPlus – text
Phone – text
FAX - text
Type - text

Contacts ---
ContactID – Autonumber – Primary key
CompID – number – long integer – foreign key – related to Company. CompID
LName – text
FName – text
MI – text
Prefered – text – Bill, Joe, Sam for William, Joseph, Samuel, etc.
Addr1 – text
Addr2 – text
City – text
ST – text
ZIP – text
ZipPlus – text
Phone – text
FAX - text

Create a company named Null for those without a company affiliation.

In your query to pull addresses use IIF statement –
IIF([Company].[Name] = “Nullâ€, [Contacts].[Addr1], [Company].[Addr1])

 

[Mary Ann]

Thanks Karl. I'd thought about repeating the address info in both tables, but
hadn't thought about using the IF statement.

Use a one-to-many relationship --
Company ---
CompID – Autonumber – Primary key
Name – text
Addr1 – text
Addr2 – text
City – text
ST – text
ZIP – text
ZipPlus – text
Phone – text
FAX - text
Type - text

Contacts ---
ContactID – Autonumber – Primary key
CompID – number – long integer – foreign key – related to Company. CompID
LName – text
FName – text
MI – text
Prefered – text – Bill, Joe, Sam for William, Joseph, Samuel, etc.
Addr1 – text
Addr2 – text
City – text
ST – text
ZIP – text
ZipPlus – text
Phone – text
FAX - text

Create a company named Null for those without a company affiliation.

In your query to pull addresses use IIF statement –
IIF([Company].[Name] = “Nullâ€, [Contacts].[Addr1], [Company].[Addr1])

--
KARL DEWEY
Build a little - Test a little

I am creating a database for my business contacts. Some belong to a company,
where I would have several contacts from the same company. Some are private
individuals. How do I design the database so that the address fields for the
company contacts “belong†to the company while the address fields for the
private individuals “belong†to the individual?
I would eventually like to be able to see all my contacts together, whether
they are in a company or private individuals.
I am familiar with creating relationships between tables but am having
difficulty getting the fundamental design here.