creating a contracts database

[justin]

Ok, so I'm starting over from scratch. It seems like the
more I read the more confused I become.

Anyway, I need to create this database where we can input
data about our contracts (specifically; contract#,
contract name, our contact person, their contact person,
date recieved, start date, end date, route date,
completion, $ amount, contract type, account#, PO#(if
needed), and who entered the data), then we need to be
able to see which contracts have not yet been completed.
That is to say, we need to track our contracts to know
which contracts have all the necessary signatures, and
which ones are still out in the field needing to be signed.

So far, I've created two tables. One ContractType, has one
field with the same heading, and twelve
different "records", one for each type of contract.
The other table, ContractInfo, has a field for each of
those catagories I've listed above.
We've created a field labeled "Completion" which I've made
into a Yes/No entry. I did this thinking this is what we
would use to check to see if the contract has all the
required signatures. Does this make sense?

Basically after this point, I'm stuck. What else needs to
be done, so we can enter in the data, and search the data
to see which contracts are still in the field? I know that
I probably need to create relationships between the
tables, but what am I relating, and why? It's just not
making much sense to me.

Thanks again for all your replys and comments. Hopefully
I'll be able to finish this project sometime soon.

-Justin

 

[Jeff Boyce]

Justin

Having just completed a "contracts tracking" system, I feel your pain!

I'm concerned about your statement "I probably need to create
relationships", and your description of the repeating fields in the
ContractType table structure. By your description, it sounds like you've
created ... a spreadsheet!

Access is a relational database. Unless you start with a relational,
well-normalized data model, you will cause yourself (and Access) some
serious headaches, trying to come up with work-arounds.

On the other hand, if you step back from the computer, pick up paper and
pencil, and sketch out the things/categories (aka "entities") about which
you are interested, the facts about ("attributes" of) those things, and how
one thing is related to others ("relationships"), you will find Access to
have many very useful tools and functions.

I urge you to look into "normalization"... (or have I total mis-read your
post?)

 

[Roger Carlson]

I have to echo Jeff's comments. If you have a contracts database with just
two tables, you REALLY need to normalize it further. A good place to start
learning about normalization is Michael Hernandez's book: "Database Design
for Mere Mortals". He never actually uses the term "normalization" but
that's what he's teaching.

After reading that, take a look at my website for my database design
tutorials: http://www.rogersaccesslibrary.com/TutorialsDesign.html. These
tutorials are built to follow the Hernandez process. (They are not designed
to replace the book, only illustrate it!) This should give you a much
better grasp of what you are doing.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[justin]

Thanks for your post.
I've read the 4 rules to normalization several times over
now.
Yes, before yesterday basically all I had was a
spreadsheet made in Access. Which doesn't really do
anything.
So, from the original table I made with the following
fields:contract#, contract name, our contact person, their
contact person, date recieved, start date, end date, route
date, completion, $ amount, contract type, account#, PO#
(if needed), and who entered the data, I've broken it down
into two tables thus far.
I removed all the date fields, and put them into their own
table. I also created a field ContractID in both tables
that is a autogenerated number and the primary key in the
original table. Then I created a relationship btw. the
ContractID fields in both tables.
I am on the right track with any of this?
Should I break that original table up even further? I
don't see the need to, but then again, I've never used
Access before, so I don't really know what I'm doing.

Would it be possible for you to elaborate on
the "contracts tracking" database you recently created?
Like tables involved, and their relationships btw. each
other?

Thanks for the help
-Justin

 

[Jeff Boyce]

Justin

Roger offered a couple of great resources to use in setting up a
well-normalized table structure. Consider re-posting a brief outline of
your current design for comment...

For example, your situation might call for:

tblContract
ContractID
ContractorID
ProjectManagerID
Title
Description
ContractType
OriginalAmount
OriginalStartDate
OriginalEndDate
...

or maybe some of these are not germane to what you are doing, while others,
unmentioned, are critical.

There are proprietary constrictions on releasing structure or application
for the system I recently designed, and, as above, you really need to have a
clear picture of what YOUR requirements are, rather than trying to make your
data/needs fit someone else's model.

 

[Guest]

Ok, here is an outline of my current setup

tblContractInfo
ContractID (primarykey, autonumber)
Contract#
ContractName
ContractType
OurContact
TheirContact
Account#
POnumber
$Amount
Completion
EnteredBy

tblContractDates
ContractID (autonumber)
RecievedDate
StartDate
EndDate
RouteDate

tblContractType
Grant
IS<50k
IS>50k
IGA<50k
IGA>50k
Leases
Licenses
Other
SA<50k
SA>50k
USFSCA<50k
USFSCA>50k


Ok so that's what I have so far. I've created a
relationship between the ContractID fields in the first
two tables.
Question about those autonumber fields. Shouldn't they be
the same number? Everytime I enter data and then look at
it in the tables, the ContractID fields always have two
different numbers for each table. I thought by relating
those two with each other, that they would be the same in
both tables? I guess I'm wrong? Can I fix that?

Thanks
-Justin

 

[Jeff Boyce]

Justin

(Comments in-line below)

Ok, here is an outline of my current setup

tblContractInfo
ContractID (primarykey, autonumber)
Contract#
ContractName
ContractType
OurContact
TheirContact
Account#
POnumber
$Amount
Completion
EnteredBy

tblContractDates
ContractID (autonumber)

oops! If you make this ID an autonumber, Access creates a unique ID number
(e.g., "auto number"). There is no reason for Access to know this record
relates to the table above, wherein the ContractID is ACTUALLY a ContractID.
If you wish, keep a unique, autonumber ID here, but make it the table's
ID -- ContractDateID. Then add another field to hold a "foreign key" -- in
this case, a LongInt type value which is the ContractID of the contract to
which this record belongs.

RecievedDate
StartDate
EndDate
RouteDate

Hmmm? What purpose does it serve to have a table that only holds dates, and
all the date fields relate to a single contract? If you design this way,
why not keep all the date-related fields in the tblContractInfo table?
Another approach would be to have a date-related table that stores three
facts (plus possible an ID) -- DateSomeActionHappened, and WhatHappened,
WhichContract (this is the LongInt foreign key mentioned above). This kind
of "date" table would be in a 1:M relationship with the "contracts" table
(each contract could have 0, 1, or many "dates").

tblContractType
Grant
IS<50k
IS>50k
IGA<50k
IGA>50k
Leases
Licenses
Other
SA<50k
SA>50k
USFSCA<50k
USFSCA>50k

Are you saying that a contract can only be one of these types? If so, this
is a lookup table, and the "type" belongs (as you've done) in the Contracts
(tblContractInfo). So these aren't actually fields, but the different
"types" (i.e., these are the records, not the fields?), right?

Ok so that's what I have so far. I've created a
relationship between the ContractID fields in the first
two tables.
Question about those autonumber fields. Shouldn't they be
the same number? Everytime I enter data and then look at
it in the tables, the ContractID fields always have two
different numbers for each table. I thought by relating
those two with each other, that they would be the same in
both tables? I guess I'm wrong? Can I fix that?

See above -- you can't fix it, because it isn't broken. Access is doing
exactly what it is designed to do.

 

[justin]

you wrote,

Hmmm? What purpose does it serve to have a table that only holds dates, and
all the date fields relate to a single contract? If you design this way,
why not keep all the date-related fields in the tblContractInfo table?
Another approach would be to have a date-related table that stores three
facts (plus possible an ID) -- DateSomeActionHappened, and WhatHappened,
WhichContract (this is the LongInt foreign key mentioned above). This kind
of "date" table would be in a 1:M relationship with the "contracts" table
(each contract could have 0, 1, or many "dates").

I created this dates table, b/c I thought I was following
the rules of normalization. I'm not? Yes, all the dates
are contract specific. If I get rid of the dates table,
then it seems to me that I'm back to having a spreadsheet,
and not necessarily a database.
I don't think making a table like you suggest would do me
any good. All the contracts have different dates
associated with them, and all we are really trying to
manage is which contracts of ours have all the signatures,
and which ones are still out in the field.

I don't know, I'm pretty sure all of this is out of my
league, I'm basically just taking blind stabs at this.

Are you saying that a contract can only be one of these types? If so, this
is a lookup table, and the "type" belongs (as you've done) in the Contracts
(tblContractInfo). So these aren't actually fields, but the different
"types" (i.e., these are the records, not the fields?),

right?

Right, I have a field labeled contract type. And then
these twelve records, representing each type of contract.
It is set up so when I user is entering data on the form,
there is a drop down box for them to choose which type of
contract they are entering. So I guess, yes, this is a
lookup table. it currently has no relationship to any of
the other tables.

 

[Jeff Boyce]

Justin

If one contract can have zero (just started), one (only one thing done), or
many (=more than one) date-related activities, then normalization rules
would argue FOR a DatedActions table, related 1:M to the Contracts table.
My previous response mentioned this kind of table.

I am NOT arguing for a "spreadsheetly" approach, with "date" fields
scattered throughout the main table.

I was confused by your use of what looked like a table structure (table
name, field names) when you were describing a lookup table for
ContractType -- if I'm understanding you correctly, what you were showing
was the table and its values, not its fieldnames.

Good luck

Jeff Boyce
<Access MVP>