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 |
#31
|
|||
|
|||
Prospective Client Tracking Database
Hi Gina -
I had a chance to digest all your helpful info... I have rewritten all the tables I think I need based on the info you provided me. I have included a few questions next to a few fields and have included questions after some tables that apply to the table in general. I also included some future stuff I want to address down the road. I know I am a far way from most, if not all, of these things, but I wanted to lay them out there now in case we needed to set up additional things within the foundation and tables in order to make them work. Thanks again so much for your help! Here it goes... tblCompany cCompanyID (pk) - autonumber cCompanyName cMainPhone cAddress1 cAddress2 cCityID cPostalCode cFaxNumber cWebsite cClientTypeID (fk) cPropertyTypeID (fk) tblClientTypes ctClientTypeID (pk) – autonumber ctClientType – lookup (Current, Prospect, Hybrid) tblCompanyContact ccCompanyContactID (pk) – autonumber ccCompanyID (fk) ccFirstName ccLastName ccClientType (fk) ccContactType – yes/no (Main contact or not?) ccDirectPhone ccCellPhone ccEmail ccFaxNumber ccAddress1 ccAddress2 ccCityID - ccPostalCode ccBidSchedule – calendar – This will be a date. Does this work? QUESTIONS --Query will determine if the contact is the main contact. If yes, the company main phone will show along with direct and cell phone options. If no, only the direct and cell phone options will appear. Same thing for contact address. Query will determine if the contact is at the main company address. If yes, the company address will populate. If not, the address fields will remain blank to be filled in. Is that correct? --Do I need to create a CityID table? I have CityID a few places in different tables but haven’t created a CityID table yet. --Is there a way to set alerts associated with the ccBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? tblActivities aDate - calendar aActivityTypeID (fk) aStaffID (fk) aCollateralTypeID (fk) aNotes – text aDone – yes/no tblActivityTypes atActivityTypeID (pk) – autonumber atActivityType – lookup (Phone Conversation, Phone VM, Meeting, Email, Mail, Fax) QUESTIONS --Is there a way to set an activities schedule reminder to alert staff to follow up with a prospect? tblCollateralTypes ctCollateralTypeID(pk) – autonumber ctCollateralType – lookup (Newsletter, Sustainable Brochure, Stick Brochure, Complete Marketing Package) tblProperty pPropertyID (pk) – autonumber pCompanyID (fk) pAddress1 pAddress2 pCityID pPostalCode pPropertyTypeID (fk) pReceivership – yes/no pExistingPrice pCurrentVendorID (fk) pPastTGCBid pInterior – yes/no pBidSchedule – calendar pAreaScheduleID (fk) pNotes – text (255) QUESTIONS --I am a little confused. I want to make sure each property has a contact assigned to it, but I am not sure if you put a contact foreign key in the tblProperty or you put a property foreign key in the tblCompanyContact or tblPropertyContact. --Did you create the tblPropertyContact in case a contact hasn’t already been entered, but you have a new property and you need to assign a contact to it? I am a little confused why I have tblCompanyContact and tblPropertyContact. Fred mentioned that it is important to link a property to a company rather than link it to a contact, which is very helpful once the property contract is won. However, this is a prospective client database so I am not so sure that is the best way to link them. Thoughts? --If possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact…I should be able to choose if the pBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. --Same question as before, is there a way to set alerts associated with the pBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? tblCurrentVendors cvCurrentVendorID (pk) – autonumber cvCurrentVendorName tblPropertyVendors pvPropertyID (fk) pvCurrentVendorID (fk) QUESTIONS --Why is this table (tblPropertyVendors) necessary. All I am looking for with the tblCurrentVendors is who the property currently uses for their commercial landscaping. Who currently holds that contract. I tweaked the previous tblVendors you had to make the tblCurrentVendors because I am not interested in whether they do interior or exterior work, I just want to know who currently services that property. So, I ended up also adding back the pInterior field into the tblProperty because I just want to know (yes or no) whether that property has interior needs. All companies have exterior needs, that is why they are in the database to begin with, but some may also have interior and this is where I would answer that question. Does that work? tblAreaSchedule asAreaScheduleID (pk) – autonumber asAreaSchedule – lookup (Area 1, Area 2, Area 3, Area 4, Area 5) tbl PropertyContact pcPropertyContactID (pk) – Don’t you need this???? It wasn't in your last post pcPropertyID (fk) pcFirstName pcLastName pcClientType (fk) – Don’t you need this??? Just like in tblCompanyContact? pcContactType – yes/no (Main contact or not?) pcDirectPhone pcCellPhone pcEmail pcFaxNumber pcBidSchedule – calendar – Don’t you need this? pcStatus – What is this for??? QUESTIONS --I sort of asked this in an above question, but can you explain why I need a tblCompanyContact and a tblPropertyContact. I am sure there is a very logical answer but I am just not getting it yet. --Will this table be able to do the same main phone and main address query like the tblCompanyContact? --Same question as before, if possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact…I should be able to choose if the pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. --Same question as before, is there a way to set alerts associated with the pcBidSchedule (if that is even a necessary field in this table)? So our staff is reminded (via email?) of an upcoming bid opening? tblPropertyTypes ptPropertyTypeID (pk) – autonumber ptPropertyType – lookup (Office, Retail, Industrial, Hospitality, Multiple) tblStaff sStaffID (pk) sStaffName – lookup (insert all staff names) tblBids bBidID (pk) – autonumber bPropertyID (fk) bDateIssued bStaffID (fk) bAmount bBidStatusID (fk) tblBidStatus bsBidStatusID (pk) – autonumber bsBidStatusType – lookup (Pending, Won, Lost) tblBidDetails bBidID (fk) bAttachment – text (255) – OLE object tblPropertyBids pbPropertyID (fk) pbBidID (fk) QUESTIONS --Why is tblPropertyBids necessary? --Do you need to put a foreign key for the BidID into the tblProperty? Again, this is an example of when I am not sure which table gets the foreign key. FUTURE STUFF --Email prospective clients directly in Access --Track email correspondence automatically as an activity in tblActivities --Send collateral attachments directly in email within Access --If necessary, link emails sent through Boomerang (3rd party email service) with tblActivities --Link contact info with Outlook --Upload email templates to be used for Access email --Populate contact info into email templates --Download company, contact, and property info into MS Word bids and contract templates --Download company and contact street address info for mail merge --Set an alert to notify sales staff via email when a new prospect is added -- Potentially build out search option for pPastTGCBid within tblProperty so once bids come back around, I can search past bids within database and have those past bid amounts at my fingertips – Very much a future project. Not something needed now. --Build current client side of database – Very much a future project. Thanks again! Ellen "Gina Whipp" wrote: Ellen, Yes it is... and you're only at the beginning... See you next week! -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "TGC" wrote in message ... Lots of info to process....my brain hurts! Won't be back at work until next Tuesday. Will review info and respond then. Thank you again! The help is invaluable. "Gina Whipp" wrote: Ellen, You did NOT create the problem... that would be Steve who created the problem. YOU are why we volunteers are here! So, on to your tables and answers... tblProperty pPropertyID (PK) pCompanyID (FK) pAddress1 pAddress2 pCityID pPostalCode pPropertyTypeID (FK) pInReceivership (Yes/No) pPrice pPastTGCBid pBidSchedule pAreaSchedule pNotes (Text, 255) tblPropertyVendors pvPropertyID (FK) pvVendorID (FK) tblPropertyContact pcPropertyID (FK) pcFirstName pcLastName pcDirectPhoneNumber pcCellPhone pcFaxNumber pceMailAddress pcContactType (Yes/No - to determine if Main Contact or not) pcStatus tblVendors pvVendorID (PK-Autonumber) pvVendorName pvVendorTypeID (FK-Interior, Landscaping, etc...) tblPropertyTypes ptPropertyTypeID (PK-Autonumber) ptPropertyType (Office, Retail, Industiral, Hospitatlity) Also, on the properties, I want to be able to assign a contact from within the account to each property. Shouldn't that be an option here? I wanted to be able to see the first and last name options of the contacts. This might be done with a relationship as opposed to a field in the table? ****Added a table for that Oh, and I just had a new idea associated with the properties. I would like to be able to track current bids per property. So, I am guessing I might need a new table. I want to track the date the bid was issued, which staff member issued it, what the price was, and include any attachments associated with the bid (contract, budget, etc.), if possible. ****New table tblBids bBidID (PK-Autonumber) bPropertyID (FK) bDateIssued bStaffID (FK) bAmount **** You might want to consider adding a tblStaff tblBidDetails bBidID (FK) bAttachment (Text, 255) - Here you will store the path to the attachment as these can quickly couse your database to bloat should be using OLE object. And yes, you will be able to view your attachments even though you are just storing the link. tblPropertyBids pbPropertyID (FK) pbBidID (FK) tblCompany cCompanyID (PK-Autonumber) cCompanyName cMainPhoneNumber cAddress1 cAddress2 cCityID cPostalCode cFaxumber cWebsite cClientTypeID (FK) cPropertyTypeID (FK) cInReceivership tblClientTypes ctClientTypeID (PK-Autonumber) ctClientType (Current, Prospective, Hybrid, etc...) tblCompanyContact ccCompanyID (FK) ccFirstName ccLastName ccDirectPhoneNumber ccCellPhone ccFaxNumber cceMailAddress ccContactType (Yes/No - to determine if Main Contact or not) ccStatus BidSchedule - What is this and why here and in Property table?) - The bid schedule would be a calendar both within the contact and property tables. In this business, sometimes the contract bid will be handled property by property, other times it will be handled by the contact for each of the properties they manage. This is just a way for me to track when an account will be going to bid. The bid schedules should not be the same for the contact and the property. ****Does that mean the Bid Scedule is actually a date? I also want to make sure I can have the main company phone number assigned to a contact. You answered this in your response to a previous question of mine, so maybe I don't need to include it here? I also see you put a new field for ccContactType so that probably answers it. ****Yes it does so no worries there, it will be handled tblActivities aDate aActivityTypeID (FK - Correspondence, Phone, eMail, Meeting, etc...) aNotes (Prefer you don't use a Memo field data type here) aDone (Yes/No) tblActivityTypes atActivityTypeID (PK-Autonumber) atActivity In terms of the current client database idea....We currently track current clients in a program called Gold Mine. Down the road, I would like to track all current clients' info as well as transfer the account info from the prospects database and include some new additional features (include a service/request log, notify supervisors via email of service/requests, etc.). We aren't quite ready for all that yet and I just wasn't sure if that would eventually be an add-on to this database or be a whole new one. Suggestions? **** That is okay and yes you can export from GOldmine and Import to Access when you are ready. And when you are ready adding new tables will be easy but that is why you take the time to make sure the tables are correct to begin with. Think of it like a house. You can't make additions if the foundation isn't done correctly! Oh, and this is probably very naive, but do I need to put the lowercase letter in front of each field that obviously ties to the table when I am labeling the fields? ****Nope, not naive, no stupid questions only stupid answers! THAT is my own preference. It insures I *never* use a Reserved Word as a filed name AND after I am all done and two months from now I can go back and read my code and know exactly which table that field cam from. If you prefer not to use them no problem. However, read this list and make sure your field names don't show up here... http://allenbrowne.com/Ap****ueBadWord.html ....Long list, I know, that's why I do it! Thank you again SOOO much for your help. I look forward to your response and to continuing with this project. ****You're welcome! Ellen -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "TGC" wrote in message ... Wow, thank you so much for your help! And I apologize for creating a problem with other discussion group responders. I responded to your questions. tblProperty pPropertyID (PK) pCompanyID (FK) pAddress1 pAddress2 pCityID pPostalCode pTypeID (What is this for?) - This would be a lookup where I have 4 different commercial property types - office, retail, industrial, hospitality pReceivership (Why is this also in the Company table, what are you trying to track?) - The receivership would be a yes/no checkbox. A company would have that field because they might specialize in receiverships (commercial space owned by the bank) and the property would have that field because the property itself might be in receivership. pPrice pVendorID (Please elaborate) - This would be a simple text box where I could enter a properties' current landscape vendor. pPastTGCBid pInterior (What are you looking to put in this field?) - We also have an interior landscape division so this would be a yes/no checkbox for me to track whether or not a client has interior needs. pBidSchedule pAreaSchedule pNotes (If going to be Memo field then it needs to go in a seperate table) - I was just hoping to have a section where I could enter additional notes, if needed for each property. Also, on the properties, I want to be able to assign a contact from within the account to each property. Shouldn't that be an option here? I wanted to be able to see the first and last name options of the contacts. This might be done with a relationship as opposed to a field in the table? Oh, and I just had a new idea associated with the properties. I would like to be able to track current bids per property. So, I am guessing I might need a new table. I want to track the date the bid was issued, which staff member issued it, what the price was, and include any attachments associated with the bid (contract, budget, etc.), if possible. tblCompany cCompanyID (PK) cCompanyName cMainPhoneNumber cAddress1 cAddress2 cCityID cPostalCode cFaxumber cWebsite cStatusID - This is a simple drop down menu for me to choose what type of client they are - current, prospective, hybrid |
#32
|
|||
|
|||
Prospective Client Tracking Database
Ellen,
Answers in-line... Look for the 3 asterisks! (Hope I got everything) -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "TGC" wrote in message ... Hi Gina - I had a chance to digest all your helpful info... I have rewritten all the tables I think I need based on the info you provided me. I have included a few questions next to a few fields and have included questions after some tables that apply to the table in general. I also included some future stuff I want to address down the road. I know I am a far way from most, if not all, of these things, but I wanted to lay them out there now in case we needed to set up additional things within the foundation and tables in order to make them work. Thanks again so much for your help! Here it goes... tblCompany cCompanyID (pk) - autonumber cCompanyName cMainPhone cAddress1 cAddress2 cCityID ****cStateID (Oops forogt that field) cPostalCode cFaxNumber cWebsite cClientTypeID (fk) cPropertyTypeID (fk) tblClientTypes ctClientTypeID (pk) - autonumber ctClientType - lookup (Current, Prospect, Hybrid) tblCompanyContact ccCompanyContactID (pk) - autonumber ccCompanyID (fk) ccFirstName ccLastName ccClientType (fk) ccContactType - yes/no (Main contact or not? ***Yep that is what that is for!) ccDirectPhone ccCellPhone ccEmail ccFaxNumber ccAddress1 ccAddress2 ccCityID ****ccStateID (Oops forogt that field) ccPostalCode ccBidSchedule - calendar - This will be a date. Does this work? ***If this a many field which means if there are going to be many dates to on COmpany Contact then this will need to broken out... but will wait for you answer. QUESTIONS --Query will determine if the contact is the main contact. If yes, the company main phone will show along with direct and cell phone options. If no, only the direct and cell phone options will appear. Same thing for contact address. Query will determine if the contact is at the main company address. If yes, the company address will populate. If not, the address fields will remain blank to be filled in. Is that correct? ***Yes that is correct. --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. --Is there a way to set alerts associated with the ccBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***Yes and here's a link to get you started when you are ready... http://www.rogersaccesslibrary.com/f...aa c8za637888 tblActivities aDate - calendar aActivityTypeID (fk) aStaffID (fk) aCollateralTypeID (fk) aNotes - text aDone - yes/no tblActivityTypes atActivityTypeID (pk) - autonumber atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email, Mail, Fax) QUESTIONS --Is there a way to set an activities schedule reminder to alert staff to follow up with a prospect? ***See above link tblCollateralTypes ctCollateralTypeID(pk) - autonumber ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick Brochure, Complete Marketing Package) ***Please give me a little background on how you plan to use the above table. tblProperty pPropertyID (pk) - autonumber pCompanyID (fk) pAddress1 pAddress2 pCityID ****pStateID (Oops forogt that field) pPostalCode pPropertyTypeID (fk) pReceivership - yes/no pExistingPrice pCurrentVendorID (fk) pPastTGCBid pInterior - yes/no pBidSchedule - calendar pAreaScheduleID (fk) pNotes - text (255) QUESTIONS --I am a little confused. I want to make sure each property has a contact assigned to it, but I am not sure if you put a contact foreign key in the tblProperty or you put a property foreign key in the tblCompanyContact or tblPropertyContact. ***That is what the tblPropertyContact is for --Did you create the tblPropertyContact in case a contact hasn't already been entered, but you have a new property and you need to assign a contact to it? I am a little confused why I have tblCompanyContact and tblPropertyContact. Fred mentioned that it is important to link a property to a company rather than link it to a contact, which is very helpful once the property contract is won. However, this is a prospective client database so I am not so sure that is the best way to link them. Thoughts? ***No, the Property Contact should be in tblProperty, there is a seperate table for them. Fred is correct (and it is the only way I do it) because Contacts (people) come and go but Companies usually hang around. If the person quits you still want to know what Company to deal with, understand better now? --If possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***Once you answer my question above regarding BidSchedule we will work on table ideas. --Same question as before, is there a way to set alerts associated with the pBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***See link above... tblVendors vVendorID (pk) - autonumber vVendorName tblPropertyVendors pvPropertyID (fk) pvVendorID (fk) QUESTIONS --Why is this table (tblPropertyVendors) necessary. All I am looking for with the tblCurrentVendors is who the property currently uses for their commercial landscaping. Who currently holds that contract. I tweaked the previous tblVendors you had to make the tblCurrentVendors because I am not interested in whether they do interior or exterior work, I just want to know who currently services that property. ***No, you will be able to pull that information via query. Because a Vendor is Vendor whether Current or not. So, I ended up also adding back the pInterior field into the tblProperty because I just want to know (yes or no) whether that property has interior needs. All companies have exterior needs, that is why they are in the database to begin with, but some may also have interior and this is where I would answer that question. Does that work? ***Yes putting that field back in is okay BUT think about tomorrow... Are you SURE you are never going to want to pull seperate lists for Exterior Vendors or Interior Vendors? If the answer is *maybe* then put it back in the Vendor table but you can leave it in the Property table, though I would remove it and leave the tblPropertyVendors. tblAreaSchedule asAreaScheduleID (pk) - autonumber asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5) ***Please elaborate what the above table will be used for? tbl PropertyContact pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last post ***Optional, not *every* table needs a Primary Key. pcPropertyID (fk) pcFirstName pcLastName pcClientType (fk) - Don't you need this??? Just like in tblCompanyContact? ***Yes, it is actually to store Property Type but I like all my PK's and FK's to share the same name so I can know by glancing which table it is linked to. pcContactType - yes/no (Main contact or not?) pcDirectPhone pcCellPhone pcEmail pcFaxNumber pcBidSchedule - calendar - Don't you need this? ***I am trying to understand Bid Schedule, see above pcStatus - What is this for??? ***I thought you wanted to know if the Contact is still active or not, if not, can be removed. QUESTIONS --I sort of asked this in an above question, but can you explain why I need a tblCompanyContact and a tblPropertyContact. I am sure there is a very logical answer but I am just not getting it yet. ***Company Contact store just that and the same for Property Contacts. Each has their own contacts. Perhaps I don't understand your question? --Will this table be able to do the same main phone and main address query like the tblCompanyContact? ***Yes --Same question as before, if possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***See above --Same question as before, is there a way to set alerts associated with the pcBidSchedule (if that is even a necessary field in this table)? So our staff is reminded (via email?) of an upcoming bid opening? **See above tblPropertyTypes ptPropertyTypeID (pk) - autonumber ptPropertyType - lookup (Office, Retail, Industrial, Hospitality, Multiple) tblStaff sStaffID (pk) sStaffName - lookup (insert all staff names) tblBids bBidID (pk) - autonumber bPropertyID (fk) bDateIssued bStaffID (fk) bAmount bBidStatusID (fk) tblBidStatus bsBidStatusID (pk) - autonumber bsBidStatusType - lookup (Pending, Won, Lost) tblBidDetails bBidID (fk) bAttachment - text (255) ***No OLE Object data type, you store a path to the file which can be opened. OLE objects quickly fill up your database making it grow and you don't want to hit the 2 gig limit that fast. tblPropertyBids pbPropertyID (fk) pbBidID (fk) QUESTIONS --Why is tblPropertyBids necessary? **Can't a Property have more then one bid? --Do you need to put a foreign key for the BidID into the tblProperty? Again, this is an example of when I am not sure which table gets the foreign key. ***No, it is linked by PropertyID no need to link by anything else. FUTURE STUFF --Email prospective clients directly in Access ***Can be done --Track email correspondence automatically as an activity in tblActivities *** --Send collateral attachments directly in email within Access ***Can be done as long in predetermined path OR will have to have a Browse for folder option --If necessary, link emails sent through Boomerang (3rd party email service) with tblActivities ***Don't know, never used Boomerang --Link contact info with Outlook ***While I have not done it, it can be done --Upload email templates to be used for Access email **Need more information as to what you mean by templates --Populate contact info into email templates --Download company, contact, and property info into MS Word bids and contract templates ***Have a look at... http://www.members.shaw.ca/AlbertKal.../msaccess.html --Download company and contact street address info for mail merge ***See above --Set an alert to notify sales staff via email when a new prospect is added **Yes can be done -- Potentially build out search option for pPastTGCBid within tblProperty so once bids come back around, I can search past bids within database and have those past bid amounts at my fingertips - Very much a future project. Not something needed now. ***Yes can be done --Build current client side of database - Very much a future project. **Yes can be done. Would actually import in table and create field for Client or Prospect, similar to a Yes/No field. That way they are all in the same place and you can filter if you only want to see one or the other. Thanks again! Ellen CLIPPED |
#33
|
|||
|
|||
Prospective Client Tracking Database
Awesome! Thanks! I responded to a few things below. Put my answers in CAPS
so you could see them. Sorry, I hate CAPS, but probably best way to differentiate on this string... Thanks again Gina! "Gina Whipp" wrote: Ellen, Answers in-line... Look for the 3 asterisks! (Hope I got everything) -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "TGC" wrote in message ... Hi Gina - I had a chance to digest all your helpful info... I have rewritten all the tables I think I need based on the info you provided me. I have included a few questions next to a few fields and have included questions after some tables that apply to the table in general. I also included some future stuff I want to address down the road. I know I am a far way from most, if not all, of these things, but I wanted to lay them out there now in case we needed to set up additional things within the foundation and tables in order to make them work. Thanks again so much for your help! Here it goes... tblCompany cCompanyID (pk) - autonumber cCompanyName cMainPhone cAddress1 cAddress2 cCityID ****cStateID (Oops forogt that field) - DUH, THANKS! cPostalCode cFaxNumber cWebsite cClientTypeID (fk) cPropertyTypeID (fk) tblClientTypes ctClientTypeID (pk) - autonumber ctClientType - lookup (Current, Prospect, Hybrid) tblCompanyContact ccCompanyContactID (pk) - autonumber ccCompanyID (fk) ccFirstName ccLastName ccClientType (fk) ccContactType - yes/no (Main contact or not? ***Yep that is what that is for!) ccDirectPhone ccCellPhone ccEmail ccFaxNumber ccAddress1 ccAddress2 ccCityID ****ccStateID (Oops forogt that field) - SAME. THANKS! ccPostalCode ccBidSchedule - calendar - This will be a date. Does this work? ***If this a many field which means if there are going to be many dates to on COmpany Contact then this will need to broken out... but will wait for you answer. THIS IS HOW I WANT TO USE THE BID SCHEDULES. EACH PROPERTY IS UNDER CONTRACT FOR THEIR LANDSCAPING. AND OBVIOUSLY THOSE CONTRACTS EXPIRE SO COMPANIES GO TO BID. SOMETIMES A COMPANY CONTACT BIDS ALL THEIR PROPERTIES AT THE SAME TIME OR SOMETIMES EACH PROPERTY HAS A DIFFERENT BID DATE. I WANT TO BE ABLE TO TRACK THAT AND BE ABLE TO SET ALERTS TO REMIND US THAT THOSE DATES ARE APPROACHING SO WE REMEMBER TO REACH OUT TO THEM VIA THE DIFFERENT ACTIVITIES OR SUBMIT A BID AT THE RIGHT TIME. SO, I WANT TO HAVE A BID SCHEDULE FIELD (WHICH WILL SIMPLY BE A DATE) FOR THE COMPANY CONTACTS AND FOR EACH PROPERTY. I WANT TO HAVE THE OPTION ON THE PROPERTY TO CHOOSE THE COMPANY CONTACT BID SCHEDULE DATE OR ENTER A NEW ONE IF THEY ARE DIFFERENT. SO, ESSENTIALLY THERE IS JUST ONE DATE THAT WILL SHOW UP IN THE BID SCHEDULE FIELDS. THEY MAY BE DIFFERENT DATES, BUT THEY WILL BE SINGULAR DATES ALWAYS. DOES THAT HELP? QUESTIONS --Query will determine if the contact is the main contact. If yes, the company main phone will show along with direct and cell phone options. If no, only the direct and cell phone options will appear. Same thing for contact address. Query will determine if the contact is at the main company address. If yes, the company address will populate. If not, the address fields will remain blank to be filled in. Is that correct? ***Yes that is correct. COOL. --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. MAKES SENSE --Is there a way to set alerts associated with the ccBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***Yes and here's a link to get you started when you are ready... THANK YOU! http://www.rogersaccesslibrary.com/f...aa c8za637888 tblActivities aDate - calendar aActivityTypeID (fk) aStaffID (fk) aCollateralTypeID (fk) aNotes - text aDone - yes/no tblActivityTypes atActivityTypeID (pk) - autonumber atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email, Mail, Fax) QUESTIONS --Is there a way to set an activities schedule reminder to alert staff to follow up with a prospect? ***See above link. THANK YOU tblCollateralTypes ctCollateralTypeID(pk) - autonumber ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick Brochure, Complete Marketing Package) ***Please give me a little background on how you plan to use the above table. THIS TABLE MAY NOT BE NECESSARY. IT MAY JUST NEED TO BE A LOOKUP FIELD WITH THE DIFFERENT COLLATERAL TYPES AS THE LOOKUP OPTIONS WITHIN TBLACTIVITIES. I JUST WANT TO BE ABLE TO ASSIGN WHAT TYPE OF COLLATERAL WAS SENT WITH EACH ACTIVITY. SHOULD I KEEP IT AS ITS OWN TABLE OR JUST MAKE IT A LOOKUP FIELD WITHIN THE ACTIVITIES TABLE? tblProperty pPropertyID (pk) - autonumber pCompanyID (fk) pAddress1 pAddress2 pCityID ****pStateID (Oops forogt that field). THANKS! pPostalCode pPropertyTypeID (fk) pReceivership - yes/no pExistingPrice pCurrentVendorID (fk) pPastTGCBid pInterior - yes/no pBidSchedule - calendar pAreaScheduleID (fk) pNotes - text (255) QUESTIONS --I am a little confused. I want to make sure each property has a contact assigned to it, but I am not sure if you put a contact foreign key in the tblProperty or you put a property foreign key in the tblCompanyContact or tblPropertyContact. ***That is what the tblPropertyContact is for. WILL RESPOND TO THIS BELOW BECAUSE IT COMES UP AGAIN --Did you create the tblPropertyContact in case a contact hasn't already been entered, but you have a new property and you need to assign a contact to it? I am a little confused why I have tblCompanyContact and tblPropertyContact. Fred mentioned that it is important to link a property to a company rather than link it to a contact, which is very helpful once the property contract is won. However, this is a prospective client database so I am not so sure that is the best way to link them. Thoughts? ***No, the Property Contact should be in tblProperty, there is a seperate table for them. Fred is correct (and it is the only way I do it) because Contacts (people) come and go but Companies usually hang around. If the person quits you still want to know what Company to deal with, understand better now? WILL ANSWER BELOW BECAUSE IT ALL RELATES TO THE QUESTION ABOUT HAVING THE 2 DIFFERENT CONTACT TABLES --If possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***Once you answer my question above regarding BidSchedule we will work on table ideas. ANSWERED ABOVE --Same question as before, is there a way to set alerts associated with the pBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***See link above...THANK YOU tblVendors vVendorID (pk) - autonumber vVendorName tblPropertyVendors pvPropertyID (fk) pvVendorID (fk) QUESTIONS --Why is this table (tblPropertyVendors) necessary. All I am looking for with the tblCurrentVendors is who the property currently uses for their commercial landscaping. Who currently holds that contract. I tweaked the previous tblVendors you had to make the tblCurrentVendors because I am not interested in whether they do interior or exterior work, I just want to know who currently services that property. ***No, you will be able to pull that information via query. Because a Vendor is Vendor whether Current or not. MAKES SENSE. ESSENTIALLY, THE VENDORS THAT WILL BE LISTED ARE OUR COMPETITORS. So, I ended up also adding back the pInterior field into the tblProperty because I just want to know (yes or no) whether that property has interior needs. All companies have exterior needs, that is why they are in the database to begin with, but some may also have interior and this is where I would answer that question. Does that work? ***Yes putting that field back in is okay BUT think about tomorrow... Are you SURE you are never going to want to pull seperate lists for Exterior Vendors or Interior Vendors? If the answer is *maybe* then put it back in the Vendor table but you can leave it in the Property table, though I would remove it and leave the tblPropertyVendors. I WON'T EVER WANT TO PULL DIFFERENT LISTS FOR EXTERIOR VENDORS VERSUS INTERIOR VENDORS. NOT REALLY WHAT I AM TRYING TO TRACK. I GET WHERE YOU WERE GOING THOUGH. JUST DON'T THINK IT FITS WHAT I AM TRYING TO DO. tblAreaSchedule asAreaScheduleID (pk) - autonumber asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5) ***Please elaborate what the above table will be used for? AGAIN, THIS MAY BE ONE WHERE I CAN JUST HAVE THE AREA SCHEDULE BE LISTED AS A LOOKUP FIELD WITHIN TBLPROPERTIES. THE AREA SCHEDULE IS A WAY FOR ME TO ASSIGN THE PROPERTY TO AN AREA BECAUSE WE HAVE AREA SUPERVISORS FOR EACH PROPERTY. DOES THAT MAKE SENSE? SHOULD I LEAVE AS A SEPARATE TABLE OR JUST HAVE AS A LOOKUP FIELD? tbl PropertyContact pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last post ***Optional, not *every* table needs a Primary Key. OK pcPropertyID (fk) pcFirstName pcLastName pcClientType (fk) - Don't you need this??? Just like in tblCompanyContact? ***Yes, it is actually to store Property Type but I like all my PK's and FK's to share the same name so I can know by glancing which table it is linked to. pcContactType - yes/no (Main contact or not?) pcDirectPhone pcCellPhone pcEmail pcFaxNumber pcBidSchedule - calendar - Don't you need this? ***I am trying to understand Bid Schedule, see above pcStatus - What is this for??? ***I thought you wanted to know if the Contact is still active or not, if not, can be removed. THAT IS WHAT THE PCCLIENTTYPE IS. THAT WILL SHOW WHETHER THEY ARE CURRENT, PROSPECT, OR HYBRID. I DON'T THINK I ALSO NEED PCSTATUS. DOES THAT MAKE SENSE? QUESTIONS --I sort of asked this in an above question, but can you explain why I need a tblCompanyContact and a tblPropertyContact. I am sure there is a very logical answer but I am just not getting it yet. ***Company Contact store just that and the same for Property Contacts. Each has their own contacts. Perhaps I don't understand your question? I PROBABLY NEED TO DO A BETTER JOB OF EXPLANING. LET ME TRY TO EXPLAIN MORE CLEARLY HOW THE BUSINESS MODEL WORKS AGAIN. OUR PROSPECTIVE CLIENTS ARE COMPANIES. EACH ONE OF THOSE COMPANIES HAS ONE OR MORE CONTACTS WE DEAL WITH. AND EACH OF THOSE CONTACTS MANAGES ONE OR MORE PROPERTIES THAT WE SERVICE. VERY RARELY WILL ONE COMPANY HAVE MORE THAN ONE CONTACT MANAGING A SINGLE PROPERTY. THE COMPANY CONTACTS AND THE PROPERTY CONTACTS ARE THE SAME THING. THAT IS WHY I WAS CONFUSED WHY WE HAD DIFFERENT TABLES FOR EACH SINCE THEY ARE THE SAME. WHEN I IDENTIFY A NEW PROSPECT, I AM LOOKING FOR THE COMPANY INFO, THE CONTACT INFO AND THE PROPERTY INFO. I ALSO WANT TO MAKE SURE I IDENTIFY WHICH CONTACT MANAGES WHICH PROPERTY. I AM NOT SURE IF THIS IS HELPING??? EACH PROPERTY IS UNDER THE COMPANY UMBRELLA, BUT IT IS ALSO LINKED TO A CONTACT THAT IS ALSO UNDER THE COMPANY UMBRELLA. --Will this table be able to do the same main phone and main address query like the tblCompanyContact? ***Yes. GREAT. --Same question as before, if possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***See above. ANSWERED ABOVE. --Same question as before, is there a way to set alerts associated with the pcBidSchedule (if that is even a necessary field in this table)? So our staff is reminded (via email?) of an upcoming bid opening? **See above. GREAT. tblPropertyTypes ptPropertyTypeID (pk) - autonumber ptPropertyType - lookup (Office, Retail, Industrial, Hospitality, Multiple) tblStaff sStaffID (pk) sStaffName - lookup (insert all staff names) tblBids bBidID (pk) - autonumber bPropertyID (fk) bDateIssued bStaffID (fk) bAmount bBidStatusID (fk) tblBidStatus bsBidStatusID (pk) - autonumber bsBidStatusType - lookup (Pending, Won, Lost) tblBidDetails bBidID (fk) bAttachment - text (255) ***No OLE Object data type, you store a path to the file which can be opened. OLE objects quickly fill up your database making it grow and you don't want to hit the 2 gig limit that fast. OK, MAKES SENSE. tblPropertyBids pbPropertyID (fk) pbBidID (fk) QUESTIONS --Why is tblPropertyBids necessary? **Can't a Property have more then one bid? NO. THEY WILL HAVE MORE THAN ONE BID WHEN YOU CONSIDER ALL THE DIFFERENT VENDORS, BUT I ONLY CARE ABOUT THE BIDS WE ISSUE FOR EACH PROPERTY. SO, NO, EACH PROPERTY WILL ONLY HAVE ONE TGC BID. DOES THAT HELP? --Do you need to put a foreign key for the BidID into the tblProperty? Again, this is an example of when I am not sure which table gets the foreign key. ***No, it is linked by PropertyID no need to link by anything else. OK. FUTURE STUFF --Email prospective clients directly in Access ***Can be done. GREAT --Track email correspondence automatically as an activity in tblActivities *** I AM ASSUMING THIS CAN BE DONE AS WELL. SO, GREAT! --Send collateral attachments directly in email within Access ***Can be done as long in predetermined path OR will have to have a Browse for folder option. OK, GREAT --If necessary, link emails sent through Boomerang (3rd party email service) with tblActivities ***Don't know, never used Boomerang. I DON'T KNOW TOO MUCH ABOUT IT EITHER...WILL HAVE TO LEARN MORE AND THEN TACKLE LATER. --Link contact info with Outlook ***While I have not done it, it can be done. OK. --Upload email templates to be used for Access email **Need more information as to what you mean by templates --Populate contact info into email templates. I WOULD WANT TO WRITE A STANDARD EMAIL RESPONSE WITH GENERIC TEXT THAT COULD BE USED FOR ALL CLIENTS AND JUST BE ABLE TO PERSONALIZE IT WITH THE COMPANY, CONTACT, AND PROPERTY INFO FROM THE DATABASE. I JUST WANT TO BE ABLE TO STORE CANNED RESPONSES. --Download company, contact, and property info into MS Word bids and contract templates ***Have a look at... http://www.members.shaw.ca/AlbertKal.../msaccess.html. THANKS! --Download company and contact street address info for mail merge ***See above. GOT IT. --Set an alert to notify sales staff via email when a new prospect is added **Yes can be done. GREAT. -- Potentially build out search option for pPastTGCBid within tblProperty so once bids come back around, I can search past bids within database and have those past bid amounts at my fingertips - Very much a future project. Not something needed now. ***Yes can be done. GREAT! --Build current client side of database - Very much a future project. **Yes can be done. Would actually import in table and create field for Client or Prospect, similar to a Yes/No field. That way they are all in the same place and you can filter if you only want to see one or the other. OK, COOL. |
#34
|
|||
|
|||
Prospective Client Tracking Database
Ellen,
There will be a slight delay in answering... want to eat some dinner (which I do have to cook) but I'll be back... -- 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 ... Awesome! Thanks! I responded to a few things below. Put my answers in CAPS so you could see them. Sorry, I hate CAPS, but probably best way to differentiate on this string... Thanks again Gina! "Gina Whipp" wrote: Ellen, Answers in-line... Look for the 3 asterisks! (Hope I got everything) -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "TGC" wrote in message ... Hi Gina - I had a chance to digest all your helpful info... I have rewritten all the tables I think I need based on the info you provided me. I have included a few questions next to a few fields and have included questions after some tables that apply to the table in general. I also included some future stuff I want to address down the road. I know I am a far way from most, if not all, of these things, but I wanted to lay them out there now in case we needed to set up additional things within the foundation and tables in order to make them work. Thanks again so much for your help! Here it goes... tblCompany cCompanyID (pk) - autonumber cCompanyName cMainPhone cAddress1 cAddress2 cCityID ****cStateID (Oops forogt that field) - DUH, THANKS! cPostalCode cFaxNumber cWebsite cClientTypeID (fk) cPropertyTypeID (fk) tblClientTypes ctClientTypeID (pk) - autonumber ctClientType - lookup (Current, Prospect, Hybrid) tblCompanyContact ccCompanyContactID (pk) - autonumber ccCompanyID (fk) ccFirstName ccLastName ccClientType (fk) ccContactType - yes/no (Main contact or not? ***Yep that is what that is for!) ccDirectPhone ccCellPhone ccEmail ccFaxNumber ccAddress1 ccAddress2 ccCityID ****ccStateID (Oops forogt that field) - SAME. THANKS! ccPostalCode ccBidSchedule - calendar - This will be a date. Does this work? ***If this a many field which means if there are going to be many dates to on COmpany Contact then this will need to broken out... but will wait for you answer. THIS IS HOW I WANT TO USE THE BID SCHEDULES. EACH PROPERTY IS UNDER CONTRACT FOR THEIR LANDSCAPING. AND OBVIOUSLY THOSE CONTRACTS EXPIRE SO COMPANIES GO TO BID. SOMETIMES A COMPANY CONTACT BIDS ALL THEIR PROPERTIES AT THE SAME TIME OR SOMETIMES EACH PROPERTY HAS A DIFFERENT BID DATE. I WANT TO BE ABLE TO TRACK THAT AND BE ABLE TO SET ALERTS TO REMIND US THAT THOSE DATES ARE APPROACHING SO WE REMEMBER TO REACH OUT TO THEM VIA THE DIFFERENT ACTIVITIES OR SUBMIT A BID AT THE RIGHT TIME. SO, I WANT TO HAVE A BID SCHEDULE FIELD (WHICH WILL SIMPLY BE A DATE) FOR THE COMPANY CONTACTS AND FOR EACH PROPERTY. I WANT TO HAVE THE OPTION ON THE PROPERTY TO CHOOSE THE COMPANY CONTACT BID SCHEDULE DATE OR ENTER A NEW ONE IF THEY ARE DIFFERENT. SO, ESSENTIALLY THERE IS JUST ONE DATE THAT WILL SHOW UP IN THE BID SCHEDULE FIELDS. THEY MAY BE DIFFERENT DATES, BUT THEY WILL BE SINGULAR DATES ALWAYS. DOES THAT HELP? QUESTIONS --Query will determine if the contact is the main contact. If yes, the company main phone will show along with direct and cell phone options. If no, only the direct and cell phone options will appear. Same thing for contact address. Query will determine if the contact is at the main company address. If yes, the company address will populate. If not, the address fields will remain blank to be filled in. Is that correct? ***Yes that is correct. COOL. --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. MAKES SENSE --Is there a way to set alerts associated with the ccBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***Yes and here's a link to get you started when you are ready... THANK YOU! http://www.rogersaccesslibrary.com/f...aa c8za637888 tblActivities aDate - calendar aActivityTypeID (fk) aStaffID (fk) aCollateralTypeID (fk) aNotes - text aDone - yes/no tblActivityTypes atActivityTypeID (pk) - autonumber atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email, Mail, Fax) QUESTIONS --Is there a way to set an activities schedule reminder to alert staff to follow up with a prospect? ***See above link. THANK YOU tblCollateralTypes ctCollateralTypeID(pk) - autonumber ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick Brochure, Complete Marketing Package) ***Please give me a little background on how you plan to use the above table. THIS TABLE MAY NOT BE NECESSARY. IT MAY JUST NEED TO BE A LOOKUP FIELD WITH THE DIFFERENT COLLATERAL TYPES AS THE LOOKUP OPTIONS WITHIN TBLACTIVITIES. I JUST WANT TO BE ABLE TO ASSIGN WHAT TYPE OF COLLATERAL WAS SENT WITH EACH ACTIVITY. SHOULD I KEEP IT AS ITS OWN TABLE OR JUST MAKE IT A LOOKUP FIELD WITHIN THE ACTIVITIES TABLE? tblProperty pPropertyID (pk) - autonumber pCompanyID (fk) pAddress1 pAddress2 pCityID ****pStateID (Oops forogt that field). THANKS! pPostalCode pPropertyTypeID (fk) pReceivership - yes/no pExistingPrice pCurrentVendorID (fk) pPastTGCBid pInterior - yes/no pBidSchedule - calendar pAreaScheduleID (fk) pNotes - text (255) QUESTIONS --I am a little confused. I want to make sure each property has a contact assigned to it, but I am not sure if you put a contact foreign key in the tblProperty or you put a property foreign key in the tblCompanyContact or tblPropertyContact. ***That is what the tblPropertyContact is for. WILL RESPOND TO THIS BELOW BECAUSE IT COMES UP AGAIN --Did you create the tblPropertyContact in case a contact hasn't already been entered, but you have a new property and you need to assign a contact to it? I am a little confused why I have tblCompanyContact and tblPropertyContact. Fred mentioned that it is important to link a property to a company rather than link it to a contact, which is very helpful once the property contract is won. However, this is a prospective client database so I am not so sure that is the best way to link them. Thoughts? ***No, the Property Contact should be in tblProperty, there is a seperate table for them. Fred is correct (and it is the only way I do it) because Contacts (people) come and go but Companies usually hang around. If the person quits you still want to know what Company to deal with, understand better now? WILL ANSWER BELOW BECAUSE IT ALL RELATES TO THE QUESTION ABOUT HAVING THE 2 DIFFERENT CONTACT TABLES --If possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***Once you answer my question above regarding BidSchedule we will work on table ideas. ANSWERED ABOVE --Same question as before, is there a way to set alerts associated with the pBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***See link above...THANK YOU tblVendors vVendorID (pk) - autonumber vVendorName tblPropertyVendors pvPropertyID (fk) pvVendorID (fk) QUESTIONS --Why is this table (tblPropertyVendors) necessary. All I am looking for with the tblCurrentVendors is who the property currently uses for their commercial landscaping. Who currently holds that contract. I tweaked the previous tblVendors you had to make the tblCurrentVendors because I am not interested in whether they do interior or exterior work, I just want to know who currently services that property. ***No, you will be able to pull that information via query. Because a Vendor is Vendor whether Current or not. MAKES SENSE. ESSENTIALLY, THE VENDORS THAT WILL BE LISTED ARE OUR COMPETITORS. So, I ended up also adding back the pInterior field into the tblProperty because I just want to know (yes or no) whether that property has interior needs. All companies have exterior needs, that is why they are in the database to begin with, but some may also have interior and this is where I would answer that question. Does that work? ***Yes putting that field back in is okay BUT think about tomorrow... Are you SURE you are never going to want to pull seperate lists for Exterior Vendors or Interior Vendors? If the answer is *maybe* then put it back in the Vendor table but you can leave it in the Property table, though I would remove it and leave the tblPropertyVendors. I WON'T EVER WANT TO PULL DIFFERENT LISTS FOR EXTERIOR VENDORS VERSUS INTERIOR VENDORS. NOT REALLY WHAT I AM TRYING TO TRACK. I GET WHERE YOU WERE GOING THOUGH. JUST DON'T THINK IT FITS WHAT I AM TRYING TO DO. tblAreaSchedule asAreaScheduleID (pk) - autonumber asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5) ***Please elaborate what the above table will be used for? AGAIN, THIS MAY BE ONE WHERE I CAN JUST HAVE THE AREA SCHEDULE BE LISTED AS A LOOKUP FIELD WITHIN TBLPROPERTIES. THE AREA SCHEDULE IS A WAY FOR ME TO ASSIGN THE PROPERTY TO AN AREA BECAUSE WE HAVE AREA SUPERVISORS FOR EACH PROPERTY. DOES THAT MAKE SENSE? SHOULD I LEAVE AS A SEPARATE TABLE OR JUST HAVE AS A LOOKUP FIELD? tbl PropertyContact pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last post ***Optional, not *every* table needs a Primary Key. OK pcPropertyID (fk) pcFirstName pcLastName pcClientType (fk) - Don't you need this??? Just like in tblCompanyContact? ***Yes, it is actually to store Property Type but I like all my PK's and FK's to share the same name so I can know by glancing which table it is linked to. pcContactType - yes/no (Main contact or not?) pcDirectPhone pcCellPhone pcEmail pcFaxNumber pcBidSchedule - calendar - Don't you need this? ***I am trying to understand Bid Schedule, see above pcStatus - What is this for??? ***I thought you wanted to know if the Contact is still active or not, if not, can be removed. THAT IS WHAT THE PCCLIENTTYPE IS. THAT WILL SHOW WHETHER THEY ARE CURRENT, PROSPECT, OR HYBRID. I DON'T THINK I ALSO NEED PCSTATUS. DOES THAT MAKE SENSE? QUESTIONS --I sort of asked this in an above question, but can you explain why I need a tblCompanyContact and a tblPropertyContact. I am sure there is a very logical answer but I am just not getting it yet. ***Company Contact store just that and the same for Property Contacts. Each has their own contacts. Perhaps I don't understand your question? I PROBABLY NEED TO DO A BETTER JOB OF EXPLANING. LET ME TRY TO EXPLAIN MORE CLEARLY HOW THE BUSINESS MODEL WORKS AGAIN. OUR PROSPECTIVE CLIENTS ARE COMPANIES. EACH ONE OF THOSE COMPANIES HAS ONE OR MORE CONTACTS WE DEAL WITH. AND EACH OF THOSE CONTACTS MANAGES ONE OR MORE PROPERTIES THAT WE SERVICE. VERY RARELY WILL ONE COMPANY HAVE MORE THAN ONE CONTACT MANAGING A SINGLE PROPERTY. THE COMPANY CONTACTS AND THE PROPERTY CONTACTS ARE THE SAME THING. THAT IS WHY I WAS CONFUSED WHY WE HAD DIFFERENT TABLES FOR EACH SINCE THEY ARE THE SAME. WHEN I IDENTIFY A NEW PROSPECT, I AM LOOKING FOR THE COMPANY INFO, THE CONTACT INFO AND THE PROPERTY INFO. I ALSO WANT TO MAKE SURE I IDENTIFY WHICH CONTACT MANAGES WHICH PROPERTY. I AM NOT SURE IF THIS IS HELPING??? EACH PROPERTY IS UNDER THE COMPANY UMBRELLA, BUT IT IS ALSO LINKED TO A CONTACT THAT IS ALSO UNDER THE COMPANY UMBRELLA. --Will this table be able to do the same main phone and main address query like the tblCompanyContact? ***Yes. GREAT. --Same question as before, if possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pcBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***See above. ANSWERED ABOVE. --Same question as before, is there a way to set alerts associated with the pcBidSchedule (if that is even a necessary field in this table)? So our staff is reminded (via email?) of an upcoming bid opening? **See above. GREAT. tblPropertyTypes ptPropertyTypeID (pk) - autonumber ptPropertyType - lookup (Office, Retail, Industrial, Hospitality, Multiple) tblStaff sStaffID (pk) sStaffName - lookup (insert all staff names) tblBids bBidID (pk) - autonumber bPropertyID (fk) bDateIssued bStaffID (fk) bAmount bBidStatusID (fk) tblBidStatus bsBidStatusID (pk) - autonumber bsBidStatusType - lookup (Pending, Won, Lost) tblBidDetails bBidID (fk) bAttachment - text (255) ***No OLE Object data type, you store a path to the file which can be opened. OLE objects quickly fill up your database making it grow and you don't want to hit the 2 gig limit that fast. OK, MAKES SENSE. tblPropertyBids pbPropertyID (fk) pbBidID (fk) QUESTIONS --Why is tblPropertyBids necessary? **Can't a Property have more then one bid? NO. THEY WILL HAVE MORE THAN ONE BID WHEN YOU CONSIDER ALL THE DIFFERENT VENDORS, BUT I ONLY CARE ABOUT THE BIDS WE ISSUE FOR EACH PROPERTY. SO, NO, EACH PROPERTY WILL ONLY HAVE ONE TGC BID. DOES THAT HELP? --Do you need to put a foreign key for the BidID into the tblProperty? Again, this is an example of when I am not sure which table gets the foreign key. ***No, it is linked by PropertyID no need to link by anything else. OK. FUTURE STUFF --Email prospective clients directly in Access ***Can be done. GREAT --Track email correspondence automatically as an activity in tblActivities *** I AM ASSUMING THIS CAN BE DONE AS WELL. SO, GREAT! --Send collateral attachments directly in email within Access ***Can be done as long in predetermined path OR will have to have a Browse for folder option. OK, GREAT --If necessary, link emails sent through Boomerang (3rd party email service) with tblActivities ***Don't know, never used Boomerang. I DON'T KNOW TOO MUCH ABOUT IT EITHER...WILL HAVE TO LEARN MORE AND THEN TACKLE LATER. --Link contact info with Outlook ***While I have not done it, it can be done. OK. --Upload email templates to be used for Access email **Need more information as to what you mean by templates --Populate contact info into email templates. I WOULD WANT TO WRITE A STANDARD EMAIL RESPONSE WITH GENERIC TEXT THAT COULD BE USED FOR ALL CLIENTS AND JUST BE ABLE TO PERSONALIZE IT WITH THE COMPANY, CONTACT, AND PROPERTY INFO FROM THE DATABASE. I JUST WANT TO BE ABLE TO STORE CANNED RESPONSES. --Download company, contact, and property info into MS Word bids and contract templates ***Have a look at... http://www.members.shaw.ca/AlbertKal.../msaccess.html. THANKS! --Download company and contact street address info for mail merge ***See above. GOT IT. --Set an alert to notify sales staff via email when a new prospect is added **Yes can be done. GREAT. -- Potentially build out search option for pPastTGCBid within tblProperty so once bids come back around, I can search past bids within database and have those past bid amounts at my fingertips - Very much a future project. Not something needed now. ***Yes can be done. GREAT! --Build current client side of database - Very much a future project. **Yes can be done. Would actually import in table and create field for Client or Prospect, similar to a Yes/No field. That way they are all in the same place and you can filter if you only want to see one or the other. OK, COOL. |
#35
|
|||
|
|||
Prospective Client Tracking Database
On Tue, 26 Jan 2010 15:29:17 -0500, "Gina Whipp"
wrote: --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. I'll just jump in briefly with a suggestion. Cities are one of the very few tables for which we don't link with foreign keys or enforce referential integrity. As Gina suggests, we just let people type in the City name - it becomes data-entry assistance rather than a rigid relationship. - we create tblCity - we create relationships from any table with an address to tblCity using CityName, without enforcing referential integrity. - on our forms, the City combobox helps users remember the spelling of the city and type it in faster. - the City combobox traps for NotInList and silently adds a new City on the fly. - we build a simple admin screen to allow the user to delete incorrect cities. They should also find and correct those cities in the address tables. Cheers, Armen Stein Microsoft Access MVP www.JStreetTech.com |
#36
|
|||
|
|||
Prospective Client Tracking Database
Armen,
Great point! It is true no referential integrity, just to prevent typos! Glad you brought that up, jump in anytime! -- 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 "Armen Stein" wrote in message ... On Tue, 26 Jan 2010 15:29:17 -0500, "Gina Whipp" wrote: --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. I'll just jump in briefly with a suggestion. Cities are one of the very few tables for which we don't link with foreign keys or enforce referential integrity. As Gina suggests, we just let people type in the City name - it becomes data-entry assistance rather than a rigid relationship. - we create tblCity - we create relationships from any table with an address to tblCity using CityName, without enforcing referential integrity. - on our forms, the City combobox helps users remember the spelling of the city and type it in faster. - the City combobox traps for NotInList and silently adds a new City on the fly. - we build a simple admin screen to allow the user to delete incorrect cities. They should also find and correct those cities in the address tables. Cheers, Armen Stein Microsoft Access MVP www.JStreetTech.com |
#37
|
|||
|
|||
Prospective Client Tracking Database
That is helpful. After reading Gina's response on that question I thought
about having some kind of autofill option where when the user starts typing in the city name, the field starts filling it in (similar to google). It sounds like that is what you are suggesting. Is that correct? "Armen Stein" wrote: On Tue, 26 Jan 2010 15:29:17 -0500, "Gina Whipp" wrote: --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. I'll just jump in briefly with a suggestion. Cities are one of the very few tables for which we don't link with foreign keys or enforce referential integrity. As Gina suggests, we just let people type in the City name - it becomes data-entry assistance rather than a rigid relationship. - we create tblCity - we create relationships from any table with an address to tblCity using CityName, without enforcing referential integrity. - on our forms, the City combobox helps users remember the spelling of the city and type it in faster. - the City combobox traps for NotInList and silently adds a new City on the fly. - we build a simple admin screen to allow the user to delete incorrect cities. They should also find and correct those cities in the address tables. Cheers, Armen Stein Microsoft Access MVP www.JStreetTech.com . |
#38
|
|||
|
|||
Prospective Client Tracking Database
Haha....no problem. I am only in the office on Tuesdays and Thursdays
anyways so that is the only time I will be working on this. Thanks again! "Gina Whipp" wrote: Ellen, There will be a slight delay in answering... want to eat some dinner (which I do have to cook) but I'll be back... -- 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 ... Awesome! Thanks! I responded to a few things below. Put my answers in CAPS so you could see them. Sorry, I hate CAPS, but probably best way to differentiate on this string... Thanks again Gina! "Gina Whipp" wrote: Ellen, Answers in-line... Look for the 3 asterisks! (Hope I got everything) -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "TGC" wrote in message ... Hi Gina - I had a chance to digest all your helpful info... I have rewritten all the tables I think I need based on the info you provided me. I have included a few questions next to a few fields and have included questions after some tables that apply to the table in general. I also included some future stuff I want to address down the road. I know I am a far way from most, if not all, of these things, but I wanted to lay them out there now in case we needed to set up additional things within the foundation and tables in order to make them work. Thanks again so much for your help! Here it goes... tblCompany cCompanyID (pk) - autonumber cCompanyName cMainPhone cAddress1 cAddress2 cCityID ****cStateID (Oops forogt that field) - DUH, THANKS! cPostalCode cFaxNumber cWebsite cClientTypeID (fk) cPropertyTypeID (fk) tblClientTypes ctClientTypeID (pk) - autonumber ctClientType - lookup (Current, Prospect, Hybrid) tblCompanyContact ccCompanyContactID (pk) - autonumber ccCompanyID (fk) ccFirstName ccLastName ccClientType (fk) ccContactType - yes/no (Main contact or not? ***Yep that is what that is for!) ccDirectPhone ccCellPhone ccEmail ccFaxNumber ccAddress1 ccAddress2 ccCityID ****ccStateID (Oops forogt that field) - SAME. THANKS! ccPostalCode ccBidSchedule - calendar - This will be a date. Does this work? ***If this a many field which means if there are going to be many dates to on COmpany Contact then this will need to broken out... but will wait for you answer. THIS IS HOW I WANT TO USE THE BID SCHEDULES. EACH PROPERTY IS UNDER CONTRACT FOR THEIR LANDSCAPING. AND OBVIOUSLY THOSE CONTRACTS EXPIRE SO COMPANIES GO TO BID. SOMETIMES A COMPANY CONTACT BIDS ALL THEIR PROPERTIES AT THE SAME TIME OR SOMETIMES EACH PROPERTY HAS A DIFFERENT BID DATE. I WANT TO BE ABLE TO TRACK THAT AND BE ABLE TO SET ALERTS TO REMIND US THAT THOSE DATES ARE APPROACHING SO WE REMEMBER TO REACH OUT TO THEM VIA THE DIFFERENT ACTIVITIES OR SUBMIT A BID AT THE RIGHT TIME. SO, I WANT TO HAVE A BID SCHEDULE FIELD (WHICH WILL SIMPLY BE A DATE) FOR THE COMPANY CONTACTS AND FOR EACH PROPERTY. I WANT TO HAVE THE OPTION ON THE PROPERTY TO CHOOSE THE COMPANY CONTACT BID SCHEDULE DATE OR ENTER A NEW ONE IF THEY ARE DIFFERENT. SO, ESSENTIALLY THERE IS JUST ONE DATE THAT WILL SHOW UP IN THE BID SCHEDULE FIELDS. THEY MAY BE DIFFERENT DATES, BUT THEY WILL BE SINGULAR DATES ALWAYS. DOES THAT HELP? QUESTIONS --Query will determine if the contact is the main contact. If yes, the company main phone will show along with direct and cell phone options. If no, only the direct and cell phone options will appear. Same thing for contact address. Query will determine if the contact is at the main company address. If yes, the company address will populate. If not, the address fields will remain blank to be filled in. Is that correct? ***Yes that is correct. COOL. --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. MAKES SENSE --Is there a way to set alerts associated with the ccBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***Yes and here's a link to get you started when you are ready... THANK YOU! http://www.rogersaccesslibrary.com/f...aa c8za637888 tblActivities aDate - calendar aActivityTypeID (fk) aStaffID (fk) aCollateralTypeID (fk) aNotes - text aDone - yes/no tblActivityTypes atActivityTypeID (pk) - autonumber atActivityType - lookup (Phone Conversation, Phone VM, Meeting, Email, Mail, Fax) QUESTIONS --Is there a way to set an activities schedule reminder to alert staff to follow up with a prospect? ***See above link. THANK YOU tblCollateralTypes ctCollateralTypeID(pk) - autonumber ctCollateralType - lookup (Newsletter, Sustainable Brochure, Stick Brochure, Complete Marketing Package) ***Please give me a little background on how you plan to use the above table. THIS TABLE MAY NOT BE NECESSARY. IT MAY JUST NEED TO BE A LOOKUP FIELD WITH THE DIFFERENT COLLATERAL TYPES AS THE LOOKUP OPTIONS WITHIN TBLACTIVITIES. I JUST WANT TO BE ABLE TO ASSIGN WHAT TYPE OF COLLATERAL WAS SENT WITH EACH ACTIVITY. SHOULD I KEEP IT AS ITS OWN TABLE OR JUST MAKE IT A LOOKUP FIELD WITHIN THE ACTIVITIES TABLE? tblProperty pPropertyID (pk) - autonumber pCompanyID (fk) pAddress1 pAddress2 pCityID ****pStateID (Oops forogt that field). THANKS! pPostalCode pPropertyTypeID (fk) pReceivership - yes/no pExistingPrice pCurrentVendorID (fk) pPastTGCBid pInterior - yes/no pBidSchedule - calendar pAreaScheduleID (fk) pNotes - text (255) QUESTIONS --I am a little confused. I want to make sure each property has a contact assigned to it, but I am not sure if you put a contact foreign key in the tblProperty or you put a property foreign key in the tblCompanyContact or tblPropertyContact. ***That is what the tblPropertyContact is for. WILL RESPOND TO THIS BELOW BECAUSE IT COMES UP AGAIN --Did you create the tblPropertyContact in case a contact hasn't already been entered, but you have a new property and you need to assign a contact to it? I am a little confused why I have tblCompanyContact and tblPropertyContact. Fred mentioned that it is important to link a property to a company rather than link it to a contact, which is very helpful once the property contract is won. However, this is a prospective client database so I am not so sure that is the best way to link them. Thoughts? ***No, the Property Contact should be in tblProperty, there is a seperate table for them. Fred is correct (and it is the only way I do it) because Contacts (people) come and go but Companies usually hang around. If the person quits you still want to know what Company to deal with, understand better now? WILL ANSWER BELOW BECAUSE IT ALL RELATES TO THE QUESTION ABOUT HAVING THE 2 DIFFERENT CONTACT TABLES --If possible, I would like to work with the ccBidSchedule. Similar to what you did with the main phone number situation with the tblCompany and tblCompanyContact.I should be able to choose if the pBidScheule is the same as the ccBidSchedule. If yes, it will populate in this area. If no, I will have the option to choose a new date. ***Once you answer my question above regarding BidSchedule we will work on table ideas. ANSWERED ABOVE --Same question as before, is there a way to set alerts associated with the pBidSchedule? So our staff is reminded (via email?) of an upcoming bid opening? ***See link above...THANK YOU tblVendors vVendorID (pk) - autonumber vVendorName tblPropertyVendors pvPropertyID (fk) pvVendorID (fk) QUESTIONS --Why is this table (tblPropertyVendors) necessary. All I am looking for with the tblCurrentVendors is who the property currently uses for their commercial landscaping. Who currently holds that contract. I tweaked the previous tblVendors you had to make the tblCurrentVendors because I am not interested in whether they do interior or exterior work, I just want to know who currently services that property. ***No, you will be able to pull that information via query. Because a Vendor is Vendor whether Current or not. MAKES SENSE. ESSENTIALLY, THE VENDORS THAT WILL BE LISTED ARE OUR COMPETITORS. So, I ended up also adding back the pInterior field into the tblProperty because I just want to know (yes or no) whether that property has interior needs. All companies have exterior needs, that is why they are in the database to begin with, but some may also have interior and this is where I would answer that question. Does that work? ***Yes putting that field back in is okay BUT think about tomorrow... Are you SURE you are never going to want to pull seperate lists for Exterior Vendors or Interior Vendors? If the answer is *maybe* then put it back in the Vendor table but you can leave it in the Property table, though I would remove it and leave the tblPropertyVendors. I WON'T EVER WANT TO PULL DIFFERENT LISTS FOR EXTERIOR VENDORS VERSUS INTERIOR VENDORS. NOT REALLY WHAT I AM TRYING TO TRACK. I GET WHERE YOU WERE GOING THOUGH. JUST DON'T THINK IT FITS WHAT I AM TRYING TO DO. tblAreaSchedule asAreaScheduleID (pk) - autonumber asAreaSchedule - lookup (Area 1, Area 2, Area 3, Area 4, Area 5) ***Please elaborate what the above table will be used for? AGAIN, THIS MAY BE ONE WHERE I CAN JUST HAVE THE AREA SCHEDULE BE LISTED AS A LOOKUP FIELD WITHIN TBLPROPERTIES. THE AREA SCHEDULE IS A WAY FOR ME TO ASSIGN THE PROPERTY TO AN AREA BECAUSE WE HAVE AREA SUPERVISORS FOR EACH PROPERTY. DOES THAT MAKE SENSE? SHOULD I LEAVE AS A SEPARATE TABLE OR JUST HAVE AS A LOOKUP FIELD? tbl PropertyContact pcPropertyContactID (pk) - Don't you need this???? It wasn't in your last post ***Optional, not *every* table needs a Primary Key. OK pcPropertyID (fk) pcFirstName pcLastName |
#39
|
|||
|
|||
Prospective Client Tracking Database
Ellen,
That is what we are suggesting but with the table set up so they can't make a typo. -- 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 ... That is helpful. After reading Gina's response on that question I thought about having some kind of autofill option where when the user starts typing in the city name, the field starts filling it in (similar to google). It sounds like that is what you are suggesting. Is that correct? "Armen Stein" wrote: On Tue, 26 Jan 2010 15:29:17 -0500, "Gina Whipp" wrote: --Do I need to create a CityID table? I have CityID a few places in different tables but haven't created a CityID table yet. ***I use one because you'd be surprised how many typos people can make when typing their own city name. So yes, you need a City table should you decide to go that route OR you could change it to CityName and let people type. I'll just jump in briefly with a suggestion. Cities are one of the very few tables for which we don't link with foreign keys or enforce referential integrity. As Gina suggests, we just let people type in the City name - it becomes data-entry assistance rather than a rigid relationship. - we create tblCity - we create relationships from any table with an address to tblCity using CityName, without enforcing referential integrity. - on our forms, the City combobox helps users remember the spelling of the city and type it in faster. - the City combobox traps for NotInList and silently adds a new City on the fly. - we build a simple admin screen to allow the user to delete incorrect cities. They should also find and correct those cities in the address tables. Cheers, Armen Stein Microsoft Access MVP www.JStreetTech.com . |
#40
|
|||
|
|||
Prospective Client Tracking Database
On Tue, 26 Jan 2010 19:04:59 -0500, "Gina Whipp"
wrote: That is what we are suggesting but with the table set up so they can't make a typo. Yes. A combobox has a built-in ability to display the whole value of the first match it finds, which makes data entry much quicker. However, the way we do it just discourages typos, it doesn't *prevent* them. If they type a new city name, we add it to the lookup table automatically and silently. So it's possible to add a misspelled city. It's just not that big of a deal to remove it later, and we find that when a correctly spelled city is in there already, people tend to just use it and not get creative with a new spelling. You typically need some kind of add-on-the-fly because new cities will need to be added all the time. You could prompt the user in the Not In List event to see if they want to add a new city, but we don't want to slow down the data entry process. So we just add it in there without asking. Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|