I'm just getting started an I have some questions on tables.

[Marc]

I deal with both companies and end users and I'm trying to figure out how to
set up the contact table. My plan was to set one table for companies and
have contact entries as a sub form and another form for contacts with
individuals but both the company contact sub form and the individual form
would link to the same table? My reasoning is that I may have many contacts
to one company but have individuals that contacts normally at most would be
"domestic partners"and their family members and I want to track contacts
whether it's email, phone calls, letters and more. The first question on my
form would be which kind of contact would I be working with and bring up the
correct form. I know that this will be the first of many questions.

Marc

 

[DL]

I'd have thought a single Contacts Tbl containing a yes/no field for
Bus/Personal
You could then, if neccessary, have a seperate data entry form for the two
types of contact.
Mind you assuming your using MS Office you could allways use Outlook
Contacts

 

[Marc]

How would you handle multiple branch locations?

Marc

 

[Mike Revis]

Just a thought here but how about treating both companies and individuals as
the same type of entity. i.e. "customers" or "people and companies I want to
keep track of".
You could then have one table for "customers" and have a field for "type" in
which you could select either business or individual. Your contact table
would then contain the contacts at either the company or individual.
If the type and amount of contact data you are collecting for companies is
greatly different from the contact data collected for individuals you should
have a separate contact table for companies and individuals with a separate
data entry form for each. Which contact form to open could be determined by
the entry in the "type" field.

Mike

 

[Mike Revis]

Same company name. Different locations. I have this a lot in my business.
The way I have handled this is to have a field CompanyID(AutoNumber) and a
field CompanyName(Indexed duplicates yes) this way I can have a lot of
duplicate company names but maintain the individuality of each location.

Mike

 

[Mike Revis]

Just a thought here but how about treating both companies and individuals as
the same type of entity. i.e. "customers" or "people and companies I want to
keep track of".
You could then have one table for "customers" and have a field for "type" in
which you could select either business or individual. Your contact table
would then contain the contacts at either the company or individual.
If the type and amount of contact data you are collecting for companies is
greatly different from the contact data collected for individuals you should
have a separate contact table for companies and individuals with a separate
data entry form for each. Which contact form to open could be determined by
the entry in the "type" field.

Mike

 

[Marc]

The reason for the company/customers was having all the contacts - people -
in the customer table. Otherwise I'm going to have both in the same table
that seems awkward to me.

Marc

 

[Marc]

To make this a bit more complex I also need to have a place to store the job
address which most of the time will be different than either company or
customer.

Marc

 

[Marc]

Also a contact with a company can be a customer as an individual.

Marc

 

[Marc]

I understand will there be a problem with the companies having contacts all
in the same table?

Marc

 

[DL]

What you need/require seems to have changed substantialy, from a 'simple'
contacts db to nearly a full blown PIM. Perhaps you might be advised to
write down everything you require first.
Then layout the tables on paper, you only need show main/index fields, that
way you can see what is required and their relationships. Only then would
you fire up Access

 

[Marc]

Part of it's a PIM but the other part is a way of generating quotes, scope
of work, invoices (exported to QuickBooks) and possible inventory. I deal
with builders, end user and commercial customers that have different price
structures. In some cases the builder (company) becomes a customer (end
user) when they build their own homes. The builders often have their
infra-structure - purchasing, AP, superintendents etc. that have to be
referenced to the builder and have different locatyions. In some cases the
superintendent's office is in their home with completely different contact
information that the office and I need to send information to both. This is
why I see a need for both tables - tell me if I'm wrong. Eventually we
want to move the quote part on-line for the builders clients to use.

Marc

 

[DL]

It may be helpfull if you gave your (Bus.) position in all this.
eg The Architect who may be contracted by the builder (customer) or may be
contracted by the customer ( Individual who then contracts the builder)
We could then more easily follow the structure required.
You might also look here,
http://www.databaseanswers.org/data_models/index.htm
there maybe something that helps

 

[Marc]

I'm a low voltage contractor i.e. phone, cable, networking, distributed
audio, home theater, secuirty, home automation, lighting contol, central
vacuum, intercom, CCTV and access control. We pre-wire, sell and install
all the equipment. So we have applications across the board - commercial,
residential - new construction and retro. Right now I'm using a workbook in
Excel which works - kind of.
Marc

 

[DL]

If you follow link, in prev.post, then check out 64,65,57,68, and their
various links, it shows you how to handle clients with multiple addresses
and multiple contacts.
I don't see it making any difference, other than to rates as to what type of
customer or what type of installation. You can in any case use a 'customer
type' field and 'installation type' which can in turn be linked to various
rates tbls.

 

[Marc]

Thanks for the site. It gives me some ideas - scary!

Marc

 

[DL]

At first maybe, but its all logical.
Use pencil and paper PRIOR to Access
Remember the idea with a tbl is that the same info is not repeated if it is
it generally means you need another tbl
eg A Customer Tble should not have same CustName repeated if it is it
probably means you need a Client Tbl and a Contract Tbl the two being linked