Results 1 to 2 of 2
  1. #1
    lemmiwinks is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    6

    Need Advice on Database for Scientific Lab Inventory & Data

    My supervisor asked me to make a database for the data for my own project and for other people that are starting to make the same data as me. I didn't have any experience with access, but I read a textbook that wasn't very helpful for what I wanted, and I did most of the Microsoft Access 2007 training on their website, which was more helpful in teaching me some fundamentals. I'll try to explain what I'm doing, what I want from Access, and what I've done so far.

    What I'm doing:
    My job is making monoclonal antibodies. Each of these unique antibodies is made from two unique pieces of DNA called a heavy chain and a light chain. To get these pieces of DNA I take a mouse and extract a cell from it and then run some reactions to get the heavy chain and the light chain DNA. I then use this DNA to make an antibody. Once an antibody is made I can then run different tests on it. The goal of the project is to compare the test results for antibodies from different mice and different cell types. Please let me know if that doesn't make sense and I can try and clarify it.

    What I Want:
    I want to be able to keep an inventory of the antibodies that I've made and also keep track of their test results as well. I've made over 300 antibodies at this point, and it's getting to be a pain to keep track of where they physically are and what their details are, in an excel spreadsheet. Our professor wants some other lab members to start making these antibodies as well, so a user-friendly way of adding their data to a database is another goal.

    Here's my thoughts on the types of forms I would like to make it easy for my lab members.

    Add User Form:
    Text box for a new username. (use this to distinguish between what antibodies belong to who).

    Add Mouse Form:
    Text box for a new type of mouse if necessary (we all use the same types of mouse so this wouldn't be unique to the user).

    Add Cell Type Form:
    Text box for a new cell type if necessary (we also all use same types of cells so this wouldn't be unique to the user).

    Add Plate Form: (We put the cells from the mice on a plate and then get the DNA from here. Each plate is unique to each user. No one shares plates.)
    Have combo boxes so user can choose their username, the mouse type, the cell type.
    Text box to name the plate.


    Text box for dates for when DNA reactions took place.

    Add Antibody and Chain Details Form: (Each plate will have 10-20 antibodies associated with it)
    Have cascading combo boxes so user can choose their username and this affects a combo box that limits the platename to only those of that user. (I don't want other lab members changing the data for other people too easily).
    Text box to name the antibody.
    Many text boxes for all of the DNA information for the antibody (genetic names, construction date, location of DNA). Two DNA pieces (heavy chain and light chain) are used to make an antibody, so I would have the information for both in this section of the form.

    Add Purified Antibody Form: (I can use the DNA to make an antibody multiple times, so for every antibody there could possibly be a one to many relationships in its making process.)
    Have cascading combo boxes so user can choose their name, their plate, and this limits the antibody name choices from what they entered in above.
    Text boxes for dates, amounts and location.
    (I'm hoping this will make it so I can query antibodies based on their plates or cell types and easily find their locations).


    If I could do this then I would have a decent inventory database setup. I'm still trying to figure out how to setup the testing part of the database. Each antibody can be tested in many different ways (a one to many relationship). However, each test I run contains many antibodies as well (a one-to-many relationship as well). I think it is similar to a student taking a class and that class contains many students, and each student can take multiple classes. This is a many-to-many relationship, right? I'm thinking I'll need a table and form so users can add in details on the type of test they want to run. Then I'd have another table and form for the test they run that lets them select the test type, add a test name and date, and some other details associated with that test. Then I would need a junction table and form that uses the IDs from the tests and from the antibody, and then associates those with fields for the test data for the antibody of interest. Is this correct?

    I attached a picture of my relationships I have made so far (I haven't done the test table yet). When I did the training from Microsoft's website, they suggested writing down all the fields I would want and then grouping them to make my tables. When I did this I made separate tables for my two DNA pieces that make one antibody (I have separate Heavy Chain and Light Chain tables from the antibody table). However, now I'm learning that making forms is pretty much a one table for one form thing. Each antibody only has one heavy chain and one light chain, which is a one-to-one relationship; should I just combine the heavy chain and light chain table into the antibody table to make it easier to make the form I want?
    Also, when looking at the relationships I noticed that the UserID is connected to two different tables that are also related to each other. Is this OK? I did this to make it so I could have cascading combo boxes that limited my plateID selection based on the initial plateID selection.

    Thanks for reading and sorry for all the words. Any suggestions and links so I can learn more would be much appreciated.
    Attached Thumbnails Attached Thumbnails bcr_relationships.JPG  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You want only one form for creating new Chain records? Yes, then should be one table, otherwise a form for each, which could be subforms on a 'Chains' form.

    The Chain tables are not identical in structure, Light has 3 additional fields. Could combine into one table and those fields will be ignored for the Heavy chains. Not pure normalization but tolerable.

    I think in this case, either approach is workable.

    Shouldn't need the UserID in both Antibodies and Plates. A combobox RowSource can be a query that joins tables.

    You have done a lot of prep work to gain an understanding of relational database and Access functionality. Looks like a good start.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Form error and database advice
    By gaby424 in forum Forms
    Replies: 4
    Last Post: 06-26-2012, 06:46 PM
  2. Getting Advice From Access/Database Consultants
    By ajetrumpet in forum Tutorials
    Replies: 0
    Last Post: 09-21-2011, 06:02 PM
  3. Database Structure Advice
    By Douglasrac in forum Access
    Replies: 9
    Last Post: 07-16-2011, 07:14 PM
  4. Database advice
    By PRINCE SWAGG in forum Access
    Replies: 29
    Last Post: 06-21-2011, 03:56 PM
  5. Replies: 1
    Last Post: 11-08-2008, 10:40 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