Relationship/Normalizing

[cableguy47905 via AccessMonster.com]

I have a database that I just can't visualize the relationships in.
The data going in is made up of
Company
Contract
Location
etc.

Each company may have several contracts, but one main contract. They may
also have several locations for each contract. I can relate the locations to
the contract, but I don't know how to relate the contracts to each other.
They do have the one common factor of the company name, but the real problem
is that the db is already set up and it is not normalized. It is just one
very big table. It was set up before me, and I am trying to normalize it and
make it a bit more usable.

Right now most of the contracts are each set up individually with duplicating
data for their contacts, address/phone/email etc. for those that are
associated with each other. They have one tab on a form that will list all
of the other contract numbers that are associated with it. The problem with
this is that each contract on that tab is it's own field, and I cannot search
easily for the other contracts if I don't know the main contract, because not
all of them are built individually.

I would greatly appreciate any suggestions on how I can update this DB so
that I can add the locations/associated contracts.

Thanks in advance.

 

[Allen Browne]

Over time, each company will have mulitple contracts. (Contracts cannot last
for ever.) Therefore you have a one-to-many relation between Company and
Contract.

Each contract can cover mulitple locations. Therefore you have a one-to-many
relation between Contract and Location. Therefore one contract will have
many line items, so these will be stored in a ContractDetail table.

Is it possible that a location could change hands over the years (e.g. if
one company sells it to another)? Or, are these locations towns where
different companies may be operating? If so, it's actually a many-to-many
relation beween Contract and Location. The following shows how to do that.

Location table:
LocationID AutoNumber primary key
LocationName Text

Company table:
CompanyID AutoNumber primary key
CompanyName Text
...

Contract table:
ContractID AutoNumber primary key
CompanyID Number relates to Company.CompanyID
ContractStart Date/Time when this contract starts
ContractEnd Date/Time when this contract needs renewing
...

ContractDetail table:
ContractDetailID AutoNumber primary key
ContractID Number relates to Contract.ContractID
LocationID Number relates to Location.LocationID

If a contract may not cover the same dates for all the locations, move the
ContractStart and ContractEnd fields into the ContractDetail table.

 

[Chris2]

I have a database that I just can't visualize the relationships in.
The data going in is made up of
Company
Contract
Location
etc.

Each company may have several contracts, but one main contract. They may
also have several locations for each contract. I can relate the locations to
the contract, but I don't know how to relate the contracts to each other.
They do have the one common factor of the company name, but the real problem
is that the db is already set up and it is not normalized. It is just one
very big table. It was set up before me, and I am trying to normalize it and
make it a bit more usable.

Right now most of the contracts are each set up individually with duplicating
data for their contacts, address/phone/email etc. for those that are
associated with each other. They have one tab on a form that will list all
of the other contract numbers that are associated with it. The problem with
this is that each contract on that tab is it's own field, and I cannot search
easily for the other contracts if I don't know the main contract, because not
all of them are built individually.

I would greatly appreciate any suggestions on how I can update this DB so
that I can add the locations/associated contracts.

Thanks in advance.

cableguy47905,

The following tables represent my best guess.

It will require interpretation on your part as to how to fit them into
your overall database schema.

You can copy and paste these DDL SQL queries each into an MS Access
Query, executing each one in order to create the tables and the
relationships.


CREATE TABLE Companies
(CompanyID AUTOINCREMENT
,CompanyName TEXT(96)
,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(96)
,CONSTRAINT pk_Locations
PRIMARY KEY (LocationID)
)

CREATE TABLE CompanyContracts
(CompanyContractID AUTOINCREMENT
,CompanyID INTEGER
,ContractID INTEGER
,ContractStart DATETIME
,ContractEnd DATETIME
,CONSTRAINT pk_CompanyContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_CompanyContracts_Companies
FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
,CONSTRAINT fk_CompanyContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT un_CompanyID_ContractID_ContractStart_ContractEnd
UNIQUE (CompanyID, ContractID, ContractStart, ContractEnd)
)

CREATE TABLE MainContracts
(CompanyContractID INTEGER
,CONSTRAINT pk_MainContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_MainContracts_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
)


CREATE TABLE ContractLocations
(ContractLocationID AUTOINCREMENT
,CompanyContractID INTEGER
,LocationID INTEGER
,CONSTRAINT pk_ContractLocations
PRIMARY KEY (ContractLocationID)
,CONSTRAINT fk_ContractLocations_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
,CONSTRAINT fk_ContractLocations_Locations
FOREIGN KEY (LocationID)
REFERENCES Locations (LocationID)
)

Open the Relationships window, and use "Show All".


Sincerely,

Chris O.

 

[cableguy47905 via AccessMonster.com]

Wow, thanks for all the responses so far. It might take a little while to
digest some of this.

For both responses so far, I have the same question.

Would I be able to pull up a contract showing the Company Name, Contract
Number, and Specific location, plus show all of the other contracts that are
associated with that contract?

For a group of associated contracts, I want to be able to pull up a contract
and also show all of the other contracts that are associated with that one.
Whether there be a main contract or not.

I think I am having trouble visualizing it because it is the same field.
ContractID. I understand the relationship between Contract and Location.
Two separate tables with a one to many relationship between the two. I just
can't figure out the first case.

Both responses so far are going to give me something to test out and see. I
appreciate all of the thought that has gone into the responses.

Thanks again,
Lee (cableguy)

 

[Chris2]

Wow, thanks for all the responses so far. It might take a little while to
digest some of this.

For both responses so far, I have the same question.

Would I be able to pull up a contract showing the Company Name, Contract
Number, and Specific location, plus show all of the other contracts that are
associated with that contract?

Lee,

Although you did mention "but I don't know how to relate the contracts
to each other.", you did not describe what this meant.

I had assumed contracts were associated with each other by the company
they were associated with. (If this is so, the answer to your
question above is yes, it's possible. It would be a form/subform
arrangement for display, where the subform showed "all the other
contracts".)

In what way are your contracts associated to each other in your
business?

For a group of associated contracts, I want to be able to pull up a

contract

Based on what? Is the choice of contract that is pulled up from the
group of associated contracts random? Is the user inputting this
choice in a text box (or a combo box)? (As above, what defines a
"group of associated contracts"?)

and also show all of the other contracts that are associated with that one.
Whether there be a main contract or not.

I thought every company always had a main contract. Hmm, a careful
reading of your original post shows that this is not necessarily the
case, but fortunately it does not invalidate the design I wrote out.

What defines a "main contract" and whether a company has one, or not?

Both responses so far are going to give me something to test out and see. I
appreciate all of the thought that has gone into the responses.

You're welcome.


Sincerely,

Chris O.

 

[Chris2]

I'm interested in how you came up with 96.

FWIW in my jurisdiction (UK) it is was previously VARCHAR(70), now
NVARCHAR(255):

http://www.govtalk.gov.uk/gdsc/html/frames/OrganisationName-2-0-Release.htm

though I have yet to encounter a corporate body with a non-ascii name.

I'm interested in how you came up with 96...

Jamie.

Jamie,

I hope I'm not too great a disappointment when I say that they were
basically random numbers I yanked out of the air when writing up the
DDL SQL.

I have not previously considered adhering to known ANSI, ISO, or
governmental standards on the lengths of certain types of attributes
when responding on an ad hoc help basis in this newsgroup.

Sincerely,

Chris O.

 

[Allen Browne]

I'm not clear about "contacts that are associated with that contract."

The core idea is that a contract can have several detail lines (covering
different aspects, e.g. different locations), like an order has multiple
details lines. For an example of that, open the Northwind sample database
that installs with Access, and see the relation between Orders and Order
Details.

Is that what you mean? Or are you talking about something else, such as the
a contract that is derived from another one (such as a renewal that
effectively duplicates a previous one for a different timeframe)?

 

[cableguy47905 via AccessMonster.com]

Thanks for keeping up with me on this. i really appreciate it.

Although you did mention "but I don't know how to relate the contracts
to each other.", you did not describe what this meant.

A company may have one contract with us for specific purpose, and another for
a different purpose, etc. They are able to be active all at the same time,
so there isn't a timeframe, or a renewal of a contract to worry about. So
all of these contracts have one thing in common, the company.

Sometimes a contract may have different Locations associated with it. So a
contract can be representative of several different locations with all their
own personal information such as address, phone, email etc. Those locations
are only associated with the one contract, not with any others that the
company has with us.

I had assumed contracts were associated with each other by the company
they were associated with. (If this is so, the answer to your
question above is yes, it's possible. It would be a form/subform
arrangement for display, where the subform showed "all the other
contracts".)

Yes, I am pretty sure I understand that part, but I don't know how the tables
in the background are going to be set up. Is it possible to update all of
this information on one form? Maybe with subforms is alright.

Right now the DB has just one table and all of the data is able to be updated
via one form. There is no normalization to it at all. It has worked fine up
til now. I want to be able to perform more specific searches/queries. So it
doesn't work for me right now.

Based on what? Is the choice of contract that is pulled up from the
group of associated contracts random? Is the user inputting this
choice in a text box (or a combo box)? (As above, what defines a
"group of associated contracts"?)

This would be by simply doing a "find" on the txtContractID textbox.
Right now it brings up all the information, but it is just not functional.
You have to put in the main contract to find out the others associated with
it.

What defines a "main contract" and whether a company has one, or not?

Nothing really defines it, it just happens to be the one that gets the most
attention. I could be any one of them. No real definition for it.

I did create the tables that you gave me for the design that you had in mind,
but I am a little stumped about the ContractLocations table. I am not sure
what that table is for. Couldn't I just put the LocationID in the
CompanyContract table and do a one to one with the Location table?

Thanks again for the help.

 

[Chris2]

Thanks for keeping up with me on this. i really appreciate it.

You're welcome.

A company may have one contract with us for specific purpose, and another for
a different purpose, etc. They are able to be active all at the same time,
so there isn't a timeframe, or a renewal of a contract to worry about. So
all of these contracts have one thing in common, the company.

Sometimes a contract may have different Locations associated with it. So a
contract can be representative of several different locations with all their
own personal information such as address, phone, email etc. Those locations
are only associated with the one contract, not with any others that the
company has with us.

Thank you for clarifying.

It may be that the relationship between Locations and
ContractLocations that I specificed would have to be altered, since
contracts can have many locations, but each location can only have one
contract.

Each contract may have many locations. (1-to-Many from Contracts to
ContractLocations.)

Each location will appear on only one contract. (1-to-1 from Locations
to Contract Locations.)

Yes, I am pretty sure I understand that part, but I don't know how the tables
in the background are going to be set up.

The tables would be set almost up exactly as I gave them in my earlier
post.

I have modified them per these clarifications.

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
(CompanyContractID AUTOINCREMENT
,CompanyID INTEGER
,ContractID INTEGER
,CONSTRAINT pk_CompanyContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_CompanyContracts_Companies
FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
,CONSTRAINT fk_CompanyContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT un_CompanyID_ContractID
UNIQUE (CompanyID, ContractID)
)

CREATE TABLE MainContracts
(CompanyContractID INTEGER
,CONSTRAINT pk_MainContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_MainContracts_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
)


CREATE TABLE ContractLocations
(ContractLocationID AUTOINCREMENT
,CompanyContractID INTEGER
,LocationID INTEGER
,CONSTRAINT pk_ContractLocations
PRIMARY KEY (ContractLocationID)
,CONSTRAINT fk_ContractLocations_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
,CONSTRAINT fk_ContractLocations_Locations
FOREIGN KEY (LocationID)
REFERENCES Locations (LocationID)
,CONSTRAINT un_LocationID
UNIQUE (LocationID)
)

Is it possible to update all of this information on one form? Maybe
with subforms is alright.

I would like to say yes and yes, but I am running out the door in 4
minutes and don't have time to create and load sample data to the
tables, and then design a test form and write a step-by-step to
replicate what I did.

If I have time, I'll be back on it later tonight, and for sure when I
get home from work tomorrow evening (07-Jun-2007).

Right now the DB has just one table and all of the data is able to be updated
via one form. There is no normalization to it at all. It has worked fine up
til now. I want to be able to perform more specific searches/queries. So it
doesn't work for me right now.

This would be by simply doing a "find" on the txtContractID textbox.
Right now it brings up all the information, but it is just not functional.
You have to put in the main contract to find out the others associated with
it.

I'm not sure why it would be processed that way.

In the setup I gave, once you know what one ContractID is, you know
the CompanyID, and therefore can automatically find all the associated
contracts (in the CompanyContracts table). The main contract is a
fact recorded in another table, and is only queried when it is
desirable to know what the main contract is.

not?

Nothing really defines it, it just happens to be the one that gets the most
attention. I could be any one of them. No real definition for it.

That's a minor red-flag (at least to me). It's a "fuzzy" idea
floating around in the database.

It effectively means that no one knows for certain why a certain fact
is being recorded in the database.

I did create the tables that you gave me for the design that you had in mind,
but I am a little stumped about the ContractLocations table. I am not sure
what that table is for. Couldn't I just put the LocationID in the
CompanyContract table and do a one to one with the Location table?

No, that won't work. It would mean that each ContractID could only be
associated with one LocationID.

Well, now that I know that the relationship between Contracts and
Locations is not many-to-many (which I why I set it up that way), yes,
you could do it that way.

----

You could dump the date columns from CompanyContracts since you have
stated you do not need them.

-----

I, personally, would keep them anyway. I would have the application
record the date the the row in CompanyContracts was entered, and then
I would also have the application record the date the contract was
closed. CompanyContract rows with a closing date would be filtered
out of most active queries so that they would not appear on forms, but
you would retain the historical proceedings of your contracts.

Or, at least you could create a set of duplicate "archive" tables to
stick deleted information in.

Someday a manager is going to ask "what did we have before".

Thanks again for the help.

Sincerely,

Chris O.

 

[Chris2]

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]

Lee,

Hello?


Sincerely,

Chris O.

 

[cableguy47905 via AccessMonster.com]

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,

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]

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/specificproducts/a/normalization.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/kb/articles/q209/5/34.asp


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.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/database/datamodeling/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

 

[cableguy47905 via AccessMonster.com]

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

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/specificproducts/a/normalization.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/kb/articles/q209/5/34.asp

Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.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/database/datamodeling/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

 

[Chris2]

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.

 

[cableguy47905 via AccessMonster.com]

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?

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.

 

[Chris2]

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.

 

[cableguy47905 via AccessMonster.com]

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

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.

 

[Chris2]

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.