Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jul 2010
    Posts
    25

    Beginning Design Help

    So my current assignment is to transfer an excel spreadsheet that was being used as a database in to an actual database using Access. I do know Access but have been trying to learn as much as possible. Originally I just transfered the excel spreadsheet in to Access and was going to keep everything on one table but that is proving to not be efficient. I am trying to seperate the information in to multiple tables but my knowledge base is not quite there. I am just looking for some suggestions in designing my database, below I put was is on the current table and how I am thinking of splitting it up as well restrictions and so on.
    The database is going to be used to keep track of companies who are submitting corrective letters to us and it often takes multiple submissions before they are acceptable. Also I would like to use this database for mail merges.

    On the original table I have the default ID field, company name, doing business as, their company number, the received date of their letter, the date their letter was scanned in to the computer, whether or not it fits in to a two different catgories, the revision date on their letter, the date we mailed a letter back to them, which submission they are on and the status of their submission, address of company.

    I have now split this up in to 4 tables.
    1st table: Company information- name, doing business as, company number, address


    2nd table: 1st submission- received date, date scanned, whether or not it fits in to two different catergories, revision date, date we mailed letter back, status of submission
    3rd table(only for companies who have an unacceptable submission): 2nd submission- received date, date scanned, whether or not it fits in two different categories, revision date, date we mailed letter back, status of submission
    This can continue for up to four submissions so 5 tables.
    In addition, in the first table I obviously do not want any duplicated but the problem is that in the second, third, fourth and so on tables there can be duplicated because sometimes companies will have to go through this submission process again in the future.

    My biggest goal is ease of data entry and look up. Just looking for some tips, and pointers.
    Also I am still having a trouble grasping the idea of primary keys/secondary keys/relationships
    THANKS!!!!!!!!!!!!!
    Last edited by GenericHbomb; 07-15-2010 at 10:33 AM. Reason: updating

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think that any time you have multiple tables with the same fields, you've got a design problem. I would have one table for submissions. You can either use your status field to denote which were acceptable or not, or perhaps add an extra field. I suspect using multiple tables for these would be a nightmare to deal with. That table would have a one-to-many relationship with the company table on your company ID field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    First off, like data should be stored in 1 table. So when you say you want to have multiple tables to hold the same type of submission data, that is the wrong approach. All recording of submissions should be in one table. You would just identify each submission # within a particular field.

    Now a word about primary and foreign keys. They are used to create the relationship between records in one table and related records in another table. Primary key fields are best set up as autonumber datatype fields. An autonumber datatype is just a long integer that automatically increments for each new record. Additionally, the primary key should have no significance to the user; it is only a way to uniquely identify each record in a table. The foreign key field in the related table needs to be the same data type as the related primary key, so in other words, the foreign key field needs to be a long integer.

    Now on to your design.

    As you have already indicated, you need a table to hold the basic company info

    You mentioned this:
    1st table: Company information- name, doing business as, company number, address
    Question: are "name" and "doing business as" both names for the same company? If a company can have multiple names, then that describes a one-to-many relationship which is handled with 2 tables. For illustrative purposes I will not worry about that issue for now.

    (Note: I generally use prefixes for my field names: pk=primary key, fk=foreign key, txt=text, dte=date etc. Additionally, the word name is a reserved word in Access so it is best not to use that as either a table or field name. Furthermore, it is recommended to not have spaces or special characters in your table and fields names)

    tblCompanies
    -pkCompanyID primary key, autonumber
    -txtCompanyNumber (I assume that this fields holds a company number of your making which I assume can have numbers and letters, you can change the datatype to meet your needs)
    -txtAddress
    -txtCity
    etc.



    Now as to the submissions, a company can have many submissions

    tblCompanySubmissions
    -pkCompanySubsID primary key, autonumber
    -fkCompanyID foreign key to tblCompanies
    -longSubmissionNumber (as you describe)
    -dteSubmission (the actual date on the submission, not the date you receive it)

    Now from your description, you do various things to a submission. So for a submission you have many actions (one-to-many relationship)

    tblSubmissionActions
    -pkSubmissionActionID primary key, autonumber
    -fkCompanySubsID foreign key to tblCompanySubmissions
    -fkActionID foreign key to tblActions
    -fkSubmissionCatID foreign key to tblSubmissionCategories
    -dtAction a date field to hold the date on which the action occurred.

    tblSubmissionCategories (you mentioned 2 categories)
    -pkSubmissionCatID primary key, autonumber
    -txtSubmissionCategoryName

    tblActions (a table to hold a general list of the various actions to which a submission can be subjected)
    -pkActionID primary key, autonumber
    -txtAction

    The action table would hold things like received, scan, sent letter, submission closed etc.

    Now you mentioned that a company will go through this process multiple times. Is this something like an audit that occurs every year? If so, then the above table structure will have to be adjusted. You'll have to provide some more detail about that aspect.

  4. #4
    Join Date
    Jul 2010
    Posts
    25
    Thanks for the quick response. You both have indicated that it is not a good idea to have multiple tables that hold similar information. I was just thinking multiple tables because each submission has a unique set of dates with it.
    Thank you jzpw11 for the extremely detailed response. I am going to begin putting together what you have given me examples on thus far.
    I was told to include a doing business as field because some companies are owed and operated by a single person so it would be John Doe doing business as Doe Inc. for instand. Most companies will not require a DBA so I am thinking of doing away with it entirely though I have to take that up with my employer.
    To answer your second question it is more of a follow up to an audit. We do an audit and then the company must make these submissions telling us how they plan to correct their problems. 1st, 2nd, 3rd submissions refer to how many times they must revise this submission in order for them to be acceptable by our standard.
    A companie's audit frequency is based upon complaints and other criteria. So as a result some companies will have to go through this follow up procedure two or three times within a couple years or months while other companies will never have to go through this procedure. So there is no set pattern in when companies will go through this process.

  5. #5
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I was told to include a doing business as field because some companies are owed and operated by a single person so it would be John Doe doing business as Doe Inc. for instand.
    Since the "doing business as" field is just a further description then I would probably just leave it as you have it.

    Now since the submissions are tied to a particular audit event and a company can have many audits (one company-to-many audits relationship) as you describe; we have to adjust the table structure to capture the audit to which the submissions are related. So now instead of relating the company directly to the submission, we need to relate the company to the audit event and the submissions to that audit event.

    tblCompanies
    -pkCompanyID primary key, autonumber
    -txtCompanyNumber (I assume that this fields holds a company number of your making which I assume can have numbers and letters, you can change the datatype to meet your needs)
    -txtAddress
    -txtCity
    etc.

    tblCompanyAudits
    -pkCompanyAuditID primary key, autonumber
    -fkCompanyID foreign key to tblCompanies
    -dteAudit (audit date)
    other fields related to the audit

    Now there can be many submissions to a particular audit

    tblAuditSubmissions
    -pkAuditSubsID primary key, autonumber
    -fkCompanyAuditID foreign key to tblCompanyAudits
    -longSubmissionNumber (as you describe)
    -dteSubmission (the actual date on the submission, not the date you receive it)

    Now from your description, you do various things to a submission. So for a submission you have many actions (one-to-many relationship)

    tblSubmissionActions
    -pkSubmissionActionID primary key, autonumber
    -fkAuditSubsID foreign key to tblAuditSubmissions
    -fkActionID foreign key to tblActions
    -fkSubmissionCatID foreign key to tblSubmissionCategories
    -dtAction a date field to hold the date on which the action occurred.

    tblSubmissionCategories (you mentioned 2 categories)
    -pkSubmissionCatID primary key, autonumber
    -txtSubmissionCategoryName

    tblActions (a table to hold a general list of the various actions to which a submission can be subjected)
    -pkActionID primary key, autonumber
    -txtAction

  6. #6
    Join Date
    Jul 2010
    Posts
    25
    After to speaking with someone a little higher up I was informed that the proccess is only completed once. Thr 1st, 2nd, 3rd and so on for submissions still exists but it is a one time audit. I was misinformed by my peer so I have changed what you have written to reflect that but I wanted to run it by you to ensure its correctness.

    Basically I removed the entire the tblCompanyAudits, and removed fkCompanyAuditID-foreign key to tblCompanyAudits.

    I am going to really get in to setting it up tomorrow and will let you know how it goes.

  7. #7
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I don't think it will really hurt much if you leave it as is and it will give you the functionality in the future if your company ends up doing multiple audits of a company over time.

    If you want to stick with just the one audit, then you can go back to my original structure and just add an audit date field to the company table since there will only be 1 audit per company

  8. #8
    Join Date
    Jul 2010
    Posts
    25
    I was working on the database this morning and am definitely making progress but am having to move away from your model a little but I am at fault here. I think is because I need to give you more information. So i am going to go through the information again and go more in depth.
    So we have the database with the first table being
    tblCarriers
    pk CompanyID
    numCompany(number)
    txtCompany(name)
    txtAddress
    txtCity
    txtState
    txtZipCode

    When I spoke of categories, there are two categories 45 or 60. These categories do not affect the database at all since for my position they are used for descriptive purposes.
    As for actions- I mean the only action we take is labeling their submission acceptable, unacceptable so I do not know if that requires a seperate table either?
    The other question I have is how does the layout from your initial post handle the idea that there can be multiple submissions and that each submission requires all of the information(received date, revised date, date scanned, date sent etc.)?
    I think it is a possibility that I am just not comprehending something in your above post.

  9. #9
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Anything you do to a submission constitutes an action (1 submission to many actions relationship).

    Altering what you said below:

    received date, revised date, date scanned, date sent, acceptance, non-acceptance, etc
    All of these are the actions that can happen to a submission and are thus recorded as records in tblSubmissionActions

    You'll have to explain more about the categories and how they relate to the submissions

  10. #10
    Join Date
    Jul 2010
    Posts
    25
    I thought it would be easier to attach the database than to explain it so above is attached. This is the old database that I am working off of now. The new database is going well so far but I thought attaching the old one would clear things up. The main datasheet does have multiple entires. I would attach the newer one but it does not cotain much information. The old one is before we talked at all so you will see many things wrong with it now such as labeling and so one. I had to erase the information in the carrier's name column. Also all of the address information is not there because that is something that we were just going to start adding with the move from excel to access.

  11. #11
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Just some design tips, it is best not to have spaces or special characters (#,/,\, &) in your table and field names. Some of the special characters are used in Visual Basic for Application code. Also, even though Access has the capability of having lookup fields at the table level, it is generally not recommended. Check out this link for a more detailed explanation.

  12. #12
    Join Date
    Jul 2010
    Posts
    25
    In the new database i did away with all symbols and spaces. Would it be better to just use the "Find" and "Filter" tools then?

  13. #13
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'm not sure what you mean by find/filter tools. You would not use these at the table level; in fact, your users should never see your tables. All user interaction should be through forms. You can use the find/filter tools when using the forms or you can use queries to do the filtering for you, but that should wait until your design is complete.

  14. #14
    Join Date
    Jul 2010
    Posts
    25
    I sat down and went through the Access 2007 Essential Training by Lynda over the past week and my database is in much better shape.

  15. #15
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad to hear that you are making progress. Please let us know if you have further questions.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating Beginning and Ending page numbers
    By sabraham in forum Access
    Replies: 3
    Last Post: 01-07-2010, 12:36 PM
  2. Design help
    By jacko311 in forum Database Design
    Replies: 0
    Last Post: 11-12-2009, 05:57 AM
  3. DB Design
    By Merkava in forum Database Design
    Replies: 2
    Last Post: 11-10-2009, 05:51 PM
  4. newbie needs design help
    By ashiers in forum Database Design
    Replies: 0
    Last Post: 09-13-2008, 07:05 PM
  5. Design help
    By marix in forum Database Design
    Replies: 1
    Last Post: 04-18-2007, 07:54 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