contracts database

[justin]

Hello, I'm a new Access user. I'm working with 2003 if
that helps. Anyway, I'm trying to create a database to
keep track of the various contracts that come in and out
of our office. Currently what I've created is a form where
the user selects the type of contract they are entering,
(eg: lease, grant, IGA<50k, IGA>50k, etc... there are 12
contract types total) using option buttons. Each option
button runs a macro which opens up a table. I've created
12 different tables. One for each type of contract.

My first question would be: Is this the right approach?
I've never used this program before, so all this has been
created by trial and error. Would there be an easier way
to store all the data for the different contract types?

If I've set up my database fine thus far, then I have
another dilema. The contracts need to be sent to different
people to be signed before the contract starts. So, we
also need this database to search through all the
contracts and return a list of the contracts which haven't
been completed.

To try to do this, in each contract table, we've created a
Yes/No field for completion. Thinking that we can somehow
get Access to search this field for each table, and return
only those where the field is no. Is this possible? If so,
how? Thank you in advance for any help or suggestions.

 

[gls858]

Hello, I'm a new Access user. I'm working with 2003 if
that helps. Anyway, I'm trying to create a database to
keep track of the various contracts that come in and out
of our office. Currently what I've created is a form where
the user selects the type of contract they are entering,
(eg: lease, grant, IGA<50k, IGA>50k, etc... there are 12
contract types total) using option buttons. Each option
button runs a macro which opens up a table. I've created
12 different tables. One for each type of contract.

My first question would be: Is this the right approach?
I've never used this program before, so all this has been
created by trial and error. Would there be an easier way
to store all the data for the different contract types?

If I've set up my database fine thus far, then I have
another dilema. The contracts need to be sent to different
people to be signed before the contract starts. So, we
also need this database to search through all the
contracts and return a list of the contracts which haven't
been completed.

To try to do this, in each contract table, we've created a
Yes/No field for completion. Thinking that we can somehow
get Access to search this field for each table, and return
only those where the field is no. Is this possible? If so,
how? Thank you in advance for any help or suggestions.

You might consider a table called ContractType and then another
for the other info. Now you have just 2 tables instead
of 12. Include the ContractType as a field in second table and create
a one to many relationship.

To search for the Yes no field you need to build a query including
all of the fields you would want to see and limit the yes/no field
to the value 1 this will give you the rows without a check mark.

You can then build a report using this query.

gls858

 

[Guest]

hi,
I see a slight problem with your design.
with your data split up into 12 table you will need to
have 12 queries to search through them. 12 reports to
print them out.
I would have put it all in one table and added a field for
contract type. this way i can search 1 table with 1 query
and get all on the ones that are not complete. you could
sort by contract type. you could add search criteria and
return 1 contract type or 2 or more.
many advantages to it all being in one table.
i don't know what all is in your contract records but if
you have something like client name and address in your
records then i would split that into another db
perticularly if you have multiple or re-occuring contracts
with then.
put them in a different table with a clients number. then
add a field for client number in the contracts table.
there is a tool in access.
tools>analyze>table
this will recomment if your table needs splitting and how.
but i would seriously rething my approch if i were you.
good luck

 

[justin]

Thanks for the quick reply. Ok, so say I create two new
tables titled ContractType and ContractInfo.
In the ContractType table, would I have each type of
contract in a different field or have one field labeled
type and then multiple entries in that field?
also;
Would I still use the original form I created where the
user clicks on the type of contract and it brings up the
ContractInfo table? Or should I lose the form all together
and just use these two new tables?

 

[Guest]

Thanks for the help. Yep, so after reading these
responses, and what I've read so far in this book "MS
Office Access 2003 Inside Out" using one table seems like
the way to go. The main priority with this database is to
track our contracts to know which one's still need
signatures and which ones are complete.

 

[gls858]

Thanks for the quick reply. Ok, so say I create two new
tables titled ContractType and ContractInfo.
In the ContractType table, would I have each type of
contract in a different field or have one field labeled
type and then multiple entries in that field?
also;
Would I still use the original form I created where the
user clicks on the type of contract and it brings up the
ContractInfo table? Or should I lose the form all together
and just use these two new tables?




table and create


yes/no field


check mark.

Without seeing the data it's hard to say. What you want to do
is create a table for each set of unique data. You don't
redundant data in separate tables.

You might want just one table called Contracts with two fields
ContractType and ContractInfo

Check this link on normalizing data
http://office.microsoft.com/en-us/assistance/HA010563211033.aspx

 

[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

 

[gls858]

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

Typicallly the two tables would have one field in common.
In this case I would include ContractType on the ContractInfo.
This will allow you to relate the two tables in a one to many
realtionship. IOW you will have one unique record in the ContractType
table for many in the ContractInfo table. Forms are the usual method
of entering data, or you can enter the data directly into the table.
Entering data directly into the table is not the recommended method.
The forms wizard can create basic forms. To get the data that you want
you need to create a query. This will group data based on your criteria.
For example all lease contracts not completed. You then create a report
based on the query. Once again the report wizard will helpyou build a
basic report. Keep in mind the the yes/no values are stored as -1 and 1.

gls858