Results 1 to 8 of 8
  1. #1
    Naja is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2020
    Posts
    16

    Contracts Daily Log Database

    Hi!



    I have a question and I would like to know the opinion of the experts here. I have a database for my department contracts which I manage. All the information is in a single table and has many fields and I think it can be reduced to two or more tables. The problem is that I would not know how to make the relationship of these tables. The only fields that are unique are the PO# and the PR#, the others are variable fields.

    I have to track the renewal of these contracts and notify the owners to begin the renewal process, at least 30 days before expiration. This information must be kept year after year to look at the trend of annual increase of the contract. So this DB not only is a tracking tool, but also is use for financial purpose. Attached find the layout of the table. I just need to know how is the best way to break this table into multiple table to manage better the data. Thanks for your help.
    Click image for larger version. 

Name:	Contract DB structure.png 
Views:	18 
Size:	47.2 KB 
ID:	43385

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There is virtually NO way that 99% of databases are properly designed when they contain one table. I hate to keep pounding on the "normalization" theme, but that's where you simply must start. I see at least 5 tables based on that table. It all depends on one's understanding of the process and business, but likely you need more. Start by understanding normalization and then post back with a treatise of the business your db should support and ideally, a pic of the relationships (which at most, you would create based on your tables, but not populate those tables).

    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition to comments by Micron, DO NOT use spaces, punctuation or special characters in object names. Names should be Letters or numbers. Do not begin a name with a number.
    Be aware of reserved words - ex: "Type". A reserved word, plus it is not very descriptive. "Type" of What???

    Calculations do not belong in tables. They should be done in queries.

    My PK field is an Autonumber type field - in Every table. Take the time to properly name fields. (ie: Do not use "ID" as the PK field name in every table)

    I commented your image of the fields. Just something to think about while/after reading about Normalization.
    Attached Thumbnails Attached Thumbnails Contract DB structure3.png  

  4. #4
    Naja is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2020
    Posts
    16
    Quote Originally Posted by Micron View Post
    There is virtually NO way that 99% of databases are properly designed when they contain one table. I hate to keep pounding on the "normalization" theme, but that's where you simply must start. I see at least 5 tables based on that table. It all depends on one's understanding of the process and business, but likely you need more. Start by understanding normalization and then post back with a treatise of the business your db should support and ideally, a pic of the relationships (which at most, you would create based on your tables, but not populate those tables).

    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    I apologized for presenting my scenario and not knowing much about access table structure. My Access level is minimum, but I do appreciate the information you provided which I'm reading and trying to make sense of it.

  5. #5
    Naja is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2020
    Posts
    16
    Quote Originally Posted by ssanfu View Post
    In addition to comments by Micron, DO NOT use spaces, punctuation or special characters in object names. Names should be Letters or numbers. Do not begin a name with a number.
    Be aware of reserved words - ex: "Type". A reserved word, plus it is not very descriptive. "Type" of What???

    Calculations do not belong in tables. They should be done in queries.

    My PK field is an Autonumber type field - in Every table. Take the time to properly name fields. (ie: Do not use "ID" as the PK field name in every table)

    I commented your image of the fields. Just something to think about while/after reading about Normalization.
    Thank you for your explanation, I will definitely look into this over the weekend and if you don't mind, can I come back and ask question during the process? Let me know. Your help is appreciated.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I apologized for presenting my scenario and not knowing much about access table structure.
    No apologies necessary. We are/were all beginners at some point and had to learn. Normalization can be difficult to understand. I think that sometimes that is due to the way it's explained. If you don't "get it" from one source, find another - there must be at least a million hits if you search it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As an afterthought, here's what I often post along with the normalization links. Some of it relates to what ssanfu is saying. Lots to read up on, but you will be grateful later! The problem with Access is that it's easy to do poorly, and even M$ contributes to some of the poor results by introducing things that aren't really all that beneficial.

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Naja is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2020
    Posts
    16
    Quote Originally Posted by Micron View Post
    As an afterthought, here's what I often post along with the normalization links. Some of it relates to what ssanfu is saying. Lots to read up on, but you will be grateful later! The problem with Access is that it's easy to do poorly, and even M$ contributes to some of the poor results by introducing things that aren't really all that beneficial.

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
    Thank you. I will definitely look into this. I appreciate the time you are dedicating to help putting this together. I will be sharing below one line record of the table with real data which I fixed giving the advises you gave me above . I put some explanation as to what function each field plays on the DB, hopefully it will shed more light on what I'm trying to do.New Compressed (zipped) Folder.zip

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Daily Back up Database Question
    By Karaline in forum Access
    Replies: 6
    Last Post: 02-08-2017, 06:10 AM
  2. Daily Task database
    By augcorv@gmail.com in forum Forms
    Replies: 20
    Last Post: 03-13-2014, 09:15 PM
  3. Making daily backup of database
    By JeroenMioch in forum Access
    Replies: 5
    Last Post: 10-30-2013, 04:50 PM
  4. Daily Log Database??
    By brittle in forum Access
    Replies: 2
    Last Post: 08-26-2013, 12:53 PM
  5. Help needed on Contracts Portfolio database
    By futurecoder in forum Database Design
    Replies: 4
    Last Post: 04-04-2011, 09:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums