Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43

    Lab information system db

    First of all i want to thank you all for the reply to my previous posts. With the help of respectful members of this forum, i ve solved some problems with the disign. But there is a long way ahead of me... my knowledge especialy to vb is ... zero...


    Now the problems i am facing to this effort are:

    -Some of the exams(tests) performed by the lab include other tests. For examlpe bloodcount includes 20 tests. When the doctor asks for bloodcount, the lab must do all of the 20 tests. The order has only one test but this one test " includes " 20 results. The billing and the order is for one test (bloodcount). The same thing happens to quite a few tests.I ve made tables with the bloodcount results but i dont know if this is the proper solution.

    -Each patient refers to a group, male, female and child. For these groups there are different normal values for each exam(test). I ve made a table with normal values with the pk of exams as fk, group pk as fk and as data the min an max normal values. Hpw can i link the report of the test results with the group?

    -Should i have different tables for quality control tests? I ve made a table Quality control, with ExamId_fk and low, mean, high, values for each test linked with a quality control results table.

    - Finally some results dont have numeric values. Some test have specific values as possitive or negative, other some text and other some preselected values. The results table, to result field refers only to numeric, or text, or lookup. One thing i think of is three result fields... is that right? The other proble is that the lookup field are different for the tests (where they are...)
    Will it be better to have tables for each of these tests(exams)?

    The work flow:
    One person goes to a doctor. The doctor gives a paper with the tests. Now the tests must be selected (programed) to the db, performed by the analyzers and input the results. Each test is performed only by one analyzer, one analyzer can perform more than one test.
    I m waiting for some help and suggestions.
    With respect to all members Tony Walker
    ps ... sorry about the Greek to some descpriptions
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I built a laboratory db with similar situation - tests can be selected individually and then some tests are members of a 'combo' test. I manage this with a ComboTests table and lots of code.

    Regardless of how a test is selected, each test has its own table for data. These test tables actually have a 1-to-1 relationship with the 'Samples' login table.

    IMHO, it is a balancing act between normalization and ease of data entry/output. The db design is not a strictly normalized structure and there are a lot of empty fields, but it works. This structure allows simple forms and reports. The whole purpose is to produce a printed report of the test results, very little statistical analysis is done with the data.
    Last edited by June7; 11-01-2017 at 03:02 AM.
    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.

  3. #3
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Dear June7, if you saw at the db i ve uploaded, i ve tried to apply one to one relationship, (or at least i think) at table bloodcountexams. i m sure you have read other posts from me, i have read carefully what other members proposed and i mtrying to apply them, with the level of access knowledge i have.
    I m thinking to create more than one table for results depending on the kind of results(text, number, lookup) but i dont know if it will slow the db.(instead of having blank fields.If you are kind enough i would like you opinion. ( i think you can imagine what i m going through, with much less knowledge...)
    For one thing i m sure that i really need some help. This is a great step forward for me, really. I m trying with limmited resources (zero), lot of personal time and effort. I dont know if i can make it work, but i ll do my best and i m not willing to quit.(and i cant say that some things you write "seem like Greek to me"..lol)
    So far i must say one more time, THANKS to you, pbaldy, ssanfu.( sorry if i forgot someone) You have all of my respect! I just want you to "light the way" and...
    Oh, I get by with a little help from my friends
    Mm, I get high with a little help from my friends
    Mm, gonna try with a little help from my friends.(Joe Cocker)
    With respect Tony Walker

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The 'spider-web' pattern of table relationships concerns me. Review https://www.codeproject.com/articles...atabase-design

    EDIT: link removed, Box.com share link won't work, very odd, link worked for years
    Last edited by June7; 11-02-2017 at 03:08 AM.
    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.

  5. #5
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    The second link is invalid "the page does not exist"... and i m reading the first...

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Bizarre, never had issues with Box.com before. Links always worked with click. I replaced the link and tried click and it worked. Also, copy/paste the link into browser URL address box worked when click did not.
    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.

  7. #7
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Still not working, i ve made an account, thought that might this is the problem, but still nothing. How can i find your file?

  8. #8
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Still not working, i ve made an account, thought that might this is the problem, but still nothing. How can i find your file?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just tested the link and it opens the download window. I am not even logged in to Box, just clicking like any anonymous user. That's the only place I have made it available because it is so large. The frontend alone is almost 5MB zipped. I will see what I can do about that, probably take me a couple days to get to it.
    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.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, link really isn't working after all. I must have been logged in even though I had the site page closed. Very disappointing. Those share links worked for years.

    I reduced the frontend about as much as I can without compromising core functionality and zip for the frontend alone is still 3.5MB. So that's my next step, strip out some core stuff and modify code so can still run.
    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.

  11. #11
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Thank you very much June, i ve opened the file and ... it is too complicayed for me. Reading and searcing, i came up with a simple stracture. I ve made a table test group and a table tests. Each group is linked to one or more tests. For example Alboumine group (and many others) is likend to a single test alboumine, and bloodcount is linked to many tests ( group with many tests).
    1)Now is there a way for user to select a "group" and all tests under it( one or many from tbl tests) will be linked to tbl orders and then add results for the tests?
    2)Each pesrson has reference values depending on group male, female and child. If i manage to overcame step 1, how can i link the results with the reference values depending on group?
    Attached Files Attached Files

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the email did go through?

    1. Yes, there is a way. My database does that with a bunch of code. I am not wanting to build this for you.

    2. A patient can be both (male OR female) AND (child OR adult). Shouldn't norms take these combinations into account?

    Gender should be another field in tblPatients and then need a BirthDate field. Calculate the age to determine if patient is a child or adult.
    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.

  13. #13
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    1) Thank you June! I just want to know some things, to focus on and move on.
    2) I m trying to make things simple, three options, Child, Male, Female, consider all of it as "gender", one of three applied. As you can see this is a thread about the structure. I ask peaple with expierience and knowledge, to correct mistakes and suggest. Do you thing this structure is ok?

    P.S. About 1 First of all thank you for sending the DB... Although i wish someone could make it (i m not a liar) i asked for a book so i can read and make it by myself (thank you for the one suggested.)

  14. #14
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    1) Thank you June! I just want to know some things, to focus on and move on.
    2) I m trying to make things simple, three options, Child, Male, Female, consider all of it as "gender", one of three applied. As you can see this is a thread about the structure. I ask peaple with expierience and knowledge, to correct mistakes and suggest. Do you thing this structure is ok?

    P.S. About 1 First of all thank you for sending the DB... Although i wish someone could make it (i m not a liar) i asked for a book so i can read and make it by myself (thank you for the one suggested.)
    I think there may be answers to Access:2010VBA Programing at pg 286....

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I already expressed my concerns about your table linking in Relationships builder.

    When building queries, the query designer will pull in the links established in the Relationships builder. This means you could end up with queries that have circular linking and you will have to edit the query to remove extraneous links.
    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.

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

Similar Threads

  1. System.mdw
    By knarfreppep in forum Access
    Replies: 2
    Last Post: 10-26-2017, 04:56 PM
  2. Replies: 2
    Last Post: 02-05-2015, 03:51 PM
  3. Student Information System Design
    By ghoughton in forum Database Design
    Replies: 3
    Last Post: 07-16-2014, 01:51 PM
  4. Replies: 3
    Last Post: 05-02-2014, 09:27 AM
  5. Replies: 2
    Last Post: 04-12-2011, 10:14 AM

Tags for this Thread

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