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