Prospective Client Tracking Database

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

On Tue, 26 Jan 2010 15:17:01 -0800, TGC wrote:

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

A Combo Box based on a table of cities will do so, yes.
--

John W. Vinson [MVP]

Armen,

I guess I handle mine differently because the End User cannot add a City...
They have to get the *Admin* to add it. (I have a File Maintenance section
that on the *System Admin* can get to. This also prevents
changes/additions/deletions from other tables that have look-up values.) Of
course, I include a City, State and FIPS Code table in all my databases so
the odds of them typing a City I don't have is a zillion to one.

However, unless you have such a table probably better to do it your way!

--
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
"Armen Stein" wrote in message
...
On Tue, 26 Jan 2010 19:04:59 -0500, "Gina Whipp"
wrote:

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

On Tue, 26 Jan 2010 19:55:13 -0500, "Gina Whipp"
wrote:


I include a City, State and FIPS Code table in all my databases so
the odds of them typing a City I don't have is a zillion to one.

However, unless you have such a table probably better to do it your way!

We don't normally include such a table because we don't want our
client to need to keep it up to date, their addresses are usually
quite localized anyway, and we don't usually need 100% accuracy (no
bulk mail). So our method works fine in *most* cases. In other cases
we've gone the other direction and written interfaces to
address-scrubbing APIs, so I see your point!

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen Stein wrote in
:

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

I never bother with that. I just base the combo box on the unique
values for the data field it's bound to. This does require a hidden
field for saving the data before requerying the combo box, but it's
so much easier that mucking around with maintaining a lookup table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Armen Stein wrote in
:

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.

I would say that there's little point in a combo box if you're not
going to inform the user that they've typed a value that's not in
the list. While you certainly get the value of Autocomplete in
nudging them in the direction of choosing something that already
exists (and also there's the problem of users not paying attention
and choosing the wrong autocompleted value, but there's nothing can
be done about that!), but I really think the user should be informed
that they've typed a value that's not in the list.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 27 Jan 2010 02:41:33 GMT, "David W. Fenton"
wrote:

I never bother with that. I just base the combo box on the unique
values for the data field it's bound to. This does require a hidden
field for saving the data before requerying the combo box, but it's
so much easier that mucking around with maintaining a lookup table.

Good point. We've done the unique values approach too, but you can
run into performance problems on large recordsets.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen Stein wrote in
:

On 27 Jan 2010 02:41:33 GMT, "David W. Fenton"
wrote:

I never bother with that. I just base the combo box on the unique
values for the data field it's bound to. This does require a
hidden field for saving the data before requerying the combo box,
but it's so much easier that mucking around with maintaining a
lookup table.

Good point. We've done the unique values approach too, but you
can run into performance problems on large recordsets.

....in which case a combo box is likely not the correct interface
control (or you need to prefilter it, which means its recordsource
has to be a UNION with a SELECT statement returning the value for
the current record and it has to be requried on each row).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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?

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

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?

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