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" wrote:
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
"TGC" wrote in message
...
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" wrote:
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/Ap****ueBadWord.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" wrote in message
...
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