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  

creating a contracts database



 
 
Thread Tools Display Modes
  #1  
Old February 21st, 2005, 07:39 PM
justin
external usenet poster
 
Posts: n/a
Default creating a contracts database

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

  #2  
Old February 21st, 2005, 09:33 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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?)

--
Good luck

Jeff Boyce
Access MVP


"justin" wrote in message
...
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


  #3  
Old February 22nd, 2005, 02:43 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

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/...UBED1=ACCESS-L



"justin" wrote in message
...
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



  #4  
Old February 23rd, 2005, 06:13 PM
justin
external usenet poster
 
Posts: n/a
Default

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

-----Original Message-----
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?)

--
Good luck

Jeff Boyce
Access MVP


"justin" wrote in

message
...
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


.

  #5  
Old February 24th, 2005, 02:43 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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.

--
Good luck!

Jeff Boyce
Access MVP

"justin" wrote in message
...
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


  #6  
Old February 25th, 2005, 07:41 PM
external usenet poster
 
Posts: n/a
Default

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
IS50k
IS50k
IGA50k
IGA50k
Leases
Licenses
Other
SA50k
SA50k
USFSCA50k
USFSCA50k


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

-----Original Message-----
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.

--
Good luck!

Jeff Boyce
Access MVP

"justin" wrote in

message
...
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


.

  #7  
Old February 27th, 2005, 01:28 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Justin

(Comments in-line below)

wrote in message
...
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
IS50k
IS50k
IGA50k
IGA50k
Leases
Licenses
Other
SA50k
SA50k
USFSCA50k
USFSCA50k


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.

--
Good luck

Jeff Boyce
Access MVP

  #8  
Old March 1st, 2005, 10:20 PM
justin
external usenet poster
 
Posts: n/a
Default

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.


tblContractType
Grant
IS50k
IS50k
IGA50k
IGA50k
Leases
Licenses
Other
SA50k
SA50k
USFSCA50k
USFSCA50k


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.


  #9  
Old March 2nd, 2005, 04:22 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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

"justin" wrote in message
...
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.


tblContractType
Grant
IS50k
IS50k
IGA50k
IGA50k
Leases
Licenses
Other
SA50k
SA50k
USFSCA50k
USFSCA50k


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.



 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
creating a contracts database justin New Users 1 February 21st, 2005 09:57 PM
Creating a database file Baylynx Contacts 1 December 14th, 2004 08:29 PM
Converting 97 database to 2003 database and implications John Database Design 1 November 22nd, 2004 06:23 AM
Creating an access database T General Discussion 8 July 30th, 2004 09:21 PM
Database Window Gone DaveB General Discussion 2 July 29th, 2004 12:24 AM


All times are GMT +1. The time now is 03:46 PM.


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