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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|