A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Prospective Client Tracking Database



 
 
Thread Tools Display Modes
  #1  
Old January 19th, 2010, 09:21 PM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default 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!
Ads
  #2  
Old January 19th, 2010, 10:23 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Prospective Client Tracking Database

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!



  #3  
Old January 20th, 2010, 01:06 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old January 20th, 2010, 01:25 AM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default 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
Email
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  
Old January 20th, 2010, 01:39 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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
Email
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  
Old January 20th, 2010, 01:49 AM posted to microsoft.public.access.tablesdbdesign
TGC
external usenet poster
 
Posts: 13
Default 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  
Old January 20th, 2010, 02:26 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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
Email
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  
Old January 20th, 2010, 02:39 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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
Email
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  
Old January 20th, 2010, 02:42 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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
Email
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  
Old January 20th, 2010, 02:49 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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
Email
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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.