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 |
#1
|
|||
|
|||
Prospective Client Tracking Database
I am new to Access 2007 and recently reviewed all the online tutorials on the
Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! |
#3
|
|||
|
|||
Prospective Client Tracking Database
On Tue, 19 Jan 2010 12:21:01 -0800, TGC wrote:
I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! I would recommend at least some changes. Your basic table structures look ok for a start... but! Names are NOT good keys or linking fields. A Key should meet three requirements: it *must* be unique; it should be stable, not changing over time; and ideally it should be short. People's names fail on all three counts! The Primary Key of your Companies table should be a numeric, meaningless unique identifier, such as an Autonumber (CompanyID let's call it). This would be related to a Long Integer CompanyID in the Contacts table. Similarly, each Contact should have a numeric ContactID; otherwise you might have Dale Williams, the big husky guy, and Dale Williams, the nice grey-haired lady, getting their properties mixed up. You also need to be sure that your relationships reflect the business logic. Is a Property in fact managed *by an individual contact* or by a company? Might a property have more than one person who you might need to contact? Also be sure you're not storing data redundantly. Phone, email, address need to be in the table for the entity (person or company) that uses them, and should NOT be duplicated in any other table; a property probably does not have an email address, for example. Note also - you may be doing this correctly, but it wasn't clear - that the "foreign key" goes in the Many table. That is, there should be a CompanyID in the Contacts table, but there should not be any information about the Contact in the Companies table. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Prospective Client Tracking Database
That makes sense!
Here is what I have so far based on what you started... TblCompany CompanyID CompanyName Street City Zip Phone Fax Website Status Type Receivership TblCompanyContactID CompanyID FirstName LastName Status BusinessPhone (the phone from the company table would be populated in this field as an option-only want the phone numbers listed within the applicable company though, not all numbers entered into database) OfficePhone CellPhone Fax Correspondence BidSchedule TblProperty PropertyID Street City Zip ContactName (the contact first and last name from the contact table would be populated in this field as an option-only want the contacts listed within the applicable company though, not all contacts entered into database) Type Receivership CurrentPrice CurrentVendor PastTGCBid Interior BidSchedule AreaSchedule Notes There are a few fields named the same in different tables...those are not automatically the same unless noted in my notes above where I want the options to populate for me to choose from. From here, I obviously need to assign relationships and then I want to create a form. I started one with the form wizard where the company table is the main form and the contact and property tables are subforms. It is aesthetically what I am looking for, but a few of my relationships must not be right because the contacts and properties I enter are not being associated with just the one company I want them associated with. Within each company, there can be multiple contacts and multiple properties, but each contact and each property should only be assigned to one company. Hopefully that makes sense???? I also did not use your final "CompanyContactProperty" table when I first started so that is probably where my problem is. Thank you so much for your help! "Steve" wrote: Conside starting with these tables: TblCompany CompanyID CompanyName address fields other company attribute fields TblCompanyContact CompanyContactID CompanyID FirstName LastName other contact fields TblPropery PropertyID property description fields property location fields TblCompanyContactProperty CompanyContactPropertyID CompanyContactID PropertyID other attribute fields, other contact fields, property description fields and property location fields need to be refined. Start with postiing your other company attribute fields and we will help you with the normalized design of your tables. Steve "TGC" wrote in message ... I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! . |
#5
|
|||
|
|||
Prospective Client Tracking Database
TGC,
Let's stay away from those Reserved Words,ie: Type... as Access doesn't respond well which will cause you problems. For a complete list see... http://allenbrowne.com/Ap****ueBadWord.html And you probably want to add CompanyID to your tblProperty. -- 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 makes sense! Here is what I have so far based on what you started... TblCompany CompanyID CompanyName Street City Zip Phone Fax Website Status Type Receivership TblCompanyContactID CompanyID FirstName LastName Status BusinessPhone (the phone from the company table would be populated in this field as an option-only want the phone numbers listed within the applicable company though, not all numbers entered into database) OfficePhone CellPhone Fax Correspondence BidSchedule TblProperty PropertyID Street City Zip ContactName (the contact first and last name from the contact table would be populated in this field as an option-only want the contacts listed within the applicable company though, not all contacts entered into database) Type Receivership CurrentPrice CurrentVendor PastTGCBid Interior BidSchedule AreaSchedule Notes There are a few fields named the same in different tables...those are not automatically the same unless noted in my notes above where I want the options to populate for me to choose from. From here, I obviously need to assign relationships and then I want to create a form. I started one with the form wizard where the company table is the main form and the contact and property tables are subforms. It is aesthetically what I am looking for, but a few of my relationships must not be right because the contacts and properties I enter are not being associated with just the one company I want them associated with. Within each company, there can be multiple contacts and multiple properties, but each contact and each property should only be assigned to one company. Hopefully that makes sense???? I also did not use your final "CompanyContactProperty" table when I first started so that is probably where my problem is. Thank you so much for your help! "Steve" wrote: Conside starting with these tables: TblCompany CompanyID CompanyName address fields other company attribute fields TblCompanyContact CompanyContactID CompanyID FirstName LastName other contact fields TblPropery PropertyID property description fields property location fields TblCompanyContactProperty CompanyContactPropertyID CompanyContactID PropertyID other attribute fields, other contact fields, property description fields and property location fields need to be refined. Start with postiing your other company attribute fields and we will help you with the normalized design of your tables. Steve "TGC" wrote in message ... I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! . |
#6
|
|||
|
|||
Prospective Client Tracking Database
Thank you for the feedback John. That all makes sense!
"John W. Vinson" wrote: On Tue, 19 Jan 2010 12:21:01 -0800, TGC wrote: I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! I would recommend at least some changes. Your basic table structures look ok for a start... but! Names are NOT good keys or linking fields. A Key should meet three requirements: it *must* be unique; it should be stable, not changing over time; and ideally it should be short. People's names fail on all three counts! The Primary Key of your Companies table should be a numeric, meaningless unique identifier, such as an Autonumber (CompanyID let's call it). This would be related to a Long Integer CompanyID in the Contacts table. Similarly, each Contact should have a numeric ContactID; otherwise you might have Dale Williams, the big husky guy, and Dale Williams, the nice grey-haired lady, getting their properties mixed up. You also need to be sure that your relationships reflect the business logic. Is a Property in fact managed *by an individual contact* or by a company? Might a property have more than one person who you might need to contact? Also be sure you're not storing data redundantly. Phone, email, address need to be in the table for the entity (person or company) that uses them, and should NOT be duplicated in any other table; a property probably does not have an email address, for example. Note also - you may be doing this correctly, but it wasn't clear - that the "foreign key" goes in the Many table. That is, there should be a CompanyID in the Contacts table, but there should not be any information about the Contact in the Companies table. -- John W. Vinson [MVP] . |
#7
|
|||
|
|||
Prospective Client Tracking Database
And you probably want to add CompanyID to your tblProperty
NO-oooo!!! That is wrong!!!! Steve "Gina Whipp" wrote in message ... TGC, Let's stay away from those Reserved Words,ie: Type... as Access doesn't respond well which will cause you problems. For a complete list see... http://allenbrowne.com/Ap****ueBadWord.html And you probably want to add CompanyID to your tblProperty. -- 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 makes sense! Here is what I have so far based on what you started... TblCompany CompanyID CompanyName Street City Zip Phone Fax Website Status Type Receivership TblCompanyContactID CompanyID FirstName LastName Status BusinessPhone (the phone from the company table would be populated in this field as an option-only want the phone numbers listed within the applicable company though, not all numbers entered into database) OfficePhone CellPhone Fax Correspondence BidSchedule TblProperty PropertyID Street City Zip ContactName (the contact first and last name from the contact table would be populated in this field as an option-only want the contacts listed within the applicable company though, not all contacts entered into database) Type Receivership CurrentPrice CurrentVendor PastTGCBid Interior BidSchedule AreaSchedule Notes There are a few fields named the same in different tables...those are not automatically the same unless noted in my notes above where I want the options to populate for me to choose from. From here, I obviously need to assign relationships and then I want to create a form. I started one with the form wizard where the company table is the main form and the contact and property tables are subforms. It is aesthetically what I am looking for, but a few of my relationships must not be right because the contacts and properties I enter are not being associated with just the one company I want them associated with. Within each company, there can be multiple contacts and multiple properties, but each contact and each property should only be assigned to one company. Hopefully that makes sense???? I also did not use your final "CompanyContactProperty" table when I first started so that is probably where my problem is. Thank you so much for your help! "Steve" wrote: Conside starting with these tables: TblCompany CompanyID CompanyName address fields other company attribute fields TblCompanyContact CompanyContactID CompanyID FirstName LastName other contact fields TblPropery PropertyID property description fields property location fields TblCompanyContactProperty CompanyContactPropertyID CompanyContactID PropertyID other attribute fields, other contact fields, property description fields and property location fields need to be refined. Start with postiing your other company attribute fields and we will help you with the normalized design of your tables. Steve "TGC" wrote in message ... I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! . |
#8
|
|||
|
|||
Prospective Client Tracking Database
Steve,
Not sure why you jumping p and down. Does not each Property have a company assigned? And then each Company have multiple Contacts? That is what I read... tblProperty (1 to 1) tblCompany (1 to many) tblCompanyContacts pCompanyID (FK) pCompanyID (FK) ccCompanyID (FK) -- 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 "Steve" wrote in message ... And you probably want to add CompanyID to your tblProperty NO-oooo!!! That is wrong!!!! Steve "Gina Whipp" wrote in message ... TGC, Let's stay away from those Reserved Words,ie: Type... as Access doesn't respond well which will cause you problems. For a complete list see... http://allenbrowne.com/Ap****ueBadWord.html And you probably want to add CompanyID to your tblProperty. -- 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 makes sense! Here is what I have so far based on what you started... TblCompany CompanyID CompanyName Street City Zip Phone Fax Website Status Type Receivership TblCompanyContactID CompanyID FirstName LastName Status BusinessPhone (the phone from the company table would be populated in this field as an option-only want the phone numbers listed within the applicable company though, not all numbers entered into database) OfficePhone CellPhone Fax Correspondence BidSchedule TblProperty PropertyID Street City Zip ContactName (the contact first and last name from the contact table would be populated in this field as an option-only want the contacts listed within the applicable company though, not all contacts entered into database) Type Receivership CurrentPrice CurrentVendor PastTGCBid Interior BidSchedule AreaSchedule Notes There are a few fields named the same in different tables...those are not automatically the same unless noted in my notes above where I want the options to populate for me to choose from. From here, I obviously need to assign relationships and then I want to create a form. I started one with the form wizard where the company table is the main form and the contact and property tables are subforms. It is aesthetically what I am looking for, but a few of my relationships must not be right because the contacts and properties I enter are not being associated with just the one company I want them associated with. Within each company, there can be multiple contacts and multiple properties, but each contact and each property should only be assigned to one company. Hopefully that makes sense???? I also did not use your final "CompanyContactProperty" table when I first started so that is probably where my problem is. Thank you so much for your help! "Steve" wrote: Conside starting with these tables: TblCompany CompanyID CompanyName address fields other company attribute fields TblCompanyContact CompanyContactID CompanyID FirstName LastName other contact fields TblPropery PropertyID property description fields property location fields TblCompanyContactProperty CompanyContactPropertyID CompanyContactID PropertyID other attribute fields, other contact fields, property description fields and property location fields need to be refined. Start with postiing your other company attribute fields and we will help you with the normalized design of your tables. Steve "TGC" wrote in message ... I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! . |
#9
|
|||
|
|||
Prospective Client Tracking Database
Your tables are still not correct!!! You need to get the tables correct
first, then create the relationships before you even think about forms. If you don't get the tables correct, you will find yourself undoing a lot of work. It looks like you need a TblCompanyType table. What is Receivership? Your TblCompanyContactID should be TblCompanyContact with CompanyContactID as primary key. BusinessPhone is wrong. What is Correspondence? You probably need a TblCorrespondence. Please describe BidSchedule. You probably need multiple tables for BidSchedule. In TblProperty, ContactName et al fields are all wrong. Let's get TblCompany and TblCompanyContact right first then work on TblProperty. DO NOT BE MISLEAD!!! Gina Whipp's suggestion is 100% wrong!!!! Steve "TGC" wrote in message ... That makes sense! Here is what I have so far based on what you started... TblCompany CompanyID CompanyName Street City Zip Phone Fax Website Status Type Receivership TblCompanyContactID CompanyID FirstName LastName Status BusinessPhone (the phone from the company table would be populated in this field as an option-only want the phone numbers listed within the applicable company though, not all numbers entered into database) OfficePhone CellPhone Fax Correspondence BidSchedule TblProperty PropertyID Street City Zip ContactName (the contact first and last name from the contact table would be populated in this field as an option-only want the contacts listed within the applicable company though, not all contacts entered into database) Type Receivership CurrentPrice CurrentVendor PastTGCBid Interior BidSchedule AreaSchedule Notes There are a few fields named the same in different tables...those are not automatically the same unless noted in my notes above where I want the options to populate for me to choose from. From here, I obviously need to assign relationships and then I want to create a form. I started one with the form wizard where the company table is the main form and the contact and property tables are subforms. It is aesthetically what I am looking for, but a few of my relationships must not be right because the contacts and properties I enter are not being associated with just the one company I want them associated with. Within each company, there can be multiple contacts and multiple properties, but each contact and each property should only be assigned to one company. Hopefully that makes sense???? I also did not use your final "CompanyContactProperty" table when I first started so that is probably where my problem is. Thank you so much for your help! "Steve" wrote: Conside starting with these tables: TblCompany CompanyID CompanyName address fields other company attribute fields TblCompanyContact CompanyContactID CompanyID FirstName LastName other contact fields TblPropery PropertyID property description fields property location fields TblCompanyContactProperty CompanyContactPropertyID CompanyContactID PropertyID other attribute fields, other contact fields, property description fields and property location fields need to be refined. Start with postiing your other company attribute fields and we will help you with the normalized design of your tables. Steve "TGC" wrote in message ... I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! . |
#10
|
|||
|
|||
Prospective Client Tracking Database
Steve,
Stop... The OP stated she need one Company associated to each Propery, why would that be wrong? Are you suggesting another joiner table? My suggestion is correct based on the OP's needs. -- 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 "Steve" wrote in message ... Your tables are still not correct!!! You need to get the tables correct first, then create the relationships before you even think about forms. If you don't get the tables correct, you will find yourself undoing a lot of work. It looks like you need a TblCompanyType table. What is Receivership? Your TblCompanyContactID should be TblCompanyContact with CompanyContactID as primary key. BusinessPhone is wrong. What is Correspondence? You probably need a TblCorrespondence. Please describe BidSchedule. You probably need multiple tables for BidSchedule. In TblProperty, ContactName et al fields are all wrong. Let's get TblCompany and TblCompanyContact right first then work on TblProperty. DO NOT BE MISLEAD!!! Gina Whipp's suggestion is 100% wrong!!!! Steve "TGC" wrote in message ... That makes sense! Here is what I have so far based on what you started... TblCompany CompanyID CompanyName Street City Zip Phone Fax Website Status Type Receivership TblCompanyContactID CompanyID FirstName LastName Status BusinessPhone (the phone from the company table would be populated in this field as an option-only want the phone numbers listed within the applicable company though, not all numbers entered into database) OfficePhone CellPhone Fax Correspondence BidSchedule TblProperty PropertyID Street City Zip ContactName (the contact first and last name from the contact table would be populated in this field as an option-only want the contacts listed within the applicable company though, not all contacts entered into database) Type Receivership CurrentPrice CurrentVendor PastTGCBid Interior BidSchedule AreaSchedule Notes There are a few fields named the same in different tables...those are not automatically the same unless noted in my notes above where I want the options to populate for me to choose from. From here, I obviously need to assign relationships and then I want to create a form. I started one with the form wizard where the company table is the main form and the contact and property tables are subforms. It is aesthetically what I am looking for, but a few of my relationships must not be right because the contacts and properties I enter are not being associated with just the one company I want them associated with. Within each company, there can be multiple contacts and multiple properties, but each contact and each property should only be assigned to one company. Hopefully that makes sense???? I also did not use your final "CompanyContactProperty" table when I first started so that is probably where my problem is. Thank you so much for your help! "Steve" wrote: Conside starting with these tables: TblCompany CompanyID CompanyName address fields other company attribute fields TblCompanyContact CompanyContactID CompanyID FirstName LastName other contact fields TblPropery PropertyID property description fields property location fields TblCompanyContactProperty CompanyContactPropertyID CompanyContactID PropertyID other attribute fields, other contact fields, property description fields and property location fields need to be refined. Start with postiing your other company attribute fields and we will help you with the normalized design of your tables. Steve "TGC" wrote in message ... I am new to Access 2007 and recently reviewed all the online tutorials on the Microsoft website and reviewed the provided templates (none of which fit my needs). I understand the general concept of tables, forms, queries, reports, etc. and have created tables and attempted to create a form for my data, but am a little unsure about my design and was hoping for some feedback from others that know Access better than I (not hard to accomplish that distinction, btw!). I want to create a prospective client tracking database for a commercial landscape company. I am looking to collect information on companies, their contact people, and the properties they manage. One company, for example, may have multiple contact people and those contact people may manage multiple properties. Within those 3 main areas (companies, contacts, properties) there are a bunch of fields relating to the area (phone, email, address, etc.). I started by creating 3 tables (companies, contacts, properties) and then created relationships amongst the tables be relating the contacts' first and last name with the companies table, relating the properties' street address and the companies' name with the contacts table, and relating the contacts' first and late name with the properties table. I then created a form with the form wizard that had the companies table as the main form and the contacts and properties tables as the subforms. Am I going about this the right way? All I want to be able to do is track the companies and be able to add multiple contacts and multiple properties within the companies. Thank you in advance for your help! . |
Thread Tools | |
Display Modes | |
|
|