Results 1 to 8 of 8
  1. #1
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49

    Design for laboratory tests entry and review

    Hello to everyone here, this is my first post. Beforehand, I must inform you that I am not an access developer, I'm just a doctor trying to set up a small database to keep track of some parameters of my patients.

    I will not bore you with details about other aspects of the database, only with my current problem. I want to monitor some laboratory (blood) tests of my patients, so I ended up with the following design:

    tbl_patients
    ----------------
    pt_ID
    pt_Name

    tbl_LabTests (lookup table)
    ----------------
    LabTest_ID
    LabTestName
    LabTestUnits

    tbl_LabTestResults
    -----------------
    LabTestResult_ID
    pt_ID
    LabTestDate
    LabTest_ID
    LabTestValue

    I think that it is pretty straightforward in design terms. What I would like to do, ideally, is this:



    1. Have a datasheet(ish) subform for each patient (selected by a combobox) where the results will be displayed per date. This would allow the user to easily input/edit/review the lab results. Also it would be easy to have calculated fields and the corresponding units for each labt test.

    Patient No 1
    --------------
    LabTestDate | LabTestName1 | LabTestUnit1 | LabTestName2 | LabTestUnit2 | LabTestName3 | LabTestUnit3

    Date1 | Value 1 | units1 | Value 2 | units2 | Value 3 | units3

    Unfortunately, I haven't been able to do something like that, despite my searching. I tend to think that it is not possible.

    2. I did manage to create a crosstab query (using an expression to combine value and corresponding unit field), but it has the limitations of not being able to have calculated fields or editing on the spot.

    Is there any other way that I could have a persenteble set of results?

    Thank you in advance, Manolis

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont store data(subform) like that; LabTestDate | LabTestName1 | LabTestUnit1 | LabTestName2 | LabTestUnit2 | LabTestName3 | LabTestUnit3
    even that layout wont work in xtab. You only get 1 field for column names.

    Its fine for reports, but you must use the layout you have for subforms.

    have the
    tbl_LabTestResults as the subform, but Id add a listbox for:tbl_LabTests
    in the Patient form, and all test results in subform.
    select the Test in the list box, and the subform (results) will list only those.

  3. #3
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    hmm, but if I understand correctly, this way I will not have a unique date per row, showing all the available results for that date
    You see, a patient may have multiple tests on a given date, each with its own entry in tbl_LabTestResults
    Furthermore, the tbl_LabTests lookup table is expected to grow as time goes by and users add new kinds of tests.
    Click image for larger version. 

Name:	datasheet.jpg 
Views:	22 
Size:	35.4 KB 
ID:	33212

  4. #4
    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
    Further to ranman's comments you may find the info in this link on Database Planning and Design useful.

    Become familiar with Normalization. It is critical to database design.
    For your specific project, I would suggest you take a good look at the output you expect/want/need. Analyze it to identify the major subjects involved, and make sure your tables and fields (and required calculations) can collect and produce that output.

    Good luck.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Correct, normalization would call for multiple lab test records for each patient/date with another field to identify the specific lab test (Hct, Na, K, P, etc.).

    Don't understand the table you posted. Where is the patient ID? If this is your 'lookup' table for test norms, why is there a date field? A lookup table should also be 'vertical', not 'horizontal'.
    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.

  6. #6
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Sorry for being late to respond and thank you for your input.
    The table structure consists of the three tables at the top of the 1st post: tbl_patients , tbl_LabTests, tbl_LabTestResults.
    What I posted as "Patient No 1" is supposed to show how I would like the results to be presented in a subform (obviously it just confused things). It shows better in my 2nd post.

    tbl_patients
    Pt_ID PtName
    1 John Doe
    2 Jane Doe


    tbl_LabTests
    LabTest_ID LabTestName LabTestUnits
    1 Ca mg/dl
    2 P mg/dl
    3 K mmol/L
    4 Na mmol/L
    5 PTH pg/ml

    tbl_LabTestResults
    LabTestResultID Pt_ID LabTestDate LabTest_ID LabTestValue
    1 2 7/3/2018 2 6,3
    2 1 8/3/2018 1 8,7
    3 1 7/3/2018 4 135
    7 1 7/3/2018 2 5,9

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Data entry form you describe does not work easily with the normalized data structure. You would have to use a lot of VBA code behind unbound form to save and retrieve data.

    Now, having voiced affirmation of the normalized structure you presented, I will admit to having built a laboratory (construction) database that violates rules of normalization.

    It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works."
    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.

  8. #8
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    From the little I can understand by searching various fora, it seems that with this structure, even though it seemed right in the beginning, I will indeed face much trouble manipulating the data in terms of retrieving, mainly.
    It is true that a crosstab query that I tried does what I want in terms of data presentation, but it's of no use when it comes to inserting data or editing existing ones.

    I also thought of trying to create a dynamic temp table per Pt_ID that will draw it's values from the tables you see, having as rows the [tbl_LabTestResults]![LabTestDate] and fields the [tbl_LabTests]![LabTestName. The later would be created with something like a "for each..." loop. But my knowledge of Access, SQL, VBA is not enough to do something like that.

    Finally, I could change the design of the whole thing, get rid of the tbl_LabTests & tbl_LabTestResults tables and create a new one:

    tbl_LabTests
    --------------
    LabTest_ID (PK)
    Pt_ID
    LabTestDate
    LabTest1
    LabTest2
    LabTest3
    (and so on)

    It doesn't look very nice and it requires that new fields are created when the user needs to add a new LabTest but, concidering that they probably will not be more than 20 LabTests, it may be the easier way.

    I welcome every suggestion that would help me do this right, even more teach me something for the next time that I will have to do a Database with much more LabTests.
    Thank you all in advance
    Last edited by jabarlee; 03-23-2018 at 04:23 AM. Reason: typos

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

Similar Threads

  1. sub-sub form for neuropsych tests
    By neuropsychresearch in forum Forms
    Replies: 83
    Last Post: 02-24-2018, 06:52 AM
  2. Replies: 2
    Last Post: 12-16-2016, 12:55 AM
  3. How long it took to pass tests and to get to the highest level?
    By aellistechsupport in forum Programming
    Replies: 3
    Last Post: 05-30-2014, 01:45 PM
  4. Design Review: Junction jable linking three tables
    By justgeig in forum Database Design
    Replies: 12
    Last Post: 07-03-2013, 03:24 PM
  5. Request for design review / critique
    By Charles7565 in forum Database Design
    Replies: 3
    Last Post: 10-26-2011, 02:04 PM

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