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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |