View Single Post
  #25  
Old January 21st, 2010, 10:31 PM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default Prospective Client Tracking Database

Wow, thank you so much for your help! And I apologize for creating a problem
with other discussion group responders.

I responded to your questions.

tblProperty
pPropertyID (PK)
pCompanyID (FK)
pAddress1
pAddress2
pCityID
pPostalCode
pTypeID (What is this for?) - This would be a lookup where I have 4 different commercial property types - office, retail, industrial, hospitality
pReceivership (Why is this also in the Company table, what are you trying
to track?) - The receivership would be a yes/no checkbox. A company would have that field because they might specialize in receiverships (commercial space owned by the bank) and the property would have that field because the property itself might be in receivership.
pPrice
pVendorID (Please elaborate) - This would be a simple text box where I could enter a properties' current landscape vendor.
pPastTGCBid
pInterior (What are you looking to put in this field?) - We also have an interior landscape division so this would be a yes/no checkbox for me to track whether or not a client has interior needs.
pBidSchedule
pAreaSchedule
pNotes (If going to be Memo field then it needs to go in a seperate table) - I was just hoping to have a section where I could enter additional notes, if needed for each property.


Also, on the properties, I want to be able to assign a contact from within
the account to each property. Shouldn't that be an option here? I wanted to
be able to see the first and last name options of the contacts. This might
be done with a relationship as opposed to a field in the table?

Oh, and I just had a new idea associated with the properties. I would like
to be able to track current bids per property. So, I am guessing I might
need a new table. I want to track the date the bid was issued, which staff
member issued it, what the price was, and include any attachments associated
with the bid (contract, budget, etc.), if possible.


tblCompany
cCompanyID (PK)
cCompanyName
cMainPhoneNumber
cAddress1
cAddress2
cCityID
cPostalCode
cFaxumber
cWebsite
cStatusID - This is a simple drop down menu for me to choose what type of client they are - current, prospective, hybrid
cTypeID (Not sure what this is) - This would be a lookup where I have 5 different commercial property types - office, retail, industrial, hospitality, multiple. This is similar to what is in the properties table, but I need them to be both because I need to be able to categorize both the company and the property separately.
cReceivership

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


BidSchedule - What is this and why here and in Property table?) - The bid schedule would be a calendar both within the contact and property tables. In this business, sometimes the contract bid will be handled property by property, other times it will be handled by the contact for each of the properties they manage. This is just a way for me to track when an account will be going to bid. The bid schedules should not be the same for the contact and the property.



I also want to make sure I can have the main company phone number assigned
to a contact. You answered this in your response to a previous question of
mine, so maybe I don't need to include it here? I also see you put a new
field for ccContactType so that probably answers it.

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


In terms of the current client database idea....We currently track current
clients in a program called Gold Mine. Down the road, I would like to track
all current clients' info as well as transfer the account info from the
prospects database and include some new additional features (include a
service/request log, notify supervisors via email of service/requests, etc.).
We aren't quite ready for all that yet and I just wasn't sure if that would
eventually be an add-on to this database or be a whole new one. Suggestions?

Oh, and this is probably very naive, but do I need to put the lowercase
letter in front of each field that obviously ties to the table when I am
labeling the fields?

Thank you again SOOO much for your help. I look forward to your response
and to continuing with this project.

Ellen

"Gina Whipp" wrote:

TGC,

I did see your response and I replied to it... Reposting my answer...

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. (Reference *FREE* is
because I see Steve offered to help you for money, something no one in this
newsgroup would ask you for.)

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

"TGC" wrote in message
...
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
tables set-up properly so you can move on to the fun part... CODING! grin


Any brilliant suggestions?



--
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" wrote in message
...
Hi Gina -

I responded awhile ago, but it looks like my response never posted. I
apologize if this is a repeat.

It looks like you might understand what I am trying to accomplish with
this
database.

Essentially, I have a bunch of prospective clients that I want to track.
Each client is characterized by the company name. Each company is pretty
much built one of three ways. They are either one company with one
contact
who manages multiple properties OR one company with multiple contacts who
manage one or multiple properties each OR one company with multiple
contacts
who manage one property (least likely).

The company table is pretty straightforward.

The only tricky part of the company_contact table is the fact that I want
the company phone entered into the company table to be an option within
the
company_phone field in the company_contact table. That way when I run a
query on a contact, the company phone will appear with any additional
numbers
I enter.

The only tricky part of the property table is the fact that I want all the
contacts' first and last name within that company to appear in a field
within
the property table so I can assign contacts to the different properties.

Steve brought up a good point in that I probably should also have a
correspondence table so that I can track date, message, collateral
provided,
etc. that would be related to the contact.

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

- Email prospective clients directly in Access and track that within the
correspondence table
- Upload documents so I can easily attach those to emails within Access
- Create a current client database
- Ability to transfer info from prospective client database to current
client database once a contract is signed
- Email current clients directly in Access and track messages in service
request log table

Where do I begin????

Thank you for your help!
"Gina Whipp" wrote:

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

"Fred" wrote in message
...
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.



.



.