Page 3 of 6 FirstFirst 123456 LastLast
Results 31 to 45 of 86
  1. #31
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Good Morning Gang,



    Feeling well-rested, but seeing disturbing images of rabbits hopping around in skirts when I close my eyes...weird.

    So I have taken some time this morning to really dig into the demo database that Steve put together (BTW - you have a private message pending Sir). While there are layers of complexity I probably haven't discovered yet (and maybe never will?), I do see some recurring "decisions" that I'm curious to better understand. For today there are two that I would like to explore.

    1. Using Queries to populate Forms and Tables.
    2. Assigning a Primary Key.

    First, Queries. I understand where they have exceptional utility. For instance data actions (average, sum, product) and filters (select data sets within a field). The only question here is why not build these functions directly into the Form, or extract them directly from the Table? Is the answer that a query "holds" and "remembers" select information (fields) that the forms then access (Access?? Access?? - maybe I'm having one of those rare moments of enlightenment. I hear music and exaltations)? I can see that if this were true, it could greatly speed up database performance. For instance if I wanted to know which kinase has been evaluated with an anti-phosphotyrosine antibody by western blot, that information likely would be distributed across three (or more) Tables. Rather than have to search all the fields in each of those Tables, a query could be designed that would "hold" and "remember" the values that you typically ask questions about - and therefore make the search much faster. Is this the right idea - am I starting to get the picture?

    Next, the amazing Primary Key. I have seen so many examples stressing how important this is, and I get it. But let's take Social Security Number (us old folk just abbreviate it SSN). This is for all practical purposes identical to a GenBank Accession Number (GBID). There is only one and it specifically refers to a unique entity (kinase or fusion protein as far as we're concerned). As stated, each of these are unique, which is what makes the GBID so useful. So if these are unique values, then why are we not using them as the Primary Key (or using SSN instead of my name)? Seems having the GBID and "another" Primary Key in the same Table is redundant, and does this not break the golden rule of "Normalization"?

    That's it for now. Time to get back to work.

    Auf Wiedersehen!

  2. #32
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The automated tape library/storage robot was just an attempt to show storage locations within cases, racks,modules.... see this for a better sample.

    I have looked at the data a little more but have focused on the storage aspect.
    How do you relate/associate barcodes with your "samples".

    I have set up a table called tblCryoStorage that encompasses freezers,canes, boxes,boxColumns and boxRows. This is representing all of your Cryostorage locations.

    When you assign a barcode to a location the meaning is "putting a sample at position X in the freezer".
    Barcode becomes the key to your operation. By means of the barcode you can uniquely identify the sample/ingredients to the related entities and to the storage location within your "cryosphere".

    Here's a jpg of the table with some sample data (again I'm focused on the storage)

    Here is some code to add random barcodes(samples) to random locations in the CryoTable. Some of the code are based on Steve's materials.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : addRandomSamples
    ' Author    : mellon
    ' Date      : 03/06/2016
    ' Purpose   : This is a mock up of storing a number of "vials/samples" in to cryostorage.
    ' For illustration of the concept I chose to place 28 samples with Random Barcodes into
    ' randomLocations in the current cryostorage made up of 2 freezers, having similar geography
    ' 9 Canes containing 8 Boxes each box having 12 columns and 8 rows. Canes, boxes and columns are numbered from 1.
    ' Rows are identified  A thru H.
    '
    'There are 17280 unique locations in 2 freezers with the above configuration.
    '---------------------------------------------------------------------------------------
    '
    Sub addRandomSamples()
    
    'to store 28 barcodes in cryostorage
        Const maxstor As Long = 17280   'max locations in 2 freezers with geography stated above
        Dim samp As Long: samp = maxstor
        Dim sSQL As String
        Dim iRecsToAdd As Integer: iRecsToAdd = 0
        Dim randStorage As Long
        Dim randBarcode As Long
        On Error GoTo addRandomSamples_Error
    
        Do Until iRecsToAdd = 28
            randStorage = randomNumber(1, maxstor)
    
            'make sure we are adding to locations that are NOT currently occupied
            If DLookup("isOccupiedYN", "tblCryostorage", "CryoRecordId = " & randStorage) = 0 Then
    
                randBarcode = GetRandomBarCode   'get a random barcode
    
                'make sure random barcode is not in use
                If DCount("barcode", "tblCryostorage", "barcode=" & randBarcode) = 0 Then
                    Debug.Print DLookup("isoccupiedYN", "tblCryoStorage", "CryoRecordId=" & randStorage)
                    Debug.Print randStorage & "    " & randBarcode
    
                    sSQL = "UPDATE tblCryoStorage SET "
                    sSQL = sSQL & " Barcode=" & randBarcode
                    sSQL = sSQL & ", isOccupiedYN = True  WHERE CryoRecordId =" & randStorage
                    Debug.Print sSQL
                    CurrentDb.Execute sSQL, dbFailOnError
                    iRecsToAdd = iRecsToAdd + 1
                    Debug.Print "iRecsToAdd   " & iRecsToAdd
                End If
            End If
        Loop
        MsgBox "Cryostorage update completed " & Now
        On Error GoTo 0
        Exit Sub
    
    addRandomSamples_Error:
    
        MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure addRandomSamples of Module Module1"
    End Sub
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : randomNumber
    ' Author    : mellon
    ' Date      : 03/06/2016
    ' Purpose   :To create a random number 
    '---------------------------------------------------------------------------------------
    '
    Function randomNumber(Lo As Long, Hi As Long) As Long
    
    10       On Error GoTo randomNumber_Error
    
    20    Randomize
    30    randomNumber = Int((Hi - Lo + 1) * Rnd + Lo)
    
    60       On Error GoTo 0
    70       Exit Function
    
    randomNumber_Error:
    
    80        MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure randomNumber of Module Module1"
    
    End Function


    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : GetRandomBarCode
    ' Author    : mellon
    ' Date      : 03/06/2016
    ' Purpose   : Routine to provide a barcode to a sample in this demo/mock up.
    'There will need to be some barcode authority in the operational system.
    '---------------------------------------------------------------------------------------
    '
    Function GetRandomBarCode()
    '8 digit barcodes in this mockup
        GetRandomBarCode = randomNumber(10000000, 99999999)
    End Function
    Query to show what storage is currently occupied.
    Code:
    SELECT TblCryoStorage.*
    FROM TblCryoStorage
    WHERE (((TblCryoStorage.IsOccupiedYN)=True));
    I think this storage approach simplifies some coding/logic. After set up, transactions will be updates.
    If history including who did what to what when etc then a transaction log record should be written when the sample is placed into storage. Again, barcode will be a major key to linking materials required in the log.

    Queries can be set up for things like: (again I focused on storage)

    What storage is occupied/unoccupied?
    Where are the specimens related to X?
    What is in Cane Y of Freezer 2?
    What interaction with storage did "person3" have since Date XX?
    How long has specimen WW been in storage?
    Who added materials to Freezer 2 in June 2016?

    Hope this is helpful.
    Comments???
    Attached Thumbnails Attached Thumbnails cryoTable.jpg  
    Last edited by orange; 06-03-2016 at 03:12 PM. Reason: add'l info

  3. #33
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Accessed View Post
    1. Using Queries to populate Forms and Tables.
    I think of queries as virtual tables because you can add multiple tables to a query to create a "table" that doesn't exist in the dB. But it still acts (mostly) like a table.
    But with a query, you can limit the records returned (setting criteria); you can also sort the records displayed in the form.
    You can use one query, with filtering and sorting, as the record source for a form and as the row source for a combo box. One query, two uses.

    You can do calculations in a query - not so much in a table. Yes, I know about calculated fields in tables (and look up fields and multi-value fields).
    Queries can have "virtual" fields that do calculations - don't have to write UDFs to do calculations.

    (side track)
    But lets say you have calculated fields and look up fields out the wazoo (technical term) and also a few MVFs in your tables.
    Then..... Hooray!!! The dB is complete! Well, as complete as possible - there are always changes...
    Now they (the voices) want better security, so time to convert to MySQL or SQL Server (Express or Standard). Because of the MVFs, calculated fields and look up fields, you now have to re-write the whole dB since those types of fields are not supported int the "big boy" databases.


    (Main track)

    Quote Originally Posted by Accessed View Post
    2. Assigning a Primary Key.
    There are lots of discussions/blogs/arguments about whether to use a natural key or a surrogate key as the PK in a table. That horse has been beat to death (poor horse).
    I always use an autonumber field as the PK field in almost every table - it is just easier. But I never display the PK field on a form or report - my PK fields, being autonumbers, are meaningless.


    More reading......

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm

  4. #34
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #35
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    What is latest status??
    Deconvoluting and assimilating. There really is a wealth of information here that I'm trying to grasp and use. Don't forget you guys are F1 drivers and I'm still trying to find the accelerator on my electric go-kart. I'll certainly need a weekend and perhaps even a week to really feel like I have a clue. Right now I'm trying to emulate without eviscerating. My first task is to build my own demo tables and then make sure I can import the key components of the other tables I built earlier.

    I'm about to step up and take a swing at some of your questions above, Orange Crusher :-)

  6. #36
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #37
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    How do you relate/associate barcodes with your "samples".
    This one I actually know ;-) We have a barcode reader and each vial already has 1D and 2D barcodes printed on it. Scan the vial and add it to the storage box. IT's really that easy :-)

    Quote Originally Posted by orange View Post
    Here is some code to add random barcodes(samples) to random locations in the CryoTable. Some of the code are based on Steve's materials.
    May I presume that these are to use to practice adding/archiving vials?


    Quote Originally Posted by orange View Post
    If history including who did what to what when etc then a transaction log record should be written when the sample is placed into storage. Again, barcode will be a major key to linking materials required in the log.
    I will absolutely want to document removing vials from our archive and that will need to be a query/form. This will help us set up an audit trail that we will want to track performance...and performance issues

    Quote Originally Posted by orange View Post
    Queries can be set up for things like: (again I focused on storage)

    What storage is occupied/unoccupied?
    Where are the specimens related to X?
    What is in Cane Y of Freezer 2?
    What interaction with storage did "person3" have since Date XX?
    How long has specimen WW been in storage?
    Who added materials to Freezer 2 in June 2016?
    Yes, many - if not all of these are capabilities that I will require. I'm hoping to have the first edition be carefully constructed, but I can already see that the database will no doubt evolve as I realize capabilities that I didn't know existed, and design deficiencies that will need to be addressed.

    Quote Originally Posted by orange View Post
    Hope this is helpful.
    Hell yeah!!

    Oops, am I allowed to say that?

    Maybe instead: Does the Devil dwell in Hades?

  8. #38
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    We have a barcode reader and each vial already has 1D and 2D barcodes printed on it. Scan the vial and add it to the storage box. IT's really that easy :-)
    Is the barcode used to identify and discuss/retrieve the "vials" and the other components? Or is it just for storage?

    Here is some code to add random barcodes(samples) to random locations in the CryoTable. Some of the code are based on Steve's materials.
    May I presume that these are to use to practice adding/archiving vials?
    This went with a sample table I created.That table identified each freezer, each cane, each box, each BoxCol and each Boxrow. I went that route because you have a known storage "cryoarea" (amount), so I considered it much like a large refrigerator and considered all shelves as either Occupied or Unoccupied. I still carried freezer, cane, box, boxcol and boxrow as a compound unique index. Such a table could be linked with others along the lines of what you have and Steve has mentioned.

    I tested the table by adding some random records(vials with random barcodes) into random locations.
    I subsequently set up a form to move X records from their current locations to other locations.
    Unfortunately, I am new to Windows 10 and my test database (in my Appdata...temp) got wiped out.
    Not a major thing, but I don't have that demo code now. (It could be rebuilt)

    I will absolutely want to document removing vials from our archive and that will need to be a query/form. This will help us set up an audit trail that we will want to track performance...and performance issues
    Yes an audit trail and possibly some utilities are great for maintenance and ad hoc events.

    The questions I posed were just thinking out loud. There are many more, and I'm sure you and your colleagues will have more focused questions/ideas as we get into some demo prototypes. Be cautious of getting too involved in loading tables and creating forms before getting your tables set up and vetted.

    Here is an article I did in response to a poster that may help focus on the evolving data model.

    Good luck.

  9. #39
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Hello - we're back {waving to all our friends}!

    We've grown somewhat frustrated with our little knucklehead. He can be a real dweeb at times, but we still love him. Problem is, he's really not so bright. So we've taken it upon ourselves to step in and start to set things straight before he starts to really muddy up the waters.

    So, here's what he really means to do (also attached as a zipped Excel file for those who wish to dabble):

    Attachment 24814

    The primary keys are highlighted in bold red and the foreign keys in bold blue. The Tables are also color coded - green is our "widgets", his so called assays/bunnies (weird dude). In light blue are all of the pieces we use to build our widgets, while you can think of orange as the QC test criteria and the rusty red as widget storage (in addition to those materials used for QC testing).

    Now that we know (for the most part) how we want to assemble his Tables, we have also noticed that he has both an Excel file and an Access DB file that have all 240 widgets/"assays"/bunnies already organized and stored. Unfortunately, each "assay" is identified by it's component field names rather than it's foreign key as identified in the green "assay" table. We hate inefficiency (trust us, we deal with his all day long); is there a way to import those 240 entries into our new green "assays" pivot table in such a way that - as the entries are imported - their field names are associated with their respective primary key in the component Table and subsequently assigned the appropriate foreign key?

    Thanks to all of you - you've been very patient with our "special" project as he has stumbled though this. You simply rock

    The Voices
    Last edited by Accessed; 06-04-2016 at 07:06 PM. Reason: trying to keep it simple

  10. #40
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Aloha Gang!

    Don't worry, this is David. Sorry about the voices - I usually do a better job of keeping them where they belong. But I see they did a better job of defining the task at hand.

    I'm close now. I understand that to do what I want above, I need to use a query form. What I just discovered is that I'm still somewhat lost, because when I attempt to append the data from my Excel worksheet, I get the following warning:

    Attachment 24823

    Looking through the demo DB from Steve, I'm pretty confident the solution lies within the query he named "qryVials". This one should let me import my "duplicate values" without adding duplicates to the primary records. Does this make sense? I'll keep working on this. Must say that having the demo DB is so amazingly helpful - thanks Steve!

  11. #41
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Closer still. Looking at the SQL view, I see that what's missing is an inner join. I presume that this creates some form of "alias" that allows Access to cross reference the field names against their appropriate primary key. Getting closer, need to get a better grasp of inner joins.

  12. #42
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    And....stumped. I have the correct joins defined, but nonetheless something is wrong. Something still isn't correctly set up. Here are the relationships for Steve's query joins:

    Attachment 24824

    and here from mine:

    Attachment 24825

    What I see is that Steve's query has the correct 1 to many relationship in place and I do not. My field values are also in square brackets (presumably due to whatever I've done wrong that doesn't have the relationship properly defined). I "think" I have the correct relationships set up in the database. Here's the main relationships I've set up:

    Attachment 24826

    So now I need to figure out what I've done wrong - or get your expert help :-)

    BTW - sorry for the giant images - I should know better (but too lazy to edit)

  13. #43
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Relationships are done in the relationships window with Access.
    Defined relationships are carried to Query designer, but can be overridden.

    You really need to get some statements along these lines(below) in order to create the proper relationships.
    Relationships are based on your "business rules". They are not arbitrary lines between tables.

    1 DerivationMethod can be used for Many CellAssays
    1 Investigator can "investigate/review" Many CellAssays

    A HostCell can have 1 or Many Sources???
    How is a Plasmid related to a FusionPartner??
    What is a KinaseClass? How is a Kinase related to a KinaseClass?
    Same question with KinaseRegion?

    What exactly is an InvNotebook? and InvPage?
    Can an Allele have different forms? If so, where are these identified? Or related?

  14. #44
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    You really need to get some statements along these lines(below)

    1 DerivationMethod can be used for Many CellAssays
    1 Investigator can "investigate/review" Many CellAssays
    Yes, for both of these

    Quote Originally Posted by orange View Post
    A HostCell can have 1 or Many Sources???
    How is a Plasmid related to a FusionPartner??
    What is a KinaseClass? How is a Kinase related to a KinaseClass?
    Same question with KinaseRegion?
    1. HostCell can potentially have many sources, though in our small world each cell line is likely to have only a single source (which can be different for each host cell).

    2. A plasmid carries DNA that encodes the fusion partner. Each fusion partner can have many plasmids, but one plasmid can have only one fusion partner

    3. A Kinase Class is a family to which related kinases belong. One family can have many kinases, but a kinase can only have one family

    4. You can think of a kinase region as a portion of the kinase protein that has a particular function. One region might act like a magnet to attract another protein, while a different region might act like scissors to cut that protein in half. A region can have many kinases and one kinase can have many of the same region.


    Quote Originally Posted by orange View Post
    What exactly is an InvNotebook? and InvPage?
    Investigator notebook and page. These are real world bound paper into which an investigator makes entries relating to the "assay" being produced. one "assay" can have only one notebook and page, but one notebook/page can have many assays.


    Quote Originally Posted by orange View Post
    Can an Allele have different forms? If so, where are these identified? Or related?
    An allele can have literally millions of forms. We track them as text entries (for instance L858R can be an allele, so too can T790M). Where it gets tricks is when two "alleles" get mixed into a single assay, in other words L858R+T790M. This is why we choose only a text entry to specify the allele rather than reference it from a table.

    These are great questions, not only because they force me to think about all of these relationships, but also make sure they are addressed in the database, and that they're normalized.

    As for my ongoing problem, I agree that it has to be something inherent to how I have set up my relationships. I'm thinking this way because if I use numbers to populate the foreign keys of my CellAssay table, the query properly displays (all) the specified field names as it should. But the query is not functioning as a two way street - I cannot make entries directly into the query without the warning regarding duplicates. So somewhere I'm still missing a really important concept regarding an Access DB and relationships.

  15. #45
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    These are great questions, not only because they force me to think about all of these relationships, but also make sure they are addressed in the database, and that they're normalized.
    Agreed, BUT these are what make you database specification. These basic facts are the essence of your relationships. You do not need queries at this point. You really need to know how the "things" in your proposed database relate to one anther.

    I'm going to point you to this tutorial from Rogers Access library. You have to work through it (~30-50 minutes). It has a problem description(narrative) and solution, and leads you through a process to create a data model. What you learn can be applied to any database.

    The Hernandez Process in a Nutshell is a great reference.

    Good luck.

Page 3 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  2. Need to use constraints
    By tsvetkovdimitar in forum Access
    Replies: 4
    Last Post: 11-19-2013, 01:39 PM
  3. Object dependencies
    By Rob S in forum Access
    Replies: 4
    Last Post: 08-03-2012, 04:00 PM
  4. Query with multiple date range constraints.
    By younggunnaz69 in forum Queries
    Replies: 2
    Last Post: 12-26-2011, 10:45 AM
  5. Replies: 1
    Last Post: 11-07-2011, 11:42 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