Prospective Client Tracking Database

[John... Visio MVP]

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

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[TGC]

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]

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

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

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]

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]

Ellen,

You did NOT create the problem... that would be Steve who created the
problem. YOU are why we volunteers are here! So, on to your tables and
answers...

tblProperty
pPropertyID (PK)
pCompanyID (FK)
pAddress1
pAddress2
pCityID
pPostalCode
pPropertyTypeID (FK)
pInReceivership (Yes/No)
pPrice
pPastTGCBid
pBidSchedule
pAreaSchedule
pNotes (Text, 255)

tblPropertyVendors
pvPropertyID (FK)
pvVendorID (FK)

tblPropertyContact
pcPropertyID (FK)
pcFirstName
pcLastName
pcDirectPhoneNumber
pcCellPhone
pcFaxNumber
pceMailAddress
pcContactType (Yes/No - to determine if Main Contact or not)
pcStatus

tblVendors
pvVendorID (PK-Autonumber)
pvVendorName
pvVendorTypeID (FK-Interior, Landscaping, etc...)

tblPropertyTypes
ptPropertyTypeID (PK-Autonumber)
ptPropertyType (Office, Retail, Industiral, Hospitatlity)

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?

****Added a table for that

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.

****New table
tblBids
bBidID (PK-Autonumber)
bPropertyID (FK)
bDateIssued
bStaffID (FK)
bAmount

**** You might want to consider adding a tblStaff

tblBidDetails
bBidID (FK)
bAttachment (Text, 255) - Here you will store the path to the attachment as
these can quickly couse your database to bloat should be using OLE object.
And yes, you will be able to view your attachments even though you are just
storing the link.

tblPropertyBids
pbPropertyID (FK)
pbBidID (FK)

tblCompany
cCompanyID (PK-Autonumber)
cCompanyName
cMainPhoneNumber
cAddress1
cAddress2
cCityID
cPostalCode
cFaxumber
cWebsite
cClientTypeID (FK)
cPropertyTypeID (FK)
cInReceivership

tblClientTypes
ctClientTypeID (PK-Autonumber)
ctClientType (Current, Prospective, Hybrid, etc...)

tblCompanyContact
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.

****Does that mean the Bid Scedule is actually a date?

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.

****Yes it does so no worries there, it will be handled

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

tblActivityTypes
atActivityTypeID (PK-Autonumber)
atActivity

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?

**** That is okay and yes you can export from GOldmine and Import to Access
when you are ready. And when you are ready adding new tables will be easy
but that is why you take the time to make sure the tables are correct to
begin with. Think of it like a house. You can't make additions if the
foundation isn't done correctly!

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?

****Nope, not naive, no stupid questions only stupid answers! THAT is my
own preference. It insures I *never* use a Reserved Word as a filed name
AND after I am all done and two months from now I can go back and read my
code and know exactly which table that field cam from. If you prefer not to
use them no problem. However, read this list and make sure your field names
don't show up here...

http://allenbrowne.com/AppIssueBadWord.html

....Long list, I know, that's why I do it!

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

****You're welcome!

Ellen


--
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]

Lots of info to process....my brain hurts!

Won't be back at work until next Tuesday. Will review info and respond
then.

Thank you again! The help is invaluable.

 

[Gina Whipp]

Ellen,

Yes it is... and you're only at the beginning... See you next week!

--
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]

<<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.

So does everyone else and it's infinitely better than yours and completely
free of charge. Another swing and a miss, why *do* you bother?

 

[Fred]

As context for the above advice, Gina, Keith and John are highly respected
experts in these forums.

Steve often has right answers, but they are colored by trying to head
towards soliciting getting paid for advice (a violation of the rules) plus,
in this case, the advice is colored by looking for a chance to insult someone
who he has insulted before.

You (probably still) have a "real world" question to answer which will lead
to structure.....whether you are going to record a property as linked
primarily to the person who is managing it vs. to the company that they work
for. I've done in-practice implementations of DB's similar to yours many
times, and have wrestled with that same question. On the face of it, linkage
to the person might seem the answer...after all such is a more specific
information and of interest. However, in practice it usually doesn't work
out so well. Every time the company has a personnel change or reorganization
of coverage, your linkages become outdated/wrong. And those companies
usually don't routinely communicate such changes. And so keeping your DB
updated requires a large amount of chasing down information that you would
not otherwise have. In DB usage, "too much work in a non-essential area"
usually means "doesn't get done" . Linkage instead to the company eliminates
that particular problem.

And so linkage to the company is probably a better idea, which is what the
advice from the noted 3 highly respected individuals is based on.

 

[TGC]

Hi Gina -

I had a chance to digest all your helpful info...

I have rewritten all the tables I think I need based on the info you
provided me. I have included a few questions next to a few fields and have
included questions after some tables that apply to the table in general.

I also included some future stuff I want to address down the road. I know I
am a far way from most, if not all, of these things, but I wanted to lay them
out there now in case we needed to set up additional things within the
foundation and tables in order to make them work.

Thanks again so much for your help! Here it goes...

tblCompany
cCompanyID (pk) - autonumber
cCompanyName
cMainPhone
cAddress1
cAddress2
cCityID
cPostalCode
cFaxNumber
cWebsite
cClientTypeID (fk)
cPropertyTypeID (fk)

tblClientTypes
ctClientTypeID (pk) – autonumber
ctClientType – lookup (Current, Prospect, Hybrid)

tblCompanyContact
ccCompanyContactID (pk) – autonumber
ccCompanyID (fk)
ccFirstName
ccLastName
ccClientType (fk)
ccContactType – yes/no (Main contact or not?)
ccDirectPhone
ccCellPhone
ccEmail
ccFaxNumber
ccAddress1
ccAddress2
ccCityID -
ccPostalCode
ccBidSchedule – calendar – This will be a date. Does this work?

QUESTIONS
--Query will determine if the contact is the main contact. If yes, the
company main phone will show along with direct and cell phone options. If
no, only the direct and cell phone options will appear. Same thing for
contact address. Query will determine if the contact is at the main company
address. If yes, the company address will populate. If not, the address
fields will remain blank to be filled in. Is that correct?

--Do I need to create a CityID table? I have CityID a few places in
different tables but haven’t created a CityID table yet.

--Is there a way to set alerts associated with the ccBidSchedule? So our
staff is reminded (via email?) of an upcoming bid opening?

tblActivities
aDate - calendar
aActivityTypeID (fk)
aStaffID (fk)
aCollateralTypeID (fk)
aNotes – text
aDone – yes/no

tblActivityTypes
atActivityTypeID (pk) – autonumber
atActivityType – lookup (Phone Conversation, Phone VM, Meeting, Email, Mail,
Fax)

QUESTIONS
--Is there a way to set an activities schedule reminder to alert staff to
follow up with a prospect?

tblCollateralTypes
ctCollateralTypeID(pk) – autonumber
ctCollateralType – lookup (Newsletter, Sustainable Brochure, Stick Brochure,
Complete Marketing Package)

tblProperty
pPropertyID (pk) – autonumber
pCompanyID (fk)
pAddress1
pAddress2
pCityID
pPostalCode
pPropertyTypeID (fk)
pReceivership – yes/no
pExistingPrice
pCurrentVendorID (fk)
pPastTGCBid
pInterior – yes/no
pBidSchedule – calendar
pAreaScheduleID (fk)
pNotes – text (255)

QUESTIONS
--I am a little confused. I want to make sure each property has a contact
assigned to it, but I am not sure if you put a contact foreign key in the
tblProperty or you put a property foreign key in the tblCompanyContact or
tblPropertyContact.

--Did you create the tblPropertyContact in case a contact hasn’t already
been entered, but you have a new property and you need to assign a contact to
it? I am a little confused why I have tblCompanyContact and
tblPropertyContact. Fred mentioned that it is important to link a property
to a company rather than link it to a contact, which is very helpful once the
property contract is won. However, this is a prospective client database so
I am not so sure that is the best way to link them. Thoughts?

--If possible, I would like to work with the ccBidSchedule. Similar to what
you did with the main phone number situation with the tblCompany and
tblCompanyContact…I should be able to choose if the pBidScheule is the same
as the ccBidSchedule. If yes, it will populate in this area. If no, I will
have the option to choose a new date.

--Same question as before, is there a way to set alerts associated with the
pBidSchedule? So our staff is reminded (via email?) of an upcoming bid
opening?

tblCurrentVendors
cvCurrentVendorID (pk) – autonumber
cvCurrentVendorName

tblPropertyVendors
pvPropertyID (fk)
pvCurrentVendorID (fk)

QUESTIONS
--Why is this table (tblPropertyVendors) necessary. All I am looking for
with the tblCurrentVendors is who the property currently uses for their
commercial landscaping. Who currently holds that contract. I tweaked the
previous tblVendors you had to make the tblCurrentVendors because I am not
interested in whether they do interior or exterior work, I just want to know
who currently services that property. So, I ended up also adding back the
pInterior field into the tblProperty because I just want to know (yes or no)
whether that property has interior needs. All companies have exterior needs,
that is why they are in the database to begin with, but some may also have
interior and this is where I would answer that question. Does that work?

tblAreaSchedule
asAreaScheduleID (pk) – autonumber
asAreaSchedule – lookup (Area 1, Area 2, Area 3, Area 4, Area 5)

tbl PropertyContact
pcPropertyContactID (pk) – Don’t you need this???? It wasn't in your last
post
pcPropertyID (fk)
pcFirstName
pcLastName
pcClientType (fk) – Don’t you need this??? Just like in tblCompanyContact?
pcContactType – yes/no (Main contact or not?)
pcDirectPhone
pcCellPhone
pcEmail
pcFaxNumber
pcBidSchedule – calendar – Don’t you need this?
pcStatus – What is this for???

QUESTIONS
--I sort of asked this in an above question, but can you explain why I need
a tblCompanyContact and a tblPropertyContact. I am sure there is a very
logical answer but I am just not getting it yet.

--Will this table be able to do the same main phone and main address query
like the tblCompanyContact?

--Same question as before, if possible, I would like to work with the
ccBidSchedule. Similar to what you did with the main phone number situation
with the tblCompany and tblCompanyContact…I should be able to choose if the
pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in
this area. If no, I will have the option to choose a new date.

--Same question as before, is there a way to set alerts associated with the
pcBidSchedule (if that is even a necessary field in this table)? So our
staff is reminded (via email?) of an upcoming bid opening?

tblPropertyTypes
ptPropertyTypeID (pk) – autonumber
ptPropertyType – lookup (Office, Retail, Industrial, Hospitality, Multiple)

tblStaff
sStaffID (pk)
sStaffName – lookup (insert all staff names)

tblBids
bBidID (pk) – autonumber
bPropertyID (fk)
bDateIssued
bStaffID (fk)
bAmount
bBidStatusID (fk)

tblBidStatus
bsBidStatusID (pk) – autonumber
bsBidStatusType – lookup (Pending, Won, Lost)

tblBidDetails
bBidID (fk)
bAttachment – text (255) – OLE object

tblPropertyBids
pbPropertyID (fk)
pbBidID (fk)

QUESTIONS
--Why is tblPropertyBids necessary?

--Do you need to put a foreign key for the BidID into the tblProperty?
Again, this is an example of when I am not sure which table gets the foreign
key.

FUTURE STUFF
--Email prospective clients directly in Access
--Track email correspondence automatically as an activity in tblActivities
--Send collateral attachments directly in email within Access
--If necessary, link emails sent through Boomerang (3rd party email service)
with tblActivities
--Link contact info with Outlook
--Upload email templates to be used for Access email
--Populate contact info into email templates
--Download company, contact, and property info into MS Word bids and
contract templates
--Download company and contact street address info for mail merge
--Set an alert to notify sales staff via email when a new prospect is added
-- Potentially build out search option for pPastTGCBid within tblProperty so
once bids come back around, I can search past bids within database and have
those past bid amounts at my fingertips – Very much a future project. Not
something needed now.
--Build current client side of database – Very much a future project.

Thanks again!

Ellen

 

[Gina Whipp]

Ellen,

Answers in-line... Look for the 3 asterisks! (Hope I got everything)

--
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 -

I had a chance to digest all your helpful info...

I have rewritten all the tables I think I need based on the info you
provided me. I have included a few questions next to a few fields and have
included questions after some tables that apply to the table in general.

I also included some future stuff I want to address down the road. I know I
am a far way from most, if not all, of these things, but I wanted to lay
them
out there now in case we needed to set up additional things within the
foundation and tables in order to make them work.

Thanks again so much for your help! Here it goes...

tblCompany
cCompanyID (pk) - autonumber
cCompanyName
cMainPhone
cAddress1
cAddress2
cCityID
****cStateID (Oops forogt that field)
cPostalCode
cFaxNumber
cWebsite
cClientTypeID (fk)
cPropertyTypeID (fk)

tblClientTypes
ctClientTypeID (pk) - autonumber
ctClientType - lookup (Current, Prospect, Hybrid)

tblCompanyContact
ccCompanyContactID (pk) - autonumber
ccCompanyID (fk)
ccFirstName
ccLastName
ccClientType (fk)
ccContactType - yes/no (Main contact or not? ***Yep that is what that is
for!)
ccDirectPhone
ccCellPhone
ccEmail
ccFaxNumber
ccAddress1
ccAddress2
ccCityID
****ccStateID (Oops forogt that field)
ccPostalCode
ccBidSchedule - calendar - This will be a date. Does this work? ***If this
a many field which means if there are going to be many dates to on COmpany
Contact then this will need to broken out... but will wait for you answer.

QUESTIONS
--Query will determine if the contact is the main contact. If yes, the
company main phone will show along with direct and cell phone options. If
no, only the direct and cell phone options will appear. Same thing for
contact address. Query will determine if the contact is at the main company
address. If yes, the company address will populate. If not, the address
fields will remain blank to be filled in. Is that correct?

***Yes that is correct.

--Do I need to create a CityID table? I have CityID a few places in
different tables but haven't created a CityID table yet.

***I use one because you'd be surprised how many typos people can make when
typing their own city name. So yes, you need a City table should you decide
to go that route OR you could change it to CityName and let people type.

--Is there a way to set alerts associated with the ccBidSchedule? So our
staff is reminded (via email?) of an upcoming bid opening?

***Yes and here's a link to get you started when you are ready...

http://www.rogersaccesslibrary.com/...?TID=141&SID=a1a3e813a47161298c3eaac8za637888

tblActivities
aDate - calendar
aActivityTypeID (fk)
aStaffID (fk)
aCollateralTypeID (fk)
aNotes - text
aDone - yes/no

tblActivityTypes
atActivityTypeID (pk) - autonumber
atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email, Mail,
Fax)

QUESTIONS
--Is there a way to set an activities schedule reminder to alert staff to
follow up with a prospect?

***See above link

tblCollateralTypes
ctCollateralTypeID(pk) - autonumber
ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick Brochure,
Complete Marketing Package)

***Please give me a little background on how you plan to use the above
table.

tblProperty
pPropertyID (pk) - autonumber
pCompanyID (fk)
pAddress1
pAddress2
pCityID
****pStateID (Oops forogt that field)
pPostalCode
pPropertyTypeID (fk)
pReceivership - yes/no
pExistingPrice
pCurrentVendorID (fk)
pPastTGCBid
pInterior - yes/no
pBidSchedule - calendar
pAreaScheduleID (fk)
pNotes - text (255)

QUESTIONS
--I am a little confused. I want to make sure each property has a contact
assigned to it, but I am not sure if you put a contact foreign key in the
tblProperty or you put a property foreign key in the tblCompanyContact or
tblPropertyContact.

***That is what the tblPropertyContact is for

--Did you create the tblPropertyContact in case a contact hasn't already
been entered, but you have a new property and you need to assign a contact
to
it? I am a little confused why I have tblCompanyContact and
tblPropertyContact. Fred mentioned that it is important to link a property
to a company rather than link it to a contact, which is very helpful once
the
property contract is won. However, this is a prospective client database so
I am not so sure that is the best way to link them. Thoughts?

***No, the Property Contact should be in tblProperty, there is a seperate
table for them. Fred is correct (and it is the only way I do it) because
Contacts (people) come and go but Companies usually hang around. If the
person quits you still want to know what Company to deal with, understand
better now?

--If possible, I would like to work with the ccBidSchedule. Similar to what
you did with the main phone number situation with the tblCompany and
tblCompanyContact.I should be able to choose if the pBidScheule is the same
as the ccBidSchedule. If yes, it will populate in this area. If no, I will
have the option to choose a new date.

***Once you answer my question above regarding BidSchedule we will work on
table ideas.

--Same question as before, is there a way to set alerts associated with the
pBidSchedule? So our staff is reminded (via email?) of an upcoming bid
opening?

***See link above...

tblVendors
vVendorID (pk) - autonumber
vVendorName

tblPropertyVendors
pvPropertyID (fk)
pvVendorID (fk)

QUESTIONS
--Why is this table (tblPropertyVendors) necessary. All I am looking for
with the tblCurrentVendors is who the property currently uses for their
commercial landscaping. Who currently holds that contract. I tweaked the
previous tblVendors you had to make the tblCurrentVendors because I am not
interested in whether they do interior or exterior work, I just want to know
who currently services that property.

***No, you will be able to pull that information via query. Because a
Vendor is Vendor whether Current or not.

So, I ended up also adding back the
pInterior field into the tblProperty because I just want to know (yes or no)
whether that property has interior needs. All companies have exterior
needs,
that is why they are in the database to begin with, but some may also have
interior and this is where I would answer that question. Does that work?

***Yes putting that field back in is okay BUT think about tomorrow... Are
you SURE you are never going to want to pull seperate lists for Exterior
Vendors or Interior Vendors? If the answer is *maybe* then put it back in
the Vendor table but you can leave it in the Property table, though I would
remove it and leave the tblPropertyVendors.


tblAreaSchedule
asAreaScheduleID (pk) - autonumber
asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5)

***Please elaborate what the above table will be used for?

tbl PropertyContact
pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last
post

***Optional, not *every* table needs a Primary Key.

pcPropertyID (fk)
pcFirstName
pcLastName
pcClientType (fk) - Don't you need this??? Just like in tblCompanyContact?

***Yes, it is actually to store Property Type but I like all my PK's and
FK's to share the same name so I can know by glancing which table it is
linked to.

pcContactType - yes/no (Main contact or not?)
pcDirectPhone
pcCellPhone
pcEmail
pcFaxNumber
pcBidSchedule - calendar - Don't you need this?

***I am trying to understand Bid Schedule, see above

pcStatus - What is this for???

***I thought you wanted to know if the Contact is still active or not, if
not, can be removed.

QUESTIONS
--I sort of asked this in an above question, but can you explain why I need
a tblCompanyContact and a tblPropertyContact. I am sure there is a very
logical answer but I am just not getting it yet.

***Company Contact store just that and the same for Property Contacts. Each
has their own contacts. Perhaps I don't understand your question?

--Will this table be able to do the same main phone and main address query
like the tblCompanyContact?

***Yes

--Same question as before, if possible, I would like to work with the
ccBidSchedule. Similar to what you did with the main phone number situation
with the tblCompany and tblCompanyContact.I should be able to choose if the
pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in
this area. If no, I will have the option to choose a new date.

***See above

--Same question as before, is there a way to set alerts associated with the
pcBidSchedule (if that is even a necessary field in this table)? So our
staff is reminded (via email?) of an upcoming bid opening?

**See above

tblPropertyTypes
ptPropertyTypeID (pk) - autonumber
ptPropertyType - lookup (Office, Retail, Industrial, Hospitality, Multiple)

tblStaff
sStaffID (pk)
sStaffName - lookup (insert all staff names)

tblBids
bBidID (pk) - autonumber
bPropertyID (fk)
bDateIssued
bStaffID (fk)
bAmount
bBidStatusID (fk)

tblBidStatus
bsBidStatusID (pk) - autonumber
bsBidStatusType - lookup (Pending, Won, Lost)

tblBidDetails
bBidID (fk)
bAttachment - text (255)

***No OLE Object data type, you store a path to the file which can be
opened. OLE objects quickly fill up your database making it grow and you
don't want to hit the 2 gig limit that fast.

tblPropertyBids
pbPropertyID (fk)
pbBidID (fk)

QUESTIONS
--Why is tblPropertyBids necessary?

**Can't a Property have more then one bid?

--Do you need to put a foreign key for the BidID into the tblProperty?
Again, this is an example of when I am not sure which table gets the foreign
key.

***No, it is linked by PropertyID no need to link by anything else.

FUTURE STUFF
--Email prospective clients directly in Access ***Can be done

--Track email correspondence automatically as an activity in tblActivities
***

--Send collateral attachments directly in email within Access ***Can be done
as long in predetermined path OR will have to have a Browse for folder
option

--If necessary, link emails sent through Boomerang (3rd party email service)
with tblActivities ***Don't know, never used Boomerang

--Link contact info with Outlook ***While I have not done it, it can be
done

--Upload email templates to be used for Access email **Need more
information as to what you mean by templates
--Populate contact info into email templates

--Download company, contact, and property info into MS Word bids and
contract templates ***Have a look at...
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

--Download company and contact street address info for mail merge ***See
above

--Set an alert to notify sales staff via email when a new prospect is added
**Yes can be done

-- Potentially build out search option for pPastTGCBid within tblProperty so
once bids come back around, I can search past bids within database and have
those past bid amounts at my fingertips - Very much a future project. Not
something needed now. ***Yes can be done

--Build current client side of database - Very much a future project. **Yes
can be done. Would actually import in table and create field for Client or
Prospect, similar to a Yes/No field. That way they are all in the same
place and you can filter if you only want to see one or the other.

Thanks again!

Ellen



<CLIPPED>

 

[TGC]

Awesome! Thanks! I responded to a few things below. Put my answers in CAPS
so you could see them. Sorry, I hate CAPS, but probably best way to
differentiate on this string...

Thanks again Gina!

Ellen,

Answers in-line... Look for the 3 asterisks! (Hope I got everything)

--
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 -

I had a chance to digest all your helpful info...

I have rewritten all the tables I think I need based on the info you
provided me. I have included a few questions next to a few fields and have
included questions after some tables that apply to the table in general.

I also included some future stuff I want to address down the road. I know I
am a far way from most, if not all, of these things, but I wanted to lay
them
out there now in case we needed to set up additional things within the
foundation and tables in order to make them work.

Thanks again so much for your help! Here it goes...

tblCompany
cCompanyID (pk) - autonumber
cCompanyName
cMainPhone
cAddress1
cAddress2
cCityID
****cStateID (Oops forogt that field) - DUH, THANKS!
cPostalCode
cFaxNumber
cWebsite
cClientTypeID (fk)
cPropertyTypeID (fk)

tblClientTypes
ctClientTypeID (pk) - autonumber
ctClientType - lookup (Current, Prospect, Hybrid)

tblCompanyContact
ccCompanyContactID (pk) - autonumber
ccCompanyID (fk)
ccFirstName
ccLastName
ccClientType (fk)
ccContactType - yes/no (Main contact or not? ***Yep that is what that is
for!)
ccDirectPhone
ccCellPhone
ccEmail
ccFaxNumber
ccAddress1
ccAddress2
ccCityID
****ccStateID (Oops forogt that field) - SAME. THANKS!
ccPostalCode
ccBidSchedule - calendar - This will be a date. Does this work? ***If this
a many field which means if there are going to be many dates to on COmpany
Contact then this will need to broken out... but will wait for you answer.

THIS IS HOW I WANT TO USE THE BID SCHEDULES. EACH PROPERTY IS UNDER
CONTRACT FOR THEIR LANDSCAPING. AND OBVIOUSLY THOSE CONTRACTS EXPIRE SO
COMPANIES GO TO BID. SOMETIMES A COMPANY CONTACT BIDS ALL THEIR PROPERTIES
AT THE SAME TIME OR SOMETIMES EACH PROPERTY HAS A DIFFERENT BID DATE. I WANT
TO BE ABLE TO TRACK THAT AND BE ABLE TO SET ALERTS TO REMIND US THAT THOSE
DATES ARE APPROACHING SO WE REMEMBER TO REACH OUT TO THEM VIA THE DIFFERENT
ACTIVITIES OR SUBMIT A BID AT THE RIGHT TIME. SO, I WANT TO HAVE A BID
SCHEDULE FIELD (WHICH WILL SIMPLY BE A DATE) FOR THE COMPANY CONTACTS AND FOR
EACH PROPERTY. I WANT TO HAVE THE OPTION ON THE PROPERTY TO CHOOSE THE
COMPANY CONTACT BID SCHEDULE DATE OR ENTER A NEW ONE IF THEY ARE DIFFERENT.
SO, ESSENTIALLY THERE IS JUST ONE DATE THAT WILL SHOW UP IN THE BID SCHEDULE
FIELDS. THEY MAY BE DIFFERENT DATES, BUT THEY WILL BE SINGULAR DATES ALWAYS.
DOES THAT HELP?

QUESTIONS
--Query will determine if the contact is the main contact. If yes, the
company main phone will show along with direct and cell phone options. If
no, only the direct and cell phone options will appear. Same thing for
contact address. Query will determine if the contact is at the main company
address. If yes, the company address will populate. If not, the address
fields will remain blank to be filled in. Is that correct?

***Yes that is correct. COOL.

--Do I need to create a CityID table? I have CityID a few places in
different tables but haven't created a CityID table yet.

***I use one because you'd be surprised how many typos people can make when
typing their own city name. So yes, you need a City table should you decide
to go that route OR you could change it to CityName and let people type. MAKES SENSE

--Is there a way to set alerts associated with the ccBidSchedule? So our
staff is reminded (via email?) of an upcoming bid opening?

***Yes and here's a link to get you started when you are ready... THANK YOU!

http://www.rogersaccesslibrary.com/...?TID=141&SID=a1a3e813a47161298c3eaac8za637888

tblActivities
aDate - calendar
aActivityTypeID (fk)
aStaffID (fk)
aCollateralTypeID (fk)
aNotes - text
aDone - yes/no

tblActivityTypes
atActivityTypeID (pk) - autonumber
atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email, Mail,
Fax)

QUESTIONS
--Is there a way to set an activities schedule reminder to alert staff to
follow up with a prospect?

***See above link. THANK YOU

tblCollateralTypes
ctCollateralTypeID(pk) - autonumber
ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick Brochure,
Complete Marketing Package)

***Please give me a little background on how you plan to use the above
table. THIS TABLE MAY NOT BE NECESSARY. IT MAY JUST NEED TO BE A LOOKUP FIELD WITH THE DIFFERENT COLLATERAL TYPES AS THE LOOKUP OPTIONS WITHIN TBLACTIVITIES. I JUST WANT TO BE ABLE TO ASSIGN WHAT TYPE OF COLLATERAL WAS SENT WITH EACH ACTIVITY. SHOULD I KEEP IT AS ITS OWN TABLE OR JUST MAKE IT A LOOKUP FIELD WITHIN THE ACTIVITIES TABLE?

tblProperty
pPropertyID (pk) - autonumber
pCompanyID (fk)
pAddress1
pAddress2
pCityID
****pStateID (Oops forogt that field). THANKS!
pPostalCode
pPropertyTypeID (fk)
pReceivership - yes/no
pExistingPrice
pCurrentVendorID (fk)
pPastTGCBid
pInterior - yes/no
pBidSchedule - calendar
pAreaScheduleID (fk)
pNotes - text (255)

QUESTIONS
--I am a little confused. I want to make sure each property has a contact
assigned to it, but I am not sure if you put a contact foreign key in the
tblProperty or you put a property foreign key in the tblCompanyContact or
tblPropertyContact.

***That is what the tblPropertyContact is for. WILL RESPOND TO THIS BELOW BECAUSE IT COMES UP AGAIN

--Did you create the tblPropertyContact in case a contact hasn't already
been entered, but you have a new property and you need to assign a contact
to
it? I am a little confused why I have tblCompanyContact and
tblPropertyContact. Fred mentioned that it is important to link a property
to a company rather than link it to a contact, which is very helpful once
the
property contract is won. However, this is a prospective client database so
I am not so sure that is the best way to link them. Thoughts?

***No, the Property Contact should be in tblProperty, there is a seperate
table for them. Fred is correct (and it is the only way I do it) because
Contacts (people) come and go but Companies usually hang around. If the
person quits you still want to know what Company to deal with, understand
better now? WILL ANSWER BELOW BECAUSE IT ALL RELATES TO THE QUESTION ABOUT HAVING THE 2 DIFFERENT CONTACT TABLES

--If possible, I would like to work with the ccBidSchedule. Similar to what
you did with the main phone number situation with the tblCompany and
tblCompanyContact.I should be able to choose if the pBidScheule is the same
as the ccBidSchedule. If yes, it will populate in this area. If no, I will
have the option to choose a new date.

***Once you answer my question above regarding BidSchedule we will work on
table ideas. ANSWERED ABOVE

--Same question as before, is there a way to set alerts associated with the
pBidSchedule? So our staff is reminded (via email?) of an upcoming bid
opening?

***See link above...THANK YOU

tblVendors
vVendorID (pk) - autonumber
vVendorName

tblPropertyVendors
pvPropertyID (fk)
pvVendorID (fk)

QUESTIONS
--Why is this table (tblPropertyVendors) necessary. All I am looking for
with the tblCurrentVendors is who the property currently uses for their
commercial landscaping. Who currently holds that contract. I tweaked the
previous tblVendors you had to make the tblCurrentVendors because I am not
interested in whether they do interior or exterior work, I just want to know
who currently services that property.

***No, you will be able to pull that information via query. Because a
Vendor is Vendor whether Current or not. MAKES SENSE. ESSENTIALLY, THE VENDORS THAT WILL BE LISTED ARE OUR COMPETITORS.

So, I ended up also adding back the
pInterior field into the tblProperty because I just want to know (yes or no)
whether that property has interior needs. All companies have exterior
needs,
that is why they are in the database to begin with, but some may also have
interior and this is where I would answer that question. Does that work?

***Yes putting that field back in is okay BUT think about tomorrow... Are
you SURE you are never going to want to pull seperate lists for Exterior
Vendors or Interior Vendors? If the answer is *maybe* then put it back in
the Vendor table but you can leave it in the Property table, though I would
remove it and leave the tblPropertyVendors. I WON'T EVER WANT TO PULL DIFFERENT LISTS FOR EXTERIOR VENDORS VERSUS INTERIOR VENDORS. NOT REALLY WHAT I AM TRYING TO TRACK. I GET WHERE YOU WERE GOING THOUGH. JUST DON'T THINK IT FITS WHAT I AM TRYING TO DO.


tblAreaSchedule
asAreaScheduleID (pk) - autonumber
asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5)

***Please elaborate what the above table will be used for? AGAIN, THIS MAY BE ONE WHERE I CAN JUST HAVE THE AREA SCHEDULE BE LISTED AS A LOOKUP FIELD WITHIN TBLPROPERTIES. THE AREA SCHEDULE IS A WAY FOR ME TO ASSIGN THE PROPERTY TO AN AREA BECAUSE WE HAVE AREA SUPERVISORS FOR EACH PROPERTY. DOES THAT MAKE SENSE? SHOULD I LEAVE AS A SEPARATE TABLE OR JUST HAVE AS A LOOKUP FIELD?

tbl PropertyContact
pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last
post

***Optional, not *every* table needs a Primary Key. OK

pcPropertyID (fk)
pcFirstName
pcLastName
pcClientType (fk) - Don't you need this??? Just like in tblCompanyContact?

***Yes, it is actually to store Property Type but I like all my PK's and
FK's to share the same name so I can know by glancing which table it is
linked to.

pcContactType - yes/no (Main contact or not?)
pcDirectPhone
pcCellPhone
pcEmail
pcFaxNumber
pcBidSchedule - calendar - Don't you need this?

***I am trying to understand Bid Schedule, see above

pcStatus - What is this for???

***I thought you wanted to know if the Contact is still active or not, if
not, can be removed. THAT IS WHAT THE PCCLIENTTYPE IS. THAT WILL SHOW WHETHER THEY ARE CURRENT, PROSPECT, OR HYBRID. I DON'T THINK I ALSO NEED PCSTATUS. DOES THAT MAKE SENSE?

QUESTIONS
--I sort of asked this in an above question, but can you explain why I need
a tblCompanyContact and a tblPropertyContact. I am sure there is a very
logical answer but I am just not getting it yet.

***Company Contact store just that and the same for Property Contacts. Each
has their own contacts. Perhaps I don't understand your question? I PROBABLY NEED TO DO A BETTER JOB OF EXPLANING. LET ME TRY TO EXPLAIN MORE CLEARLY HOW THE BUSINESS MODEL WORKS AGAIN. OUR PROSPECTIVE CLIENTS ARE COMPANIES. EACH ONE OF THOSE COMPANIES HAS ONE OR MORE CONTACTS WE DEAL WITH. AND EACH OF THOSE CONTACTS MANAGES ONE OR MORE PROPERTIES THAT WE SERVICE. VERY RARELY WILL ONE COMPANY HAVE MORE THAN ONE CONTACT MANAGING A SINGLE PROPERTY. THE COMPANY CONTACTS AND THE PROPERTY CONTACTS ARE THE SAME THING. THAT IS WHY I WAS CONFUSED WHY WE HAD DIFFERENT TABLES FOR EACH SINCE THEY ARE THE SAME. WHEN I IDENTIFY A NEW PROSPECT, I AM LOOKING FOR THE COMPANY INFO, THE CONTACT INFO AND THE PROPERTY INFO. I ALSO WANT TO MAKE SURE I IDENTIFY WHICH CONTACT MANAGES WHICH PROPERTY. I AM NOT SURE IF THIS IS HELPING??? EACH PROPERTY IS UNDER THE COMPANY UMBRELLA, BUT IT IS ALSO LINKED TO A CONTACT THAT IS ALSO UNDER THE COMPANY UMBRELLA.

--Will this table be able to do the same main phone and main address query
like the tblCompanyContact?

***Yes. GREAT.

--Same question as before, if possible, I would like to work with the
ccBidSchedule. Similar to what you did with the main phone number situation
with the tblCompany and tblCompanyContact.I should be able to choose if the
pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in
this area. If no, I will have the option to choose a new date.

***See above. ANSWERED ABOVE.

--Same question as before, is there a way to set alerts associated with the
pcBidSchedule (if that is even a necessary field in this table)? So our
staff is reminded (via email?) of an upcoming bid opening?

**See above. GREAT.

tblPropertyTypes
ptPropertyTypeID (pk) - autonumber
ptPropertyType - lookup (Office, Retail, Industrial, Hospitality, Multiple)

tblStaff
sStaffID (pk)
sStaffName - lookup (insert all staff names)

tblBids
bBidID (pk) - autonumber
bPropertyID (fk)
bDateIssued
bStaffID (fk)
bAmount
bBidStatusID (fk)

tblBidStatus
bsBidStatusID (pk) - autonumber
bsBidStatusType - lookup (Pending, Won, Lost)

tblBidDetails
bBidID (fk)
bAttachment - text (255)

***No OLE Object data type, you store a path to the file which can be
opened. OLE objects quickly fill up your database making it grow and you
don't want to hit the 2 gig limit that fast. OK, MAKES SENSE.

tblPropertyBids
pbPropertyID (fk)
pbBidID (fk)

QUESTIONS
--Why is tblPropertyBids necessary?

**Can't a Property have more then one bid? NO. THEY WILL HAVE MORE THAN
ONE BID WHEN YOU CONSIDER ALL THE DIFFERENT VENDORS, BUT I ONLY CARE ABOUT
THE BIDS WE ISSUE FOR EACH PROPERTY. SO, NO, EACH PROPERTY WILL ONLY HAVE
ONE TGC BID. DOES THAT HELP?

--Do you need to put a foreign key for the BidID into the tblProperty?
Again, this is an example of when I am not sure which table gets the foreign
key.

***No, it is linked by PropertyID no need to link by anything else. OK.

FUTURE STUFF
--Email prospective clients directly in Access ***Can be done. GREAT

--Track email correspondence automatically as an activity in tblActivities
*** I AM ASSUMING THIS CAN BE DONE AS WELL. SO, GREAT!

--Send collateral attachments directly in email within Access ***Can be done
as long in predetermined path OR will have to have a Browse for folder
option. OK, GREAT

--If necessary, link emails sent through Boomerang (3rd party email service)
with tblActivities ***Don't know, never used Boomerang. I DON'T KNOW TOO
MUCH ABOUT IT EITHER...WILL HAVE TO LEARN MORE AND THEN TACKLE LATER.

--Link contact info with Outlook ***While I have not done it, it can be
done. OK.

--Upload email templates to be used for Access email **Need more
information as to what you mean by templates
--Populate contact info into email templates. I WOULD WANT TO WRITE A
STANDARD EMAIL RESPONSE WITH GENERIC TEXT THAT COULD BE USED FOR ALL CLIENTS
AND JUST BE ABLE TO PERSONALIZE IT WITH THE COMPANY, CONTACT, AND PROPERTY
INFO FROM THE DATABASE. I JUST WANT TO BE ABLE TO STORE CANNED RESPONSES.

--Download company, contact, and property info into MS Word bids and
contract templates ***Have a look at...
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html. THANKS!

--Download company and contact street address info for mail merge ***See
above. GOT IT.

--Set an alert to notify sales staff via email when a new prospect is added
**Yes can be done. GREAT.

-- Potentially build out search option for pPastTGCBid within tblProperty so
once bids come back around, I can search past bids within database and have
those past bid amounts at my fingertips - Very much a future project. Not
something needed now. ***Yes can be done. GREAT!

--Build current client side of database - Very much a future project. **Yes
can be done. Would actually import in table and create field for Client or
Prospect, similar to a Yes/No field. That way they are all in the same
place and you can filter if you only want to see one or the other. OK, COOL.

 

[Gina Whipp]

Ellen,

There will be a slight delay in answering... want to eat some dinner (which
I do have to cook) but I'll be back...

--
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
Awesome! Thanks! I responded to a few things below. Put my answers in
CAPS
so you could see them. Sorry, I hate CAPS, but probably best way to
differentiate on this string...

Thanks again Gina!

Ellen,

Answers in-line... Look for the 3 asterisks! (Hope I got everything)

--
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 -

I had a chance to digest all your helpful info...

I have rewritten all the tables I think I need based on the info you
provided me. I have included a few questions next to a few fields and
have
included questions after some tables that apply to the table in general.

I also included some future stuff I want to address down the road. I know
I
am a far way from most, if not all, of these things, but I wanted to lay
them
out there now in case we needed to set up additional things within the
foundation and tables in order to make them work.

Thanks again so much for your help! Here it goes...

tblCompany
cCompanyID (pk) - autonumber
cCompanyName
cMainPhone
cAddress1
cAddress2
cCityID
****cStateID (Oops forogt that field) - DUH, THANKS!
cPostalCode
cFaxNumber
cWebsite
cClientTypeID (fk)
cPropertyTypeID (fk)

tblClientTypes
ctClientTypeID (pk) - autonumber
ctClientType - lookup (Current, Prospect, Hybrid)

tblCompanyContact
ccCompanyContactID (pk) - autonumber
ccCompanyID (fk)
ccFirstName
ccLastName
ccClientType (fk)
ccContactType - yes/no (Main contact or not? ***Yep that is what that is
for!)
ccDirectPhone
ccCellPhone
ccEmail
ccFaxNumber
ccAddress1
ccAddress2
ccCityID
****ccStateID (Oops forogt that field) - SAME. THANKS!
ccPostalCode
ccBidSchedule - calendar - This will be a date. Does this work? ***If
this
a many field which means if there are going to be many dates to on COmpany
Contact then this will need to broken out... but will wait for you answer.

THIS IS HOW I WANT TO USE THE BID SCHEDULES. EACH PROPERTY IS UNDER
CONTRACT FOR THEIR LANDSCAPING. AND OBVIOUSLY THOSE CONTRACTS EXPIRE SO
COMPANIES GO TO BID. SOMETIMES A COMPANY CONTACT BIDS ALL THEIR PROPERTIES
AT THE SAME TIME OR SOMETIMES EACH PROPERTY HAS A DIFFERENT BID DATE. I
WANT
TO BE ABLE TO TRACK THAT AND BE ABLE TO SET ALERTS TO REMIND US THAT THOSE
DATES ARE APPROACHING SO WE REMEMBER TO REACH OUT TO THEM VIA THE DIFFERENT
ACTIVITIES OR SUBMIT A BID AT THE RIGHT TIME. SO, I WANT TO HAVE A BID
SCHEDULE FIELD (WHICH WILL SIMPLY BE A DATE) FOR THE COMPANY CONTACTS AND
FOR
EACH PROPERTY. I WANT TO HAVE THE OPTION ON THE PROPERTY TO CHOOSE THE
COMPANY CONTACT BID SCHEDULE DATE OR ENTER A NEW ONE IF THEY ARE DIFFERENT.
SO, ESSENTIALLY THERE IS JUST ONE DATE THAT WILL SHOW UP IN THE BID SCHEDULE
FIELDS. THEY MAY BE DIFFERENT DATES, BUT THEY WILL BE SINGULAR DATES
ALWAYS.
DOES THAT HELP?

QUESTIONS
--Query will determine if the contact is the main contact. If yes, the
company main phone will show along with direct and cell phone options. If
no, only the direct and cell phone options will appear. Same thing for
contact address. Query will determine if the contact is at the main
company
address. If yes, the company address will populate. If not, the address
fields will remain blank to be filled in. Is that correct?

***Yes that is correct. COOL.

--Do I need to create a CityID table? I have CityID a few places in
different tables but haven't created a CityID table yet.

***I use one because you'd be surprised how many typos people can make
when
typing their own city name. So yes, you need a City table should you
decide
to go that route OR you could change it to CityName and let people type.
MAKES SENSE

--Is there a way to set alerts associated with the ccBidSchedule? So our
staff is reminded (via email?) of an upcoming bid opening?

***Yes and here's a link to get you started when you are ready... THANK
YOU!

http://www.rogersaccesslibrary.com/...?TID=141&SID=a1a3e813a47161298c3eaac8za637888

tblActivities
aDate - calendar
aActivityTypeID (fk)
aStaffID (fk)
aCollateralTypeID (fk)
aNotes - text
aDone - yes/no

tblActivityTypes
atActivityTypeID (pk) - autonumber
atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email,
Mail,
Fax)

QUESTIONS
--Is there a way to set an activities schedule reminder to alert staff to
follow up with a prospect?

***See above link. THANK YOU

tblCollateralTypes
ctCollateralTypeID(pk) - autonumber
ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick
Brochure,
Complete Marketing Package)

***Please give me a little background on how you plan to use the above
table. THIS TABLE MAY NOT BE NECESSARY. IT MAY JUST NEED TO BE A LOOKUP
FIELD WITH THE DIFFERENT COLLATERAL TYPES AS THE LOOKUP OPTIONS WITHIN
TBLACTIVITIES. I JUST WANT TO BE ABLE TO ASSIGN WHAT TYPE OF COLLATERAL
WAS SENT WITH EACH ACTIVITY. SHOULD I KEEP IT AS ITS OWN TABLE OR JUST
MAKE IT A LOOKUP FIELD WITHIN THE ACTIVITIES TABLE?

tblProperty
pPropertyID (pk) - autonumber
pCompanyID (fk)
pAddress1
pAddress2
pCityID
****pStateID (Oops forogt that field). THANKS!
pPostalCode
pPropertyTypeID (fk)
pReceivership - yes/no
pExistingPrice
pCurrentVendorID (fk)
pPastTGCBid
pInterior - yes/no
pBidSchedule - calendar
pAreaScheduleID (fk)
pNotes - text (255)

QUESTIONS
--I am a little confused. I want to make sure each property has a contact
assigned to it, but I am not sure if you put a contact foreign key in the
tblProperty or you put a property foreign key in the tblCompanyContact or
tblPropertyContact.

***That is what the tblPropertyContact is for. WILL RESPOND TO THIS BELOW
BECAUSE IT COMES UP AGAIN

--Did you create the tblPropertyContact in case a contact hasn't already
been entered, but you have a new property and you need to assign a contact
to
it? I am a little confused why I have tblCompanyContact and
tblPropertyContact. Fred mentioned that it is important to link a
property
to a company rather than link it to a contact, which is very helpful once
the
property contract is won. However, this is a prospective client database
so
I am not so sure that is the best way to link them. Thoughts?

***No, the Property Contact should be in tblProperty, there is a seperate
table for them. Fred is correct (and it is the only way I do it) because
Contacts (people) come and go but Companies usually hang around. If the
person quits you still want to know what Company to deal with, understand
better now? WILL ANSWER BELOW BECAUSE IT ALL RELATES TO THE QUESTION
ABOUT HAVING THE 2 DIFFERENT CONTACT TABLES

--If possible, I would like to work with the ccBidSchedule. Similar to
what
you did with the main phone number situation with the tblCompany and
tblCompanyContact.I should be able to choose if the pBidScheule is the
same
as the ccBidSchedule. If yes, it will populate in this area. If no, I
will
have the option to choose a new date.

***Once you answer my question above regarding BidSchedule we will work on
table ideas. ANSWERED ABOVE

--Same question as before, is there a way to set alerts associated with
the
pBidSchedule? So our staff is reminded (via email?) of an upcoming bid
opening?

***See link above...THANK YOU

tblVendors
vVendorID (pk) - autonumber
vVendorName

tblPropertyVendors
pvPropertyID (fk)
pvVendorID (fk)

QUESTIONS
--Why is this table (tblPropertyVendors) necessary. All I am looking for
with the tblCurrentVendors is who the property currently uses for their
commercial landscaping. Who currently holds that contract. I tweaked the
previous tblVendors you had to make the tblCurrentVendors because I am not
interested in whether they do interior or exterior work, I just want to
know
who currently services that property.

***No, you will be able to pull that information via query. Because a
Vendor is Vendor whether Current or not. MAKES SENSE. ESSENTIALLY, THE
VENDORS THAT WILL BE LISTED ARE OUR COMPETITORS.

So, I ended up also adding back the
pInterior field into the tblProperty because I just want to know (yes or
no)
whether that property has interior needs. All companies have exterior
needs,
that is why they are in the database to begin with, but some may also have
interior and this is where I would answer that question. Does that work?

***Yes putting that field back in is okay BUT think about tomorrow... Are
you SURE you are never going to want to pull seperate lists for Exterior
Vendors or Interior Vendors? If the answer is *maybe* then put it back in
the Vendor table but you can leave it in the Property table, though I
would
remove it and leave the tblPropertyVendors. I WON'T EVER WANT TO PULL
DIFFERENT LISTS FOR EXTERIOR VENDORS VERSUS INTERIOR VENDORS. NOT REALLY
WHAT I AM TRYING TO TRACK. I GET WHERE YOU WERE GOING THOUGH. JUST DON'T
THINK IT FITS WHAT I AM TRYING TO DO.


tblAreaSchedule
asAreaScheduleID (pk) - autonumber
asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5)

***Please elaborate what the above table will be used for? AGAIN, THIS
MAY BE ONE WHERE I CAN JUST HAVE THE AREA SCHEDULE BE LISTED AS A LOOKUP
FIELD WITHIN TBLPROPERTIES. THE AREA SCHEDULE IS A WAY FOR ME TO ASSIGN
THE PROPERTY TO AN AREA BECAUSE WE HAVE AREA SUPERVISORS FOR EACH
PROPERTY. DOES THAT MAKE SENSE? SHOULD I LEAVE AS A SEPARATE TABLE OR
JUST HAVE AS A LOOKUP FIELD?

tbl PropertyContact
pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last
post

***Optional, not *every* table needs a Primary Key. OK

pcPropertyID (fk)
pcFirstName
pcLastName
pcClientType (fk) - Don't you need this??? Just like in
tblCompanyContact?

***Yes, it is actually to store Property Type but I like all my PK's and
FK's to share the same name so I can know by glancing which table it is
linked to.

pcContactType - yes/no (Main contact or not?)
pcDirectPhone
pcCellPhone
pcEmail
pcFaxNumber
pcBidSchedule - calendar - Don't you need this?

***I am trying to understand Bid Schedule, see above

pcStatus - What is this for???

***I thought you wanted to know if the Contact is still active or not, if
not, can be removed. THAT IS WHAT THE PCCLIENTTYPE IS. THAT WILL SHOW
WHETHER THEY ARE CURRENT, PROSPECT, OR HYBRID. I DON'T THINK I ALSO NEED
PCSTATUS. DOES THAT MAKE SENSE?

QUESTIONS
--I sort of asked this in an above question, but can you explain why I
need
a tblCompanyContact and a tblPropertyContact. I am sure there is a very
logical answer but I am just not getting it yet.

***Company Contact store just that and the same for Property Contacts.
Each
has their own contacts. Perhaps I don't understand your question? I
PROBABLY NEED TO DO A BETTER JOB OF EXPLANING. LET ME TRY TO EXPLAIN MORE
CLEARLY HOW THE BUSINESS MODEL WORKS AGAIN. OUR PROSPECTIVE CLIENTS ARE
COMPANIES. EACH ONE OF THOSE COMPANIES HAS ONE OR MORE CONTACTS WE DEAL
WITH. AND EACH OF THOSE CONTACTS MANAGES ONE OR MORE PROPERTIES THAT WE
SERVICE. VERY RARELY WILL ONE COMPANY HAVE MORE THAN ONE CONTACT MANAGING
A SINGLE PROPERTY. THE COMPANY CONTACTS AND THE PROPERTY CONTACTS ARE THE
SAME THING. THAT IS WHY I WAS CONFUSED WHY WE HAD DIFFERENT TABLES FOR
EACH SINCE THEY ARE THE SAME. WHEN I IDENTIFY A NEW PROSPECT, I AM
LOOKING FOR THE COMPANY INFO, THE CONTACT INFO AND THE PROPERTY INFO. I
ALSO WANT TO MAKE SURE I IDENTIFY WHICH CONTACT MANAGES WHICH PROPERTY. I
AM NOT SURE IF THIS IS HELPING??? EACH PROPERTY IS UNDER THE COMPANY
UMBRELLA, BUT IT IS ALSO LINKED TO A CONTACT THAT IS ALSO UNDER THE
COMPANY UMBRELLA.

--Will this table be able to do the same main phone and main address query
like the tblCompanyContact?

***Yes. GREAT.

--Same question as before, if possible, I would like to work with the
ccBidSchedule. Similar to what you did with the main phone number
situation
with the tblCompany and tblCompanyContact.I should be able to choose if
the
pcBidScheule is the same as the ccBidSchedule. If yes, it will populate
in
this area. If no, I will have the option to choose a new date.

***See above. ANSWERED ABOVE.

--Same question as before, is there a way to set alerts associated with
the
pcBidSchedule (if that is even a necessary field in this table)? So our
staff is reminded (via email?) of an upcoming bid opening?

**See above. GREAT.

tblPropertyTypes
ptPropertyTypeID (pk) - autonumber
ptPropertyType - lookup (Office, Retail, Industrial, Hospitality,
Multiple)

tblStaff
sStaffID (pk)
sStaffName - lookup (insert all staff names)

tblBids
bBidID (pk) - autonumber
bPropertyID (fk)
bDateIssued
bStaffID (fk)
bAmount
bBidStatusID (fk)

tblBidStatus
bsBidStatusID (pk) - autonumber
bsBidStatusType - lookup (Pending, Won, Lost)

tblBidDetails
bBidID (fk)
bAttachment - text (255)

***No OLE Object data type, you store a path to the file which can be
opened. OLE objects quickly fill up your database making it grow and you
don't want to hit the 2 gig limit that fast. OK, MAKES SENSE.

tblPropertyBids
pbPropertyID (fk)
pbBidID (fk)

QUESTIONS
--Why is tblPropertyBids necessary?

**Can't a Property have more then one bid? NO. THEY WILL HAVE MORE THAN
ONE BID WHEN YOU CONSIDER ALL THE DIFFERENT VENDORS, BUT I ONLY CARE ABOUT
THE BIDS WE ISSUE FOR EACH PROPERTY. SO, NO, EACH PROPERTY WILL ONLY HAVE
ONE TGC BID. DOES THAT HELP?

--Do you need to put a foreign key for the BidID into the tblProperty?
Again, this is an example of when I am not sure which table gets the foreign
key.

***No, it is linked by PropertyID no need to link by anything else. OK.

FUTURE STUFF
--Email prospective clients directly in Access ***Can be done. GREAT

--Track email correspondence automatically as an activity in tblActivities
*** I AM ASSUMING THIS CAN BE DONE AS WELL. SO, GREAT!

--Send collateral attachments directly in email within Access ***Can be done
as long in predetermined path OR will have to have a Browse for folder
option. OK, GREAT

--If necessary, link emails sent through Boomerang (3rd party email service)
with tblActivities ***Don't know, never used Boomerang. I DON'T KNOW TOO
MUCH ABOUT IT EITHER...WILL HAVE TO LEARN MORE AND THEN TACKLE LATER.

--Link contact info with Outlook ***While I have not done it, it can be
done. OK.

--Upload email templates to be used for Access email **Need more
information as to what you mean by templates
--Populate contact info into email templates. I WOULD WANT TO WRITE A
STANDARD EMAIL RESPONSE WITH GENERIC TEXT THAT COULD BE USED FOR ALL CLIENTS
AND JUST BE ABLE TO PERSONALIZE IT WITH THE COMPANY, CONTACT, AND PROPERTY
INFO FROM THE DATABASE. I JUST WANT TO BE ABLE TO STORE CANNED RESPONSES.

--Download company, contact, and property info into MS Word bids and
contract templates ***Have a look at...
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html. THANKS!

--Download company and contact street address info for mail merge ***See
above. GOT IT.

--Set an alert to notify sales staff via email when a new prospect is added
**Yes can be done. GREAT.

-- Potentially build out search option for pPastTGCBid within tblProperty so
once bids come back around, I can search past bids within database and have
those past bid amounts at my fingertips - Very much a future project. Not
something needed now. ***Yes can be done. GREAT!

--Build current client side of database - Very much a future project. **Yes
can be done. Would actually import in table and create field for Client or
Prospect, similar to a Yes/No field. That way they are all in the same
place and you can filter if you only want to see one or the other. OK,
COOL.

 

[Armen Stein]

--Do I need to create a CityID table? I have CityID a few places in
different tables but haven't created a CityID table yet.

***I use one because you'd be surprised how many typos people can make when
typing their own city name. So yes, you need a City table should you decide
to go that route OR you could change it to CityName and let people type.

I'll just jump in briefly with a suggestion.

Cities are one of the very few tables for which we don't link with
foreign keys or enforce referential integrity. As Gina suggests, we
just let people type in the City name - it becomes data-entry
assistance rather than a rigid relationship.

- we create tblCity
- we create relationships from any table with an address to tblCity
using CityName, without enforcing referential integrity.
- on our forms, the City combobox helps users remember the spelling of
the city and type it in faster.
- the City combobox traps for NotInList and silently adds a new City
on the fly.
- we build a simple admin screen to allow the user to delete incorrect
cities. They should also find and correct those cities in the address
tables.

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Gina Whipp]

Armen,

Great point! It is true no referential integrity, just to prevent typos!
Glad you brought that up, jump in anytime!

--
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

--Do I need to create a CityID table? I have CityID a few places in
different tables but haven't created a CityID table yet.

***I use one because you'd be surprised how many typos people can make when
typing their own city name. So yes, you need a City table should you
decide
to go that route OR you could change it to CityName and let people type.

I'll just jump in briefly with a suggestion.

Cities are one of the very few tables for which we don't link with
foreign keys or enforce referential integrity. As Gina suggests, we
just let people type in the City name - it becomes data-entry
assistance rather than a rigid relationship.

- we create tblCity
- we create relationships from any table with an address to tblCity
using CityName, without enforcing referential integrity.
- on our forms, the City combobox helps users remember the spelling of
the city and type it in faster.
- the City combobox traps for NotInList and silently adds a new City
on the fly.
- we build a simple admin screen to allow the user to delete incorrect
cities. They should also find and correct those cities in the address
tables.

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[TGC]

That is helpful. After reading Gina's response on that question I thought
about having some kind of autofill option where when the user starts typing
in the city name, the field starts filling it in (similar to google). It
sounds like that is what you are suggesting. Is that correct?

 

[TGC]

Haha....no problem. I am only in the office on Tuesdays and Thursdays
anyways so that is the only time I will be working on this.

Thanks again!

 

[Gina Whipp]

Ellen,

That is what we are suggesting but with the table set up so they can't make
a typo.

--
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
That is helpful. After reading Gina's response on that question I thought
about having some kind of autofill option where when the user starts typing
in the city name, the field starts filling it in (similar to google). It
sounds like that is what you are suggesting. Is that correct?

 

[Armen Stein]

That is what we are suggesting but with the table set up so they can't make
a typo.

Yes. A combobox has a built-in ability to display the whole value of
the first match it finds, which makes data entry much quicker.

However, the way we do it just discourages typos, it doesn't *prevent*
them. If they type a new city name, we add it to the lookup table
automatically and silently. So it's possible to add a misspelled
city. It's just not that big of a deal to remove it later, and we
find that when a correctly spelled city is in there already, people
tend to just use it and not get creative with a new spelling.

You typically need some kind of add-on-the-fly because new cities will
need to be added all the time.

You could prompt the user in the Not In List event to see if they want
to add a new city, but we don't want to slow down the data entry
process. So we just add it in there without asking.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Gina Whipp]

Ellen,

Okay... no worries about the all caps... I hate it also, looks like
yelling, but I get why you did it.

Right now, I just want to focus on the Bid Schedule so I can get that thru
my *thick* head and then I'll deal with the other questions/comments...

So, if I understand the below... The Bid Schedule is attached to the
Property and there will always be only one date in that field. If that is
true then it only needs to be in tblProperty. Having it in tblCompany
serves no purpose unless I missed something.

tblProperty
pBidSchedule
pCompanyID ---> tblCompanyContacts (ccCompanyID)

OR

tblProperty
pBidSchedule
pCompanyID ---> tblCompany ---> tblCompanyContacts

The query can be run either way.

THIS IS HOW I WANT TO USE THE BID SCHEDULES. EACH PROPERTY IS UNDER
CONTRACT FOR THEIR LANDSCAPING. AND OBVIOUSLY THOSE CONTRACTS EXPIRE SO
COMPANIES GO TO BID. SOMETIMES A COMPANY CONTACT BIDS ALL THEIR PROPERTIES
AT THE SAME TIME OR SOMETIMES EACH PROPERTY HAS A DIFFERENT BID DATE. I
WANT
TO BE ABLE TO TRACK THAT AND BE ABLE TO SET ALERTS TO REMIND US THAT THOSE
DATES ARE APPROACHING SO WE REMEMBER TO REACH OUT TO THEM VIA THE DIFFERENT
ACTIVITIES OR SUBMIT A BID AT THE RIGHT TIME. SO, I WANT TO HAVE A BID
SCHEDULE FIELD (WHICH WILL SIMPLY BE A DATE) FOR THE COMPANY CONTACTS AND
FOR
EACH PROPERTY. I WANT TO HAVE THE OPTION ON THE PROPERTY TO CHOOSE THE
COMPANY CONTACT BID SCHEDULE DATE OR ENTER A NEW ONE IF THEY ARE DIFFERENT.
SO, ESSENTIALLY THERE IS JUST ONE DATE THAT WILL SHOW UP IN THE BID SCHEDULE
FIELDS. THEY MAY BE DIFFERENT DATES, BUT THEY WILL BE SINGULAR DATES
ALWAYS.
DOES THAT HELP?


--
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