Relationship/Normalizing

Lee,

I've modified the tables again.

CREATE TABLE Companies
(CompanyID AUTOINCREMENT
,CompanyName TEXT(255)
,CONSTRAINT pk_Companies
PRIMARY KEY (CompanyID)
)

CREATE TABLE Contracts
(ContractID AUTOINCREMENT
,ContractName TEXT(255)
,CONSTRAINT pk_Contracts
PRIMARY KEY (ContractID)
)

CREATE TABLE Locations
(LocationID AUTOINCREMENT
,LocationName TEXT(255)
,CONSTRAINT pk_Locations
PRIMARY KEY (LocationID)
)

CREATE TABLE CompanyContracts
(CompanyID INTEGER NOT NULL
,ContractID INTEGER NOT NULL
,CONSTRAINT pk_CompanyContracts
PRIMARY KEY (CompanyID, ContractID)
,CONSTRAINT fk_CompanyContracts_Companies
FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
,CONSTRAINT fk_CompanyContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT un_CompanyContracts_ContractID
UNIQUE (ContractID)
)

It also looks like each individual contract may be associated with
only one company, and so another unique index is required.


CREATE TABLE MainContracts
(ContractID INTEGER NOT NULL
,CONSTRAINT pk_MainContracts
PRIMARY KEY (ContractID)
,CONSTRAINT fk_MainContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
)


CREATE TABLE ContractLocations
(ContractID INTEGER NOT NULL
,LocationID INTEGER NOT NULL
,CONSTRAINT pk_ContractLocations
PRIMARY KEY (ContractID, LocationID)
,CONSTRAINT fk_ContractLocations_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT fk_ContractLocations_Locations
FOREIGN KEY (LocationID)
REFERENCES Locations (LocationID)
,CONSTRAINT un_ContractLocations_LocationID
UNIQUE (LocationID)
)




Sample Data:

Companies
1, Blue
2, Red
3, Yellow


Contracts
1, Software
2, Bridges
3, Skyscrapers
4, Parks
5, Streets
6, Police
7, Fire


Locations
1, New York
2, London
3, Paris
4, Munich
5, Athens
6, Hong Kong
7, Sydney
8, Islamabad
9, New Delhi


CompanyContracts
1, 1
1, 2
1, 3
2, 4
2, 5
3, 6
3, 7

We have companies with many contracts.


ContractLocations
1, 1
1, 2
2, 3
3, 4
4, 5
5, 6
6, 7
7, 8
7, 9

We have contracts with many locations, but no location has more than
one contract.


MainContracts
1
4
7


Ok, I believe you wanted a form where you could search for a contract
and pull up all associated contracts (i.e. all contracts with the same
company).


Create a form and name it frmContractSearch.

On frmContractSearch, draw two text boxes in a row across the top.
Name them as follows:

txtContractName, make sure it is unbound, name its label
lblContractName (caption "Contract Name"). Make sure the Control
Source is blank (unbound).
txtCompanyName, name its label lblCompanyName name (caption "Company
Name"). Make sure the control source is CompanyName (bound).

On frmContractSearch, below the row of three text boxes, draw a
command button and name it cmdSearch (caption "Search")

Draw a subform on frmContractSearch below all the other controls and
name it subContractSearch_AllContracts, name its label
lblContractSearch_OtherContracts (caption "All Contracts").


Create a Query named: qdf_frmContractSearch

SELECT C1.CompanyID
,C1.CompanyName
,CO1.ContractID
,CO1.ContractName
FROM ((Companies AS C1
INNER JOIN
CompanyContracts AS CC1
ON C1.CompanyID = CC1.CompanyID)
INNER JOIN
Contracts AS CO1
ON CO1.ContractID = CC1.ContractID)
WHERE CO1.ContractName = Forms!frmContractSearch!txtContractName


Set the Record Source of frmContractSearch to qdf_frmContractSearch.


Create a Query named: qdf_subContractSearch_AllContracts

SELECT

SELECT C1.ContractName
,C1.ContractName AS [Contracts Names]
,L1.LocationName AS [Location Names]
FROM ((ContractLocations AS CL1
INNER JOIN
Locations AS L1
ON CL1.LocationID = L1.LocationID)
INNER JOIN
Contracts AS C1
ON CL1.ContractID = C1.ContractID)
INNER JOIN
CompanyContracts AS CC1
ON CL1.ContractID = CC1.ContractID

Set the Record Source of subContractSearch_AllContracts to
qdf_subContractSearch_AllContracts.

Set both the Link Child Fields and Link Master Fields properies of the
subform to ContractName.


Select the command button cmdSearch, and click on the Code button on
the toolbar.

Paste this code in:

Private Sub cmdSearch_Click()

Form_frmContractSearch.Requery

End Sub

-----------------------------------

Run the form, enter a contract's name in the appropriate text box,
click search. The company name is shown for that contract, and in the
subform, all contract names and locations for that company are listed.


I hope that helped.


Sincerely,

Chris O.

"Chris2" wrote in
message . ..
Lee,


Hello?


Sincerely,

Chris O.

Chris,

I took all that info and ran with it. It seems to be running fine so far. I
am still having a hard time trying to figure out why we need the
CompanyContracts table and ContractLocations table. I think I could do it
now with just a one to many between the company and Contracts and also a one
to many between contracts and locations. Can you explain the
CompanyContracts table?

Thanks,
Lee

Chris2 wrote:
Lee,

I've modified the tables again.

CREATE TABLE Companies
(CompanyID AUTOINCREMENT
,CompanyName TEXT(255)
,CONSTRAINT pk_Companies
PRIMARY KEY (CompanyID)
)

CREATE TABLE Contracts
(ContractID AUTOINCREMENT
,ContractName TEXT(255)
,CONSTRAINT pk_Contracts
PRIMARY KEY (ContractID)
)

CREATE TABLE Locations
(LocationID AUTOINCREMENT
,LocationName TEXT(255)
,CONSTRAINT pk_Locations
PRIMARY KEY (LocationID)
)

CREATE TABLE CompanyContracts
(CompanyID INTEGER NOT NULL
,ContractID INTEGER NOT NULL
,CONSTRAINT pk_CompanyContracts
PRIMARY KEY (CompanyID, ContractID)
,CONSTRAINT fk_CompanyContracts_Companies
FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
,CONSTRAINT fk_CompanyContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT un_CompanyContracts_ContractID
UNIQUE (ContractID)
)

It also looks like each individual contract may be associated with
only one company, and so another unique index is required.

CREATE TABLE MainContracts
(ContractID INTEGER NOT NULL
,CONSTRAINT pk_MainContracts
PRIMARY KEY (ContractID)
,CONSTRAINT fk_MainContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
)

CREATE TABLE ContractLocations
(ContractID INTEGER NOT NULL
,LocationID INTEGER NOT NULL
,CONSTRAINT pk_ContractLocations
PRIMARY KEY (ContractID, LocationID)
,CONSTRAINT fk_ContractLocations_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT fk_ContractLocations_Locations
FOREIGN KEY (LocationID)
REFERENCES Locations (LocationID)
,CONSTRAINT un_ContractLocations_LocationID
UNIQUE (LocationID)
)

Sample Data:

Companies
1, Blue
2, Red
3, Yellow

Contracts
1, Software
2, Bridges
3, Skyscrapers
4, Parks
5, Streets
6, Police
7, Fire

Locations
1, New York
2, London
3, Paris
4, Munich
5, Athens
6, Hong Kong
7, Sydney
8, Islamabad
9, New Delhi

CompanyContracts
1, 1
1, 2
1, 3
2, 4
2, 5
3, 6
3, 7

We have companies with many contracts.

ContractLocations
1, 1
1, 2
2, 3
3, 4
4, 5
5, 6
6, 7
7, 8
7, 9

We have contracts with many locations, but no location has more than
one contract.

MainContracts
1
4
7

Ok, I believe you wanted a form where you could search for a contract
and pull up all associated contracts (i.e. all contracts with the same
company).

Create a form and name it frmContractSearch.

On frmContractSearch, draw two text boxes in a row across the top.
Name them as follows:

txtContractName, make sure it is unbound, name its label
lblContractName (caption "Contract Name"). Make sure the Control
Source is blank (unbound).
txtCompanyName, name its label lblCompanyName name (caption "Company
Name"). Make sure the control source is CompanyName (bound).

On frmContractSearch, below the row of three text boxes, draw a
command button and name it cmdSearch (caption "Search")

Draw a subform on frmContractSearch below all the other controls and
name it subContractSearch_AllContracts, name its label
lblContractSearch_OtherContracts (caption "All Contracts").

Create a Query named: qdf_frmContractSearch

SELECT C1.CompanyID
,C1.CompanyName
,CO1.ContractID
,CO1.ContractName
FROM ((Companies AS C1
INNER JOIN
CompanyContracts AS CC1
ON C1.CompanyID = CC1.CompanyID)
INNER JOIN
Contracts AS CO1
ON CO1.ContractID = CC1.ContractID)
WHERE CO1.ContractName = Forms!frmContractSearch!txtContractName

Set the Record Source of frmContractSearch to qdf_frmContractSearch.

Create a Query named: qdf_subContractSearch_AllContracts

SELECT

SELECT C1.ContractName
,C1.ContractName AS [Contracts Names]
,L1.LocationName AS [Location Names]
FROM ((ContractLocations AS CL1
INNER JOIN
Locations AS L1
ON CL1.LocationID = L1.LocationID)
INNER JOIN
Contracts AS C1
ON CL1.ContractID = C1.ContractID)
INNER JOIN
CompanyContracts AS CC1
ON CL1.ContractID = CC1.ContractID

Set the Record Source of subContractSearch_AllContracts to
qdf_subContractSearch_AllContracts.

Set both the Link Child Fields and Link Master Fields properies of the
subform to ContractName.

Select the command button cmdSearch, and click on the Code button on
the toolbar.

Paste this code in:

Private Sub cmdSearch_Click()

Form_frmContractSearch.Requery

End Sub

-----------------------------------

Run the form, enter a contract's name in the appropriate text box,
click search. The company name is shown for that contract, and in the
subform, all contract names and locations for that company are listed.

I hope that helped.

Sincerely,

Chris O.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200706/1

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:739ae3b91e21b@uwe...
Chris,

I took all that info and ran with it. It seems to be running fine
so far. I
am still having a hard time trying to figure out why we need the
CompanyContracts table and ContractLocations table. I think I could
do it
now with just a one to many between the company and Contracts and
also a one
to many between contracts and locations. Can you explain the
CompanyContracts table?


Lee,

The reason for it? It's standard normalized relational database
design.

One company may have many contracts. That, in and of itself, is a
fact of the database, it describes a relationship between company
information and contract information.

In the database schema, you must have an entity (described using at
table) to express this.

CompanyContracts is the table that expresses this entity.

You *could* store it some other way, but it would not be normalized.

It is up to you to make that decision, but I do not recommend it.

Example One. You would be unable to delete a company from Companies
unless of all of its contracts were also deleted from Contracts. You
could lose a company as a customer, and might be forced to remove it
and its contact information, but I am guessing that your company
probably does not wish to have all contracts it ever did business
under deleted (I could be wrong about that, too).

Example Two. Business requirements change ("That never happens!").
Now, two companies may have one contract. If you have put CompanyID
into the Contracts table, the database is now toast.

With my design, you simply delete the unique index on ContractID in
CompanyContracts (some queries may have to be worked on, but the
database itself it not broken).

Normalization is the high-flown name given to the "rules of database
design". Following these rules guarantees that your tables will be
designed correctly so that you can easily use SQL to answer your
questions and maintain the database.

Basics:

About.com
http://databases.about.com/od/specif...malization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1

Microsoft: Description of database normalization basics in Access 2000
(not significantly changed by Access 2007, see the article's own
references at the end to material from the early 1990s).
http://support.microsoft.com/support.../q209/5/34.asp


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/...alization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I like this whole site, since it has a handy menu on the right
describing many important aspects of the database world:
http://www.utexas.edu/its/windows/da...ng/rm/rm7.html

---------------------------


What is another good example?

Any automobile may have insured drivers.

Automobiles:
AutomobileID
PassengerCapacity

InsuredDrivers: (First Version)
InsuredDriverID
PolicyID -- Somewhere a Policy table exists.
DriverID -- Somewhere a Drivers table exists.

You could, technically, extend Insured Drivers to:

InsuredDrivers: (Second Version)
InsuredDriverID
PolicyID
DriverID
AutomobileID

But does an automobile and its information actually describe the
InsuredDriver in any way? No, that information doesn't describe the
InsuredDriver. Sticking AutomobileID in InsuredDrivers is a mistake.

It should be the first version of InsuredDrivers and:

AutomobileInsuredDrivers
AutomobileInsuredDriverID
AutomobileID
InsuredDriverID


In the same way, Contracts aren't described by what company has them.

What you need to do is represent a fact, in this case an abstract
piece of knowledge (a relationshiop), in the database. In this case,
as a table representing that relationship. (And yes, you are now
seeing how Data Modeling, Database Design, and MS Access terminology
and definitions overlap. The word "relationship" is heavily
overloaded with different meanings.)


Sincerely,

Chris O

Thanks so much.

I was confused, because the contracts are dependent upon the company. One
contract cannot have more than one company, but one company can have more
than one contract.

The intermediate/associative table that you had created was for a many to
many relationship if I understand this correctly. Your examples seem to
describe this. I actually do have just a one to many relationship between
these two tables. The way that Access shows the relationship is backwards
from the way I had learned it. It shows the company having a one to many
relationship with CompanyContracts, and CompanyContracts having a many to one
relationship with Contracts. That is backwards from what I was thinking an
intermediate table would be.

Thanks for the examples and extra resources too. I will have to check them
out.

Since your first DDLs to create the tables for me, I have really expanded on
that. I have added several associative tables for my many to many
relationships. Total now I have 22 tables. It is really coming along now.

Thanks a lot.
Lee

Chris2 wrote:
Chris,

[quoted text clipped - 4 lines]
to many between contracts and locations. Can you explain the
CompanyContracts table?

Lee,

The reason for it? It's standard normalized relational database
design.

One company may have many contracts. That, in and of itself, is a
fact of the database, it describes a relationship between company
information and contract information.

In the database schema, you must have an entity (described using at
table) to express this.

CompanyContracts is the table that expresses this entity.

You *could* store it some other way, but it would not be normalized.

It is up to you to make that decision, but I do not recommend it.

Example One. You would be unable to delete a company from Companies
unless of all of its contracts were also deleted from Contracts. You
could lose a company as a customer, and might be forced to remove it
and its contact information, but I am guessing that your company
probably does not wish to have all contracts it ever did business
under deleted (I could be wrong about that, too).

Example Two. Business requirements change ("That never happens!").
Now, two companies may have one contract. If you have put CompanyID
into the Contracts table, the database is now toast.

With my design, you simply delete the unique index on ContractID in
CompanyContracts (some queries may have to be worked on, but the
database itself it not broken).

Normalization is the high-flown name given to the "rules of database
design". Following these rules guarantees that your tables will be
designed correctly so that you can easily use SQL to answer your
questions and maintain the database.

Basics:

About.com
http://databases.about.com/od/specif...malization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1

Microsoft: Description of database normalization basics in Access 2000
(not significantly changed by Access 2007, see the article's own
references at the end to material from the early 1990s).
http://support.microsoft.com/support.../q209/5/34.asp

Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/...alization.html

Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization

Very Advanced:

University of Texas:
I like this whole site, since it has a handy menu on the right
describing many important aspects of the database world:
http://www.utexas.edu/its/windows/da...ng/rm/rm7.html

---------------------------

What is another good example?

Any automobile may have insured drivers.

Automobiles:
AutomobileID
PassengerCapacity

InsuredDrivers: (First Version)
InsuredDriverID
PolicyID -- Somewhere a Policy table exists.
DriverID -- Somewhere a Drivers table exists.

You could, technically, extend Insured Drivers to:

InsuredDrivers: (Second Version)
InsuredDriverID
PolicyID
DriverID
AutomobileID

But does an automobile and its information actually describe the
InsuredDriver in any way? No, that information doesn't describe the
InsuredDriver. Sticking AutomobileID in InsuredDrivers is a mistake.

It should be the first version of InsuredDrivers and:

AutomobileInsuredDrivers
AutomobileInsuredDriverID
AutomobileID
InsuredDriverID

In the same way, Contracts aren't described by what company has them.

What you need to do is represent a fact, in this case an abstract
piece of knowledge (a relationshiop), in the database. In this case,
as a table representing that relationship. (And yes, you are now
seeing how Data Modeling, Database Design, and MS Access terminology
and definitions overlap. The word "relationship" is heavily
overloaded with different meanings.)

Sincerely,

Chris O

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200706/1

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:73b056b5ce8a6@uwe...
Thanks so much.

I was confused, because the contracts are dependent upon the
company. One
contract cannot have more than one company, but one company can have
more
than one contract.

The intermediate/associative table that you had created was for a
many to
many relationship if I understand this correctly.

There is a unique index on ContractID in CompanyContracts. This stops
the table from creating a many-to-many type association (that arises
from two one-to-many MS Access Relationships ending at the same
table).

It is there because that is one of the CONSTRAINTs specified in the
DDL SQL to create the table (i.e. I put it there).

If you open the Relationships window and double-click on the foreign
key line to Contracts, and look down at the Relationship Type at the
bottom of the Edit Relationships dialog box, it says "One-To-One".
(Or at least it does in my example db.)

In data modeling, that's called cardinality. In MS Access, that's
called a relationship.

In data modeling, CompanyContracts is called an entity and the type of
entity is a relationship. In MS Access, it's called a table.


Your examples seem to
describe this. I actually do have just a one to many relationship
between
these two tables. The way that Access shows the relationship is
backwards
from the way I had learned it. It shows the company having a one to
many
relationship with CompanyContracts, and CompanyContracts having a
many to one
relationship with Contracts.

The Relationships window does display the 1 and inifinity symbol for a
One-To-Many relationship on my copy of the database I created for
this, as well.

It is wrong.

The Edit Dialog Box says otherwise (see above), as does the DDL.

MS Access has some rather . . . interesting limitations.



That is backwards from what I was thinking an
intermediate table would be.

"Intermediate table", hmmm, how to explain . . .

There are two main phases in relational database design.

Logical Data Modeling.

Physical Data Modeling.

The first covers the ground of defining business rules and entities
and the cardinality between the entities.

The second covers the conversion of the completed logical data model
into the physical date model that is a set of database objects.

They are separate processes. The correct handling of both is critical
to the success of any significant database. (Stacks of books have
been written on the subject.)

For the smaller-scale purpose of newsgroup help, the two processes are
often blurred together by quite broad brushstrokes.

Contracts are an entity.

Companies are an entity.

The *fact* that Companies have Contracts is a type of entity, we can
call it CompanyContracts.

There is a cardinality between Companies and CompanyContracts
(one-to-many), and there is also a cardinality between Contracts and
CompanyContracts (one-to-one, as it stands now; but as I noted
previously, that might change).

If you put CompanyID into Contracts, you are combining the entities of
Companies and CompanyContracts.

I believe this is a violation of 2nd Normal Form (or it may be a 3rd
Normal Form violation, depending on how you want to define it).

Database normalization, as a process, seeks to prevent that type of
thing from happening, or if it does exist, to undo it.



Thanks for the examples and extra resources too. I will have to
check them
out.

You are welcome. It is not precisely a simple subject, but if you
want to know the "whys" behind the answers you receive in these
newsgroups, you'll have to know.



Since your first DDLs to create the tables for me, I have really
expanded on
that. I have added several associative tables for my many to many
relationships. Total now I have 22 tables. It is really coming
along now.

I'm gald to hear that you have been able to expand your horizons and
database design skills.

Actually, that's one of the brightest things you can see on the
newsgroups.




Sincerely,

Chris O.

Thanks again Chris, I am learning so much and didn't realize I didn't have
much of a grasp on this at all.

I wanted to define my relationships a little better. Because I have another
question.

1. Each Company can have multiple contracts, but the contracts are Unique to
just those Companies.
2. Each Contract can have multiple Locations, These locations are Unique to
the Company.
3. Those locations are unique to each Company.

Therefore
Company A (ComA) can have Contract 1 (C1), Contract 2 (C2), Contract 3 (C3)
There are Locations (L1), (L2), (L3) that are all specific to the Company,
not the contract.

Company B (ComB) can have (CB1), (CB2) and has no locations.

This is correct:
Company Contract Location
ComA C1 L1
ComA C1 L2
ComA C1 L3
ComA C2
ComA C2
ComA C2
ComB CB1
ComB CB2


However,

It is allowing me to have L1, L2, L3 with CB1 or CB2. This is not correct.
C2 can have L1,L2,L3, but may not always have this. I am confused about this
relationship.

Can you help with this one?

cableguy47905 wrote:
Chris,

I took all that info and ran with it. It seems to be running fine so far. I
am still having a hard time trying to figure out why we need the
CompanyContracts table and ContractLocations table. I think I could do it
now with just a one to many between the company and Contracts and also a one
to many between contracts and locations. Can you explain the
CompanyContracts table?

Thanks,
Lee

Lee,

[quoted text clipped - 205 lines]

Chris O.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200706/1

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:73bef0c6f5376@uwe...
Thanks again Chris, I am learning so much and didn't realize I
didn't have
much of a grasp on this at all.

I wanted to define my relationships a little better. Because I have
another
question.

1. Each Company can have multiple contracts, but the contracts are
Unique to
just those Companies.
2. Each Contract can have multiple Locations, These locations are
Unique to
the Company.
3. Those locations are unique to each Company.

That wasn't a rule I realized, specifically.




Therefore
Company A (ComA) can have Contract 1 (C1), Contract 2 (C2), Contract
3 (C3)
There are Locations (L1), (L2), (L3) that are all specific to the
Company,
not the contract.

That's a major departure from what I knew previously.

If locations are specific to companies, and not contracts, then things
would be organized differently.

Except, I'm not sure exactly what new rule is, as the example below
still has contracts having locations.



Company B (ComB) can have (CB1), (CB2) and has no locations.

Wow!



This is correct:
Company Contract Location
ComA C1 L1
ComA C1 L2
ComA C1 L3
ComA C2
ComA C2
ComA C2
ComB CB1
ComB CB2


However,

It is allowing me to have L1, L2, L3 with CB1 or CB2. This is not
correct.
C2 can have L1,L2,L3, but may not always have this. I am confused
about this
relationship.

Can you help with this one?


Umm . . . think . . . think . . . think . . . Jeopardy theme music /
.. . .


Companies have contracts.

Companies may have locations.

Locations may be associated with only one Company.

Contracts may have locations.

Contracts may have many locations.

Contracts may be associated with only one Company.


Before I go ahead and start the table designing route, why don't you
look over the list of rules above and add, delete, or edit as
necessary.


Sincerely,

Chris O.

Chris,

I actually got it.


Bare with me, I have forgotten how to document this properly. Hopefully you
can figure out my notations.

This is what I have:

TBL_Companies
PK-CompanyID---Autonumber No Duplicates FK to TBL_Contracts (1-M)

TBL_Contracts
PK-ContractNumber---Text No Duplicates FK to TBL_LocationContract (1-M)

PK-CompanyID---Number Duplicates OK FK to TBL_Companies. (M-1) and
FK to TBL_LocationContract (1-M)


TBL_LocationContract
PK-LocationID---Number Duplicates OK FK to TBL_Locations (M-1)
PK-ContractNumber---Text Duplicates OK FK to TBL_Contracts (M-1)
PK-CompanyID---Number Duplicates OK FK to TBL_Contracts (M-1)

TBL_Locations
PK-LocationID---Autonumber No Duplicates FK to TBL_LocationContract (1-M)


This works. I have tested many different times now and I am pretty sure this
is what I was wanting.

I hope all of that makes sense. It is too bad we can't post the relationship
table on here.

Thanks again for all of the help.
Lee


Chris2 wrote:
Thanks again Chris, I am learning so much and didn't realize I didn't have
much of a grasp on this at all.
[quoted text clipped - 7 lines]
the Company.
3. Those locations are unique to each Company.

That wasn't a rule I realized, specifically.

Therefore
Company A (ComA) can have Contract 1 (C1), Contract 2 (C2), Contract 3 (C3)
There are Locations (L1), (L2), (L3) that are all specific to the Company,
not the contract.

That's a major departure from what I knew previously.

If locations are specific to companies, and not contracts, then things
would be organized differently.

Except, I'm not sure exactly what new rule is, as the example below
still has contracts having locations.

Company B (ComB) can have (CB1), (CB2) and has no locations.

Wow!

This is correct:
Company Contract Location
[quoted text clipped - 14 lines]

Can you help with this one?

Umm . . . think . . . think . . . think . . . Jeopardy theme music /
. . .

Companies have contracts.

Companies may have locations.

Locations may be associated with only one Company.

Contracts may have locations.

Contracts may have many locations.

Contracts may be associated with only one Company.

Before I go ahead and start the table designing route, why don't you
look over the list of rules above and add, delete, or edit as
necessary.

Sincerely,

Chris O.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200706/1

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:73e645bab2d26@uwe...
Chris,

I actually got it.


Lee,

Fantastic!


Bare with me, I have forgotten how to document this properly.
Hopefully you
can figure out my notations.

This is what I have:

TBL_Companies
PK-CompanyID---Autonumber No Duplicates FK to TBL_Contracts (1-M)

TBL_Contracts
PK-ContractNumber---Text No Duplicates FK to
TBL_LocationContract (1-M)

PK-CompanyID---Number Duplicates OK FK to TBL_Companies. (M-1)
and
FK to TBL_LocationContract (1-M)


TBL_LocationContract
PK-LocationID---Number Duplicates OK FK to TBL_Locations
(M-1)
PK-ContractNumber---Text Duplicates OK FK to TBL_Contracts (M-1)
PK-CompanyID---Number Duplicates OK FK to TBL_Contracts (M-1)

TBL_Locations
PK-LocationID---Autonumber No Duplicates FK to TBL_LocationContract
(1-M)


This works. I have tested many different times now and I am pretty
sure this
is what I was wanting.

I hope all of that makes sense. It is too bad we can't post the
relationship
table on here.

No, no, I read the above easily enough.

It isn't exactly what I would do, but if it is working for you at the
moment, that is good enough.


Thanks again for all of the help.
Lee


You are welcome.


Sincerely,

Chris O.