A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Prospective Client Tracking Database



 
 
Thread Tools Display Modes
  #51  
Old January 28th, 2010, 07:20 PM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default Prospective Client Tracking Database

Here is the catch with that...

Sometimes when we are looking up prospective clients, we have all the info
for the company and the contact, but no info for the property. So if that is
the case, we will direct all our efforts towards the contact. One of our
first questions is, "when do you go to bid?" So, I want to be able to put
the bid schedule under the contact and then have the ability to choose that
same bid schedule for each property or have a different bid schedule for the
property if the contact does not bid all their properties at once.

Does that make sense?

"Gina Whipp" wrote:

Ellen,

I guess you can't see my answer so I am reposting...

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

"TGC" wrote in message
...
Hello again! Did you get a chance to review the questions from my 1/26 post
from this string?

Thanks again!

Ellen

"Gina Whipp" wrote:

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

"Gina Whipp" wrote:

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
"TGC" wrote in message
...
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/f...aa c8za637888

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?

  #52  
Old January 28th, 2010, 08:31 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Prospective Client Tracking Database

Okay, now I get it... scratching head but thinking having it in two
tables is the way to go. Now, I have to go look at the table structure and
make sure all is right in the world but I believe that was the last hiccup!

--
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
...
Here is the catch with that...

Sometimes when we are looking up prospective clients, we have all the info
for the company and the contact, but no info for the property. So if that
is
the case, we will direct all our efforts towards the contact. One of our
first questions is, "when do you go to bid?" So, I want to be able to put
the bid schedule under the contact and then have the ability to choose that
same bid schedule for each property or have a different bid schedule for the
property if the contact does not bid all their properties at once.

Does that make sense?

"Gina Whipp" wrote:

Ellen,

I guess you can't see my answer so I am reposting...

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

"TGC" wrote in message
...
Hello again! Did you get a chance to review the questions from my 1/26
post
from this string?

Thanks again!

Ellen

"Gina Whipp" wrote:

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

"Gina Whipp" wrote:

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
"TGC" wrote in message
...
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/f...aa c8za637888

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?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.