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
  #21  
Old January 21st, 2010, 06:47 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Prospective Client Tracking Database - stevie is PIMPing again!

"Steve" wrote in message
...

I provide help with Access, Excel and Word applications. My fees are very
reasonable. Let me work with you to design the tables for your database. I
have worked with numerous customers designing the tables for their
databases. As I previously said, you need to get your tables right before
you start creating the functionality part of your database. Some of what
you described is not correct. If you don't get the tables right, you will
end up undoing previous work that you might have spent a lot of time on.
If we work together, we can save you a lot of time creating your database.
Once you hane a proper set of tables, you can then create the rest of the
database yourself. Contact me.

Steve





Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

  #22  
Old January 21st, 2010, 06:55 PM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default Prospective Client Tracking Database

Hi Gina -

I responded awhile ago, but it looks like my response never posted. I
apologize if this is a repeat.

It looks like you might understand what I am trying to accomplish with this
database.

Essentially, I have a bunch of prospective clients that I want to track.
Each client is characterized by the company name. Each company is pretty
much built one of three ways. They are either one company with one contact
who manages multiple properties OR one company with multiple contacts who
manage one or multiple properties each OR one company with multiple contacts
who manage one property (least likely).

The company table is pretty straightforward.

The only tricky part of the company_contact table is the fact that I want
the company phone entered into the company table to be an option within the
company_phone field in the company_contact table. That way when I run a
query on a contact, the company phone will appear with any additional numbers
I enter.

The only tricky part of the property table is the fact that I want all the
contacts' first and last name within that company to appear in a field within
the property table so I can assign contacts to the different properties.

Steve brought up a good point in that I probably should also have a
correspondence table so that I can track date, message, collateral provided,
etc. that would be related to the contact.

Once all the basics are set-up, I would also like to be able to do a few
more things with the database...

- Email prospective clients directly in Access and track that within the
correspondence table
- Upload documents so I can easily attach those to emails within Access
- Create a current client database
- Ability to transfer info from prospective client database to current
client database once a contract is signed
- Email current clients directly in Access and track messages in service
request log table

Where do I begin????

Thank you for your help!
"Gina Whipp" wrote:

Fred,

Thank you... starting think I had read something wrong!

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

"Fred" wrote in message
...
From the given information, there is certainly a strong possibility that
Gina's added filed/link is the way to go.

And from the given information, ruling it out is certainly en error. So
Steve's making of a "NO-oooo!!! That is wrong!!!!" statement at that point
is
certainly itself an error , and making it that way was rude.



.

  #23  
Old January 21st, 2010, 07:01 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Prospective Client Tracking Database

TGC,

I did see your response and I replied to it... Reposting my answer...

Below I have adjusted your tables slightly... still going to need a few more
but wanted to get you started. I have also answered you questions (for
FREE) in-line, see below. Please review my questions... once I see your
answers I will finish up the table layout... for FREE. (Reference *FREE* is
because I see Steve offered to help you for money, something no one in this
newsgroup would ask you for.)

tblProperty
pPropertyID (PK)
pCompanyID (FK)
pAddress1
pAddress2
pCityID
pPostalCode
pTypeID (What is this for?)
pReceivership (Why is this also in the Company table, what are you trying
to track?)
pPrice
pVendorID (Please elaborate)
pPastTGCBid
pInterior (What are you looking to put in this field?)
pBidSchedule
pAreaSchedule
pNotes (If going to be Memo field then it needs to go in a seperate table)


tblCompany
cCompanyID (PK)
cCompanyName
cMainPhoneNumber
cAddress1
cAddress2
cCityID
cPostalCode
cFaxumber
cWebsite
cStatusID
cTypeID (Not sure what this is)
cReceivership

tblCompanyContact
ccCompanyContactID (PK)
ccCompanyID (FK)
ccFirstName
ccLastName
ccDirectPhoneNumber
ccCellPhone
ccFaxNumber
cceMailAddress
ccContactType (Yes/No - to determine if Main Contact or not)
ccStatus

tblActivities
aDate
aActivityTypeID (FK - Correspondence, Phone, eMail, Meeting, etc...)
aNotes (Prefer you don't use a Memo field data type here)
aDone (Yes/No)

BidSchedule - What is this and why here and in Property table?)

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

It sounds like you might be understanding what I am trying to do...

I have a bunch of prospective companies. And there are really 2 main
models
I am looking at. One company with one contact who manages multiple
properties OR one company with multiple contacts who each manage multiple
properties.

All the fields are pretty straightforward with the company table. The
only
tricky one with the contact table is the Business_Phone. I want to be
able
to see the phone number I entered in the company table so when I run a
query
on a contact, the main business phone number shows up. And the only
tricky
one with the property table is the contact name. I want the first and
last
name of the contacts I entered for any given company to show up here so I
can
assign contacts to each property within the company.


****This will not be a problem as long as you place a ContactType in the
table. A query can be run to show Main Contact and if Property Phone exists
show that, if not show ContactDirectPhoneNumber

Steve made a note about adding a correspondence table into this database.
The more I think about it, I think that is a good idea. With the
correspondence table, I would be able to record date, message, collateral
info that was communicated to prospective client. That new table would
need
to relate to the contact table.


****See above

Once all the basics are set up, I would also like to be able to do a few
additional things....

- Email prospective clients directly in Access


****No problem

- Upload marketing collateral into prospective client database so it is
easily emailed through Access


****No problem

- Create a current client database (with service request log table built
in)


****No problem

- Ability to transfer info from prospective client database to current
client database once a contract is signed


****Actually, you would be better of keeping all Client Types in the same
database, unless there is a seperate system that handles Clients?

- Email current clients from current client database directly in Access


****No problem

The more I write the more I am realizing that this isn't as simple as I
had
originally thought!!!


****No, Access is not easy but is anything in life worth having? It will be
a bit of *work* but in the end it will be worth it. And we will be right
here to help (for FREE) when you get stuck. So let's focus on getting the
tables set-up properly so you can move on to the fun part... CODING! grin


Any brilliant suggestions?



--
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 responded awhile ago, but it looks like my response never posted. I
apologize if this is a repeat.

It looks like you might understand what I am trying to accomplish with
this
database.

Essentially, I have a bunch of prospective clients that I want to track.
Each client is characterized by the company name. Each company is pretty
much built one of three ways. They are either one company with one
contact
who manages multiple properties OR one company with multiple contacts who
manage one or multiple properties each OR one company with multiple
contacts
who manage one property (least likely).

The company table is pretty straightforward.

The only tricky part of the company_contact table is the fact that I want
the company phone entered into the company table to be an option within
the
company_phone field in the company_contact table. That way when I run a
query on a contact, the company phone will appear with any additional
numbers
I enter.

The only tricky part of the property table is the fact that I want all the
contacts' first and last name within that company to appear in a field
within
the property table so I can assign contacts to the different properties.

Steve brought up a good point in that I probably should also have a
correspondence table so that I can track date, message, collateral
provided,
etc. that would be related to the contact.

Once all the basics are set-up, I would also like to be able to do a few
more things with the database...

- Email prospective clients directly in Access and track that within the
correspondence table
- Upload documents so I can easily attach those to emails within Access
- Create a current client database
- Ability to transfer info from prospective client database to current
client database once a contract is signed
- Email current clients directly in Access and track messages in service
request log table

Where do I begin????

Thank you for your help!
"Gina Whipp" wrote:

Fred,

Thank you... starting think I had read something wrong!

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

"Fred" wrote in message
...
From the given information, there is certainly a strong possibility
that
Gina's added filed/link is the way to go.

And from the given information, ruling it out is certainly en error.
So
Steve's making of a "NO-oooo!!! That is wrong!!!!" statement at that
point
is
certainly itself an error , and making it that way was rude.



.



  #24  
Old January 21st, 2010, 07:37 PM posted to microsoft.public.access.tablesdbdesign
StopThisAdvertising
external usenet poster
 
Posts: 334
Default Prospective Client Tracking Database


"Steve" schreef in bericht ...
The more I write the more I am realizing that this isn't as simple as I had originally thought!!!


snipped the crap advertising $tuff...

And now $teve offers his 'salvation...'


--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

Arno R


  #25  
Old January 21st, 2010, 10:31 PM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default Prospective Client Tracking Database

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
eventually be an add-on to this database or be a whole new one. Suggestions?

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?

Thank you again SOOO much for your help. I look forward to your response
and to continuing with this project.

Ellen

"Gina Whipp" wrote:

TGC,

I did see your response and I replied to it... Reposting my answer...

Below I have adjusted your tables slightly... still going to need a few more
but wanted to get you started. I have also answered you questions (for
FREE) in-line, see below. Please review my questions... once I see your
answers I will finish up the table layout... for FREE. (Reference *FREE* is
because I see Steve offered to help you for money, something no one in this
newsgroup would ask you for.)

tblProperty
pPropertyID (PK)
pCompanyID (FK)
pAddress1
pAddress2
pCityID
pPostalCode
pTypeID (What is this for?)
pReceivership (Why is this also in the Company table, what are you trying
to track?)
pPrice
pVendorID (Please elaborate)
pPastTGCBid
pInterior (What are you looking to put in this field?)
pBidSchedule
pAreaSchedule
pNotes (If going to be Memo field then it needs to go in a seperate table)


tblCompany
cCompanyID (PK)
cCompanyName
cMainPhoneNumber
cAddress1
cAddress2
cCityID
cPostalCode
cFaxumber
cWebsite
cStatusID
cTypeID (Not sure what this is)
cReceivership

tblCompanyContact
ccCompanyContactID (PK)
ccCompanyID (FK)
ccFirstName
ccLastName
ccDirectPhoneNumber
ccCellPhone
ccFaxNumber
cceMailAddress
ccContactType (Yes/No - to determine if Main Contact or not)
ccStatus

tblActivities
aDate
aActivityTypeID (FK - Correspondence, Phone, eMail, Meeting, etc...)
aNotes (Prefer you don't use a Memo field data type here)
aDone (Yes/No)

BidSchedule - What is this and why here and in Property table?)

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

It sounds like you might be understanding what I am trying to do...

I have a bunch of prospective companies. And there are really 2 main
models
I am looking at. One company with one contact who manages multiple
properties OR one company with multiple contacts who each manage multiple
properties.

All the fields are pretty straightforward with the company table. The
only
tricky one with the contact table is the Business_Phone. I want to be
able
to see the phone number I entered in the company table so when I run a
query
on a contact, the main business phone number shows up. And the only
tricky
one with the property table is the contact name. I want the first and
last
name of the contacts I entered for any given company to show up here so I
can
assign contacts to each property within the company.


****This will not be a problem as long as you place a ContactType in the
table. A query can be run to show Main Contact and if Property Phone exists
show that, if not show ContactDirectPhoneNumber

Steve made a note about adding a correspondence table into this database.
The more I think about it, I think that is a good idea. With the
correspondence table, I would be able to record date, message, collateral
info that was communicated to prospective client. That new table would
need
to relate to the contact table.


****See above

Once all the basics are set up, I would also like to be able to do a few
additional things....

- Email prospective clients directly in Access


****No problem

- Upload marketing collateral into prospective client database so it is
easily emailed through Access


****No problem

- Create a current client database (with service request log table built
in)


****No problem

- Ability to transfer info from prospective client database to current
client database once a contract is signed


****Actually, you would be better of keeping all Client Types in the same
database, unless there is a seperate system that handles Clients?

- Email current clients from current client database directly in Access


****No problem

The more I write the more I am realizing that this isn't as simple as I
had
originally thought!!!


****No, Access is not easy but is anything in life worth having? It will be
a bit of *work* but in the end it will be worth it. And we will be right
here to help (for FREE) when you get stuck. So let's focus on getting the
tables set-up properly so you can move on to the fun part... CODING! grin


Any brilliant suggestions?



--
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 responded awhile ago, but it looks like my response never posted. I
apologize if this is a repeat.

It looks like you might understand what I am trying to accomplish with
this
database.

Essentially, I have a bunch of prospective clients that I want to track.
Each client is characterized by the company name. Each company is pretty
much built one of three ways. They are either one company with one
contact
who manages multiple properties OR one company with multiple contacts who
manage one or multiple properties each OR one company with multiple
contacts
who manage one property (least likely).

The company table is pretty straightforward.

The only tricky part of the company_contact table is the fact that I want
the company phone entered into the company table to be an option within
the
company_phone field in the company_contact table. That way when I run a
query on a contact, the company phone will appear with any additional
numbers
I enter.

The only tricky part of the property table is the fact that I want all the
contacts' first and last name within that company to appear in a field
within
the property table so I can assign contacts to the different properties.

Steve brought up a good point in that I probably should also have a
correspondence table so that I can track date, message, collateral
provided,
etc. that would be related to the contact.

Once all the basics are set-up, I would also like to be able to do a few
more things with the database...

- Email prospective clients directly in Access and track that within the
correspondence table
- Upload documents so I can easily attach those to emails within Access
- Create a current client database
- Ability to transfer info from prospective client database to current
client database once a contract is signed
- Email current clients directly in Access and track messages in service
request log table

Where do I begin????

Thank you for your help!
"Gina Whipp" wrote:

Fred,

Thank you... starting think I had read something wrong!

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

"Fred" wrote in message
...
From the given information, there is certainly a strong possibility
that
Gina's added filed/link is the way to go.

And from the given information, ruling it out is certainly en error.
So
Steve's making of a "NO-oooo!!! That is wrong!!!!" statement at that
point
is
certainly itself an error , and making it that way was rude.



.



.

  #26  
Old January 21st, 2010, 11:23 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Prospective Client Tracking Database

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
eventually be an add-on to this database or be a whole new one.
Suggestions?

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?

Thank you again SOOO much for your help. I look forward to your response
and to continuing with this project.

Ellen

"Gina Whipp" wrote:

TGC,

I did see your response and I replied to it... Reposting my answer...

Below I have adjusted your tables slightly... still going to need a few
more
but wanted to get you started. I have also answered you questions (for
FREE) in-line, see below. Please review my questions... once I see your
answers I will finish up the table layout... for FREE. (Reference *FREE*
is
because I see Steve offered to help you for money, something no one in
this
newsgroup would ask you for.)

tblProperty
pPropertyID (PK)
pCompanyID (FK)
pAddress1
pAddress2
pCityID
pPostalCode
pTypeID (What is this for?)
pReceivership (Why is this also in the Company table, what are you
trying
to track?)
pPrice
pVendorID (Please elaborate)
pPastTGCBid
pInterior (What are you looking to put in this field?)
pBidSchedule
pAreaSchedule
pNotes (If going to be Memo field then it needs to go in a seperate
table)


tblCompany
cCompanyID (PK)
cCompanyName
cMainPhoneNumber
cAddress1
cAddress2
cCityID
cPostalCode
cFaxumber
cWebsite
cStatusID
cTypeID (Not sure what this is)
cReceivership

tblCompanyContact
ccCompanyContactID (PK)
ccCompanyID (FK)
ccFirstName
ccLastName
ccDirectPhoneNumber
ccCellPhone
ccFaxNumber
cceMailAddress
ccContactType (Yes/No - to determine if Main Contact or not)
ccStatus

tblActivities
aDate
aActivityTypeID (FK - Correspondence, Phone, eMail, Meeting, etc...)
aNotes (Prefer you don't use a Memo field data type here)
aDone (Yes/No)

BidSchedule - What is this and why here and in Property table?)

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

It sounds like you might be understanding what I am trying to do...

I have a bunch of prospective companies. And there are really 2 main
models
I am looking at. One company with one contact who manages multiple
properties OR one company with multiple contacts who each manage
multiple
properties.

All the fields are pretty straightforward with the company table. The
only
tricky one with the contact table is the Business_Phone. I want to be
able
to see the phone number I entered in the company table so when I run a
query
on a contact, the main business phone number shows up. And the only
tricky
one with the property table is the contact name. I want the first and
last
name of the contacts I entered for any given company to show up here so
I
can
assign contacts to each property within the company.


****This will not be a problem as long as you place a ContactType in the
table. A query can be run to show Main Contact and if Property Phone
exists
show that, if not show ContactDirectPhoneNumber

Steve made a note about adding a correspondence table into this
database.
The more I think about it, I think that is a good idea. With the
correspondence table, I would be able to record date, message,
collateral
info that was communicated to prospective client. That new table would
need
to relate to the contact table.


****See above

Once all the basics are set up, I would also like to be able to do a
few
additional things....

- Email prospective clients directly in Access


****No problem

- Upload marketing collateral into prospective client database so it is
easily emailed through Access


****No problem

- Create a current client database (with service request log table
built
in)


****No problem

- Ability to transfer info from prospective client database to current
client database once a contract is signed


****Actually, you would be better of keeping all Client Types in the same
database, unless there is a seperate system that handles Clients?

- Email current clients from current client database directly in Access


****No problem

The more I write the more I am realizing that this isn't as simple as I
had
originally thought!!!


****No, Access is not easy but is anything in life worth having? It will
be
a bit of *work* but in the end it will be worth it. And we will be right
here to help (for FREE) when you get stuck. So let's focus on getting
the
tables set-up properly so you can move on to the fun part... CODING!
grin


Any brilliant suggestions?



--
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 responded awhile ago, but it looks like my response never posted. I
apologize if this is a repeat.

It looks like you might understand what I am trying to accomplish with
this
database.

Essentially, I have a bunch of prospective clients that I want to
track.
Each client is characterized by the company name. Each company is
pretty
much built one of three ways. They are either one company with one
contact
who manages multiple properties OR one company with multiple contacts
who
manage one or multiple properties each OR one company with multiple
contacts
who manage one property (least likely).

The company table is pretty straightforward.

The only tricky part of the company_contact table is the fact that I
want
the company phone entered into the company table to be an option within
the
company_phone field in the company_contact table. That way when I run
a
query on a contact, the company phone will appear with any additional
numbers
I enter.

The only tricky part of the property table is the fact that I want all
the
contacts' first and last name within that company to appear in a field
within
the property table so I can assign contacts to the different
properties.

Steve brought up a good point in that I probably should also have a
correspondence table so that I can track date, message, collateral
provided,
etc. that would be related to the contact.

Once all the basics are set-up, I would also like to be able to do a
few
more things with the database...

- Email prospective clients directly in Access and track that within
the
correspondence table
- Upload documents so I can easily attach those to emails within Access
- Create a current client database
- Ability to transfer info from prospective client database to current
client database once a contract is signed
- Email current clients directly in Access and track messages in
service
request log table

Where do I begin????

Thank you for your help!
"Gina Whipp" wrote:

Fred,

Thank you... starting think I had read something wrong!

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

"Fred" wrote in message
...
From the given information, there is certainly a strong possibility
that
Gina's added filed/link is the way to go.

And from the given information, ruling it out is certainly en error.
So
Steve's making of a "NO-oooo!!! That is wrong!!!!" statement at that
point
is
certainly itself an error , and making it that way was rude.



.



.



  #27  
Old January 22nd, 2010, 12:34 AM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default Prospective Client Tracking Database

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

  #28  
Old January 22nd, 2010, 12:41 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Prospective Client Tracking Database

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



  #29  
Old January 22nd, 2010, 10:31 AM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Prospective Client Tracking Database

"Steve" wrote in message
...
The more I write the more I am realizing that this isn't as simple as I
had originally thought!!!

I provide help with Access, Excel and Word applications.


So does everyone else and it's infinitely better than yours and completely
free of charge. Another swing and a miss, why *do* you bother?

  #30  
Old January 22nd, 2010, 03:14 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Prospective Client Tracking Database

As context for the above advice, Gina, Keith and John are highly respected
experts in these forums.

Steve often has right answers, but they are colored by trying to head
towards soliciting getting paid for advice (a violation of the rules) plus,
in this case, the advice is colored by looking for a chance to insult someone
who he has insulted before.

You (probably still) have a "real world" question to answer which will lead
to structure.....whether you are going to record a property as linked
primarily to the person who is managing it vs. to the company that they work
for. I've done in-practice implementations of DB's similar to yours many
times, and have wrestled with that same question. On the face of it, linkage
to the person might seem the answer...after all such is a more specific
information and of interest. However, in practice it usually doesn't work
out so well. Every time the company has a personnel change or reorganization
of coverage, your linkages become outdated/wrong. And those companies
usually don't routinely communicate such changes. And so keeping your DB
updated requires a large amount of chasing down information that you would
not otherwise have. In DB usage, "too much work in a non-essential area"
usually means "doesn't get done" . Linkage instead to the company eliminates
that particular problem.

And so linkage to the company is probably a better idea, which is what the
advice from the noted 3 highly respected individuals is based on.


 




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 12:45 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.