Prospective Client Tracking Database

[TGC]

I am new to Access 2007 and recently reviewed all the online tutorials on the
Microsoft website and reviewed the provided templates (none of which fit my
needs). I understand the general concept of tables, forms, queries, reports,
etc. and have created tables and attempted to create a form for my data, but
am a little unsure about my design and was hoping for some feedback from
others that know Access better than I (not hard to accomplish that
distinction, btw!).

I want to create a prospective client tracking database for a commercial
landscape company. I am looking to collect information on companies, their
contact people, and the properties they manage. One company, for example,
may have multiple contact people and those contact people may manage multiple
properties. Within those 3 main areas (companies, contacts, properties)
there are a bunch of fields relating to the area (phone, email, address,
etc.).

I started by creating 3 tables (companies, contacts, properties) and then
created relationships amongst the tables be relating the contacts' first and
last name with the companies table, relating the properties' street address
and the companies' name with the contacts table, and relating the contacts'
first and late name with the properties table.

I then created a form with the form wizard that had the companies table as
the main form and the contacts and properties tables as the subforms.

Am I going about this the right way? All I want to be able to do is track
the companies and be able to add multiple contacts and multiple properties
within the companies.

Thank you in advance for your help!

 

[Steve]

Conside starting with these tables:

TblCompany
CompanyID
CompanyName
<address fields>
<other company attribute fields>

TblCompanyContact
CompanyContactID
CompanyID
FirstName
LastName
<other contact fields>

TblPropery
PropertyID
<property description fields>
<property location fields>

TblCompanyContactProperty
CompanyContactPropertyID
CompanyContactID
PropertyID


<other attribute fields>, <other contact fields>, <property description
fields> and <property location fields> need to be refined. Start with
postiing your <other company attribute fields> and we will help you with the
normalized design of your tables.

Steve
(e-mail address removed)

 

[John W. Vinson]

I am new to Access 2007 and recently reviewed all the online tutorials on the
Microsoft website and reviewed the provided templates (none of which fit my
needs). I understand the general concept of tables, forms, queries, reports,
etc. and have created tables and attempted to create a form for my data, but
am a little unsure about my design and was hoping for some feedback from
others that know Access better than I (not hard to accomplish that
distinction, btw!).

I want to create a prospective client tracking database for a commercial
landscape company. I am looking to collect information on companies, their
contact people, and the properties they manage. One company, for example,
may have multiple contact people and those contact people may manage multiple
properties. Within those 3 main areas (companies, contacts, properties)
there are a bunch of fields relating to the area (phone, email, address,
etc.).

I started by creating 3 tables (companies, contacts, properties) and then
created relationships amongst the tables be relating the contacts' first and
last name with the companies table, relating the properties' street address
and the companies' name with the contacts table, and relating the contacts'
first and late name with the properties table.

I then created a form with the form wizard that had the companies table as
the main form and the contacts and properties tables as the subforms.

Am I going about this the right way? All I want to be able to do is track
the companies and be able to add multiple contacts and multiple properties
within the companies.

Thank you in advance for your help!

I would recommend at least some changes. Your basic table structures look ok
for a start... but!

Names are NOT good keys or linking fields. A Key should meet three
requirements: it *must* be unique; it should be stable, not changing over
time; and ideally it should be short. People's names fail on all three counts!

The Primary Key of your Companies table should be a numeric, meaningless
unique identifier, such as an Autonumber (CompanyID let's call it). This would
be related to a Long Integer CompanyID in the Contacts table. Similarly, each
Contact should have a numeric ContactID; otherwise you might have Dale
Williams, the big husky guy, and Dale Williams, the nice grey-haired lady,
getting their properties mixed up.

You also need to be sure that your relationships reflect the business logic.
Is a Property in fact managed *by an individual contact* or by a company?
Might a property have more than one person who you might need to contact?

Also be sure you're not storing data redundantly. Phone, email, address need
to be in the table for the entity (person or company) that uses them, and
should NOT be duplicated in any other table; a property probably does not have
an email address, for example.

Note also - you may be doing this correctly, but it wasn't clear - that the
"foreign key" goes in the Many table. That is, there should be a CompanyID in
the Contacts table, but there should not be any information about the Contact
in the Companies table.

 

[TGC]

That makes sense!

Here is what I have so far based on what you started...

TblCompany
CompanyID
CompanyName
Street
City
Zip
Phone
Fax
Website
Status
Type
Receivership

TblCompanyContactID
CompanyID
FirstName
LastName
Status
BusinessPhone (the phone from the company table would be populated in this
field as an option-only want the phone numbers listed within the applicable
company though, not all numbers entered into database)
OfficePhone
CellPhone
Email
Fax
Correspondence
BidSchedule

TblProperty
PropertyID
Street
City
Zip
ContactName (the contact first and last name from the contact table would be
populated in this field as an option-only want the contacts listed within the
applicable company though, not all contacts entered into database)
Type
Receivership
CurrentPrice
CurrentVendor
PastTGCBid
Interior
BidSchedule
AreaSchedule
Notes

There are a few fields named the same in different tables...those are not
automatically the same unless noted in my notes above where I want the
options to populate for me to choose from.

From here, I obviously need to assign relationships and then I want to
create a form. I started one with the form wizard where the company table is
the main form and the contact and property tables are subforms. It is
aesthetically what I am looking for, but a few of my relationships must not
be right because the contacts and properties I enter are not being associated
with just the one company I want them associated with. Within each company,
there can be multiple contacts and multiple properties, but each contact and
each property should only be assigned to one company. Hopefully that makes
sense????

I also did not use your final "CompanyContactProperty" table when I first
started so that is probably where my problem is.

Thank you so much for your help!

 

[Gina Whipp]

TGC,

Let's stay away from those Reserved Words,ie: Type... as Access doesn't
respond well which will cause you problems. For a complete list see...
http://allenbrowne.com/AppIssueBadWord.html

And you probably want to add CompanyID to your tblProperty.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[TGC]

Thank you for the feedback John. That all makes sense!

 

[Steve]

And you probably want to add CompanyID to your tblProperty >>

NO-oooo!!! That is wrong!!!!

Steve

 

[Gina Whipp]

Steve,

Not sure why you jumping p and down. Does not each Property have a company
assigned? And then each Company have multiple Contacts? That is what I
read...

tblProperty (1 to 1) > tblCompany (1 to many) >tblCompanyContacts
pCompanyID (FK) > pCompanyID (FK) > ccCompanyID (FK)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Steve]

Your tables are still not correct!!! You need to get the tables correct
first, then create the relationships before you even think about forms. If
you don't get the tables correct, you will find yourself undoing a lot of
work.

It looks like you need a TblCompanyType table. What is Receivership?

Your TblCompanyContactID should be TblCompanyContact with CompanyContactID
as primary key. BusinessPhone is wrong. What is Correspondence? You probably
need a TblCorrespondence. Please describe BidSchedule. You probably need
multiple tables for BidSchedule.

In TblProperty, ContactName et al fields are all wrong. Let's get TblCompany
and TblCompanyContact right first then work on TblProperty.

DO NOT BE MISLEAD!!! Gina Whipp's suggestion is 100% wrong!!!!

Steve

 

[Gina Whipp]

Steve,

Stop... The OP stated she need one Company associated to each Propery, why
would that be wrong? Are you suggesting another joiner table? My
suggestion is correct based on the OP's needs.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Fred]

From the given information, there is certainly a strong possibility that
Gina's added filed/link is the way to go.

And from the given information, ruling it out is certainly en error. So
Steve's making of a "NO-oooo!!! That is wrong!!!!" statement at that point is
certainly itself an error , and making it that way was rude.

 

[Keith Wilby]

Gina Whipp's suggestion is 100% wrong!!!!

How so?

 

[Gina Whipp]

Fred,

Thank you... starting think I had read something wrong!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Ketih,

I am still waiting to hear that myself...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Keith Wilby]

Ketih,

I am still waiting to hear that myself...

Perhaps he's away grinding another axe.

 

[Gina Whipp]

Keith,

Whenever he does that I find he just scurries away... In the meantime, it's
the OP who is left confused.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[TGC]

Hi Gina -

It sounds like you might be understanding what I am trying to do...

I have a bunch of prospective companies. And there are really 2 main models
I am looking at. One company with one contact who manages multiple
properties OR one company with multiple contacts who each manage multiple
properties.

All the fields are pretty straightforward with the company table. The only
tricky one with the contact table is the Business_Phone. I want to be able
to see the phone number I entered in the company table so when I run a query
on a contact, the main business phone number shows up. And the only tricky
one with the property table is the contact name. I want the first and last
name of the contacts I entered for any given company to show up here so I can
assign contacts to each property within the company.

Steve made a note about adding a correspondence table into this database.
The more I think about it, I think that is a good idea. With the
correspondence table, I would be able to record date, message, collateral
info that was communicated to prospective client. That new table would need
to relate to the contact table.

Once all the basics are set up, I would also like to be able to do a few
additional things....

- Email prospective clients directly in Access
- Upload marketing collateral into prospective client database so it is
easily emailed through Access
- Create a current client database (with service request log table built in)
- Ability to transfer info from prospective client database to current
client database once a contract is signed
- Email current clients from current client database directly in Access

The more I write the more I am realizing that this isn't as simple as I had
originally thought!!!

Any brilliant suggestions?

Thank you!

 

[Steve]

<<The more I write the more I am realizing that this isn't as simple as I
had originally thought!!! >>

I provide help with Access, Excel and Word applications. My fees are very
reasonable. Let me work with you to design the tables for your database. I
have worked with numerous customers designing the tables for their
databases. As I previously said, you need to get your tables right before
you start creating the functionality part of your database. Some of what you
described is not correct. If you don't get the tables right, you will end up
undoing previous work that you might have spent a lot of time on. If we work
together, we can save you a lot of time creating your database. Once you
hane a proper set of tables, you can then create the rest of the database
yourself. Contact me.

Steve
(e-mail address removed)

 

[Gina Whipp]

TGC,

....Help here is FREE and I am posting answers to your questions just taking
me a bit to get it all typed up. I can see by Steve's reply, he is hoping
to get you to pay for help in these FREE newsgroups, which is probably what
he was hoping for all along. Be patient, we are here and WE, that being the
ones who help for FREE, will help you to the end and for FREE.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

TGC,

Below I have adjusted your tables slightly... still going to need a few more
but wanted to get you started. I have also answered you questions (for
FREE) in-line, see below. Please review my questions... once I see your
answers I will finish up the table layout... for FREE.

tblProperty
pPropertyID (PK)
pCompanyID (FK)
pAddress1
pAddress2
pCityID
pPostalCode
pTypeID (What is this for?)
pReceivership (Why is this also in the Company table, what are you trying
to track?)
pPrice
pVendorID (Please elaborate)
pPastTGCBid
pInterior (What are you looking to put in this field?)
pBidSchedule
pAreaSchedule
pNotes (If going to be Memo field then it needs to go in a seperate table)


tblCompany
cCompanyID (PK)
cCompanyName
cMainPhoneNumber
cAddress1
cAddress2
cCityID
cPostalCode
cFaxumber
cWebsite
cStatusID
cTypeID (Not sure what this is)
cReceivership

tblCompanyContact
ccCompanyContactID (PK)
ccCompanyID (FK)
ccFirstName
ccLastName
ccDirectPhoneNumber
ccCellPhone
ccFaxNumber
cceMailAddress
ccContactType (Yes/No - to determine if Main Contact or not)
ccStatus

tblActivities
aDate
aActivityTypeID (FK - Correspondence, Phone, eMail, Meeting, etc...)
aNotes (Prefer you don't use a Memo field data type here)
aDone (Yes/No)

BidSchedule - What is this and why here and in Property table?)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi Gina -

It sounds like you might be understanding what I am trying to do...

I have a bunch of prospective companies. And there are really 2 main
models
I am looking at. One company with one contact who manages multiple
properties OR one company with multiple contacts who each manage multiple
properties.

All the fields are pretty straightforward with the company table. The
only
tricky one with the contact table is the Business_Phone. I want to be
able
to see the phone number I entered in the company table so when I run a
query
on a contact, the main business phone number shows up. And the only
tricky
one with the property table is the contact name. I want the first and
last
name of the contacts I entered for any given company to show up here so I
can
assign contacts to each property within the company.

****This will not be a problem as long as you place a ContactType in the
table. A query can be run to show Main Contact and if Property Phone exists
show that, if not show ContactDirectPhoneNumber

Steve made a note about adding a correspondence table into this database.
The more I think about it, I think that is a good idea. With the
correspondence table, I would be able to record date, message, collateral
info that was communicated to prospective client. That new table would
need
to relate to the contact table.

****See above

Once all the basics are set up, I would also like to be able to do a few
additional things....

- Email prospective clients directly in Access

****No problem

- Upload marketing collateral into prospective client database so it is
easily emailed through Access

****No problem

- Create a current client database (with service request log table built
in)

****No problem

- Ability to transfer info from prospective client database to current
client database once a contract is signed

****Actually, you would be better of keeping all Client Types in the same
database, unless there is a seperate system that handles Clients?

- Email current clients from current client database directly in Access

****No problem

The more I write the more I am realizing that this isn't as simple as I
had
originally thought!!!

****No, Access is not easy but is anything in life worth having? It will be
a bit of *work* but in the end it will be worth it. And we will be right
here to help (for FREE) when you get stuck. So let's focus on getting the