Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47

    Chemical Database Layout.

    Hi Everyone,

    I have attached a copy of my database to aid in visualization of my brain wall I ran into. I am working on a chemical database to house chemicals throughout our lab. The easiest way to see what I am attempting to do is by opening the database, and clicking on relationships, as I have them laid out according to my thought process. Generalizing, a chemical needs to have multiple ChemicalID's (an autonumber PK). What I have set-up is as such:
    tblChemicalInfo---to house a chemical's information from the bottle. PK is Chemical Name
    tblIntermediate---Only has two fields: ChemicalID (autonumbered PK) and Chemical Name (FK for tblChemicalInfo), and acting as a "middle man" between tblChemicalInfo and the "subset" tables below.

    Then my "subset" tables that house the information broken down into relevant "sections", with ChemicalID as the FK:
    tblLocation
    tblDateInfo---when a chemical was received, expires, disposed, etc.


    tblInventory---to keep track of annual inventories
    tblVirologyInfo---info that is specific only to one section of the lab, not necessary for other sections to document.

    My idea is that users would log-in a chemical by going to a form that would have the "Chemical Name" field from tblChemicalInfo for them to select a pre-entered chemical (or enter a new one), the chemical information would automatically fill in on the log-in form, and then the user can fill in the relevant "subset" information. When the user is done, this would create a new ChemicalID for a chemical in the tblIntermediate. That way I can search for a Chemical (Acetone) from tblChemicalInfo, and see all the Acetones throughout the building (since each would have its own ChemicalID)

    Have I overthought breaking my information down into those "subset" tables? Have I overcomplicated this thing? Any thoughts would be greatly appreciated!!!! Much Thanks!
    David
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    I suggest you review this tutorial to see the steps involved in taking some business facts, identifying potential tables, ensuring tables are normalized , then establishing relationships between tables all leading to a well structured data base.
    It will also help with identifying Primary and Foreign keys.
    http://www.rogersaccesslibrary.com/T...lationship.zip

  3. #3
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    Hey Orange,
    Thank you for the link. It did help me get a better grasp on my relationship setup, and he does provide some further links. My brain tends to glob up when I try and read multiple normalization and database setup articles, and end up in a temporary haze of confusion . I am still struggling with whether I have broken my tables down too far. The tblDateInfo I think could be in my intermediate table, but there is one section of the lab that needs to use the dates a little differently. I will keep experimenting with my setup and see what I come up with. Anyone else with suggestions, feel free to chime in as well. Thanks!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    After working through the tutorial, did you make any adjustments to your tables and/or relationships?
    If so, please post a copy.

  5. #5
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    I did make some changes to my relationships and tables. Attached is a copy of the database, with the relationships view being the most descriptive way to look at it. I made tblLocation and tblViroInfo a one-to-one relationship with tblChemicalID, because each Chemical under tblChemicalID should only have one matching record, since the information is more of a continuation on those records. And then tblInventory is a many-to-one relationship with tblChemicalID, being that multiple inventories could take place on one chemicalID. I think I am keeping with a one-to-many relationship with tblChemicalInfo and tblChemicalID. We will see how this pans out as I flush out details and make log in forms and what not.ChemManagement2013.accdb

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Do not use lookups at the Table field level.
    see http://access.mvps.org/access/lookupfields.htm

    I don't think tblChemicalId should be a table name. Perhaps tblChemical and ChemicalId would be a field, even the Primary key.

    There are some free inventory data models at http://www.databaseanswers.org/data_...all_models.htm
    some of which may help your design. None of these data models is necessarily customized to your needs. They are meant to give you some ideas for building your own model.

    Good luck

  7. #7
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    Orange,
    Where did I have a lookup at the table field level?
    ChemicalID is the primary key in the tblChemicalID. Any particular reason as to why it should be renamed tblChemical?
    I feel fairly confident in the current setup that I had attached, but if there is something particular you foresee as a problem, specific input would be great! Otherwise, I feel that the data is going to flow as needed and is a great improvement over the previous layout of information I had. Thanks for the links and thoughts!
    David

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    I've attached the first TableField lookup I found.

    If you are confident, then build some test data. Both good and bad data.
    Take a document and number your tests. Identify what you are testing and what result you expect. Then add a column to record what you actually get during your test.
    If everything tests well - no differences between expecteds and actuals - great. However, if there are any differences, identify what the problems is and retest. Reconcile every difference.

    Good luck
    Attached Thumbnails Attached Thumbnails Chemical_TableFieldLookup.jpg  

  9. #9
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    Orange,
    What is the replacement for using a lookup field at the table level? I have designed two other databases where I use initials, test type, etc as lookup fields to allow users a drop down box to select their values from. I haven't seen a lot of info on not using a combo box lookup for a table field, and have found that it easily allows the addition of choices into the combo box with ease. That link you provided on the Evils of Lookup Fields at the table level was not useful in providing the "best" alternative. Thanks!

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    The alternative is to have a separate table along the following format

    tblStatusLkup
    StatusId PK
    StatusDescription text

    with data such as

    01 NotYetStarted
    02 Initiated
    03 InProgress
    04 OnHold
    05 Completed

    and you link tables such as

    tblProjectStatus
    ProjectId
    StatusId ---------------tblStatusLkup.StatusId
    StatusDate

    If you need a combo, you make the RowSource = tblStatusLkup
    StatusId is bound, with length 0", so the combo shows StatusDescription and stores StatusId when/where needed.

    Have you tried searching the forum for Lookups at the Table field level??

  11. #11
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    Hey Orange,
    Thanks for the clarification, I have only allotted a small portion of my brain to remembering all the rules for database design (seeing as this is not my area of expertise, I am a scientist by nature, computer geek by necessity ), so it is helpful when these scenarios are pointed out to me. Back to some more redesign!

  12. #12
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    Orange,
    One last thing to pick your brain about? So in an old version of this database, I have my chemical names as text names. But in the new version that I am creating, I have the text names linked to a NameID autonumber. Is there an easy way to convert all of the Text names into their NameID if I were to import the data from an excel spreadsheet? Rather than me having to change every chemical name to the appropriate NameID in the excel spreadsheet prior to importing? Thanks

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    I'm not following this

    I have the text names linked to a NameID autonumber.
    "Links" between tables are really between PK and FK.

    Can you post a database showing what you are describing? Give specific details as necessary.

  14. #14
    dlab85 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    47
    I don't have the ability to zip my databases down to the appropriate size, but I attached two images showing the relationships and table contents. Below is the way I had my old database. All the information was basically maintained in one record, and as such the chemical name and company name are typed out in text format (i.e. Acetone, Thermo Fisher)
    Click image for larger version. 

Name:	OldVersion.JPG 
Views:	7 
Size:	71.6 KB 
ID:	11487
    Then below is the new version layout.
    Click image for larger version. 

Name:	NewVersion.JPG 
Views:	8 
Size:	157.4 KB 
ID:	11488
    The tblChemicalInfo houses the main information for a chemical, BUT the chemical name and company name are now the FK of NameID and CompanyID to the PK's in tblChemicalName and tblCompany (i.e. Acetone is stored as NameID 20, and Thermo Fisher is stored as CompanyID 36). If I want to transfer my data from the old database to the new, I need to replace all the Chemical Names with the appropriate NameID. I already put all the chemical names into tblChemicalName, so they have an assigned NameID, and the same goes for tblCompany. How is the best way of transferring the data into the new database without my having to manually assign the appropriate FK ID's (I was planning on using Excel as a intermediate step to allow for column rearrangement and what not)?

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    When I look at you relationships I see that Location and Virology have a 1 to 1 relationship with tblChemicalID. I think that should be a 1 (tblChemicalId) to Many(Locations) but I don't know your data.

    Typically if you have a lot of data to load into tables, and that data is not normalized. You create a number of queries to take the data and separate (select out) the fields you need to insert parts of the data into the respective tables.

    Quite often people have some csv or text files they want to load into a structured database. Often they load the inital data into a Temporary table; then create queries to move the pieces they need from the temporary table to the proper table.

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

Similar Threads

  1. Replies: 16
    Last Post: 01-10-2013, 07:14 PM
  2. Change layout on access 2010 contact database.
    By trumpetman in forum Access
    Replies: 0
    Last Post: 06-18-2012, 05:02 AM
  3. Replies: 1
    Last Post: 03-04-2012, 03:49 PM
  4. Database Design and Layout
    By rhys.downard in forum Database Design
    Replies: 1
    Last Post: 02-29-2012, 09:49 AM
  5. Database layout issues
    By jbessling in forum Access
    Replies: 3
    Last Post: 12-19-2011, 10:56 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