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