Page 4 of 4 FirstFirst 1234
Results 46 to 52 of 52

Add selected records from one table to another table

  1. #46
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Thank you for the reply Steve and the 10000 foot view
    icd 10 is an international coding for desease, it can be linked to patient or to order(the reason why he is taking these tests). Sample tbl is nessary because one sample can have many orders (dapy). Sometimes (stat-emergency) i dont have a formal invoice (order) just a paper with patient name ,doctor, healthcare, and hand written tests... (looks like a shopping list...anyway..)Think sample number as the identity of the laboratory sample, It could be used as barcode number, the unique identification , directly linked with the patient. Ex from one botle of blood(sample) i can perform almost every biochemical test, the tests could be to many orders. If you see to analyzers and samples tbl, i have linked one type of sample to each analyzer. There are many collection tubes depending by the test. There are times which i have to collect 5 tubes (samples), to perform the ordered tests... like Count Dracula... lol Sample id is the same because the all are from the same patient.Why not use patient id? Because there could be a repetition the near or far future.

  2. #47
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    LABDB.zip

    Dear members with your help i ve end up with the structure sown to the uploaded db. Steve made an add form which works fine.
    The probles im facing now are:

    1) Didn't come up with a solution about the results. Normaly they should be at the ORDERED_TEST table, but some results are not numeric. Some are just text and some others a lookup field.
    Any suggestions?????( i ve added a field to tbl Lab_tests named result_value and some unlinked lookup tables to see what i mean). Should i make a new tbl with results? Where it must be linked so i can have one to one relation with tests? Each test can have only one result per sample.
    Sould i add multiple fields with "results" to tbl orderd_tests for numeric, text and lookup fields? ( but then how can i hide the empty fields to the result?)
    I dont know if i can manage the the type of result for each test (in case the lab adds some tests in the future

    2) Going Steves solution to a highier lvl... is it possible to add lab tests as group.
    You can see edapy_group ... So for example if i select bloodcount from a from to add to tbl orderded_tests all the tests linked with this group.
    General is the a way to make some custom groups wich when they are selected to add multiple tests to tbl ordered tests. Imagine it as panels of tests added with a click
    So the user can see only records from tbl edapy_group but add records to tbl ordered_tests from table lab_tests. I ve tried it with insert into cmd but end up with a real mess, stomach and headache because of limmited knowledge probably...
    Click image for larger version. 

Name:	labdb.jpg 
Views:	30 
Size:	152.4 KB 
ID:	31600

  3. #48
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,275
    Hey Tony....


    Didn't come up with a solution about the results. Normaly they should be at the ORDERED_TEST table, but some results are not numeric. Some are just text and some others a lookup field.
    I would have/add two fields in the ORDERED_TEST table
    "UOM_ID_FK" - Long Integer - (UOM = Unit of Measure)
    "TestResult" - Text field (holds the value)


    is it possible to add lab tests as group.
    Yes. Something like a "Blood Panel"? You have specified tests that routinely are requested/ordered.
    Set up a table that has a "Panel" name and a Test. Then have code that would execute multiple Append commands.


    I modified your tables/relationships. I edited the field names to remove spaces and add the "_FK" suffix.
    The brown tables (see below) I understand (look up TABLES).
    The blue tables I understand.
    I don't understand what the yellow tables are/do.
    Click image for larger version. 

Name:	Relationship1.png 
Views:	27 
Size:	115.7 KB 
ID:	31606

    (I like the flow to go left to right. It is easier for me to see how the data flows)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #49
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Steve have you landed or still at 10000ft?

    -The yellow tables: The reason is one problem i ve faced. One sample can have aditional tests from different doctors-yards. So the flow is one sample, this could be blood, urine etch but from the same patient, think it as the number which characterises the sample(s) from a patient inside the lab. This numb can be used as a barcode id.Its the reference numb for the laboratory. I ve tried to normalise the db as far as i can.
    This sample(s) can have many test requests (orders) from different doctors (for several reasons)at the same time. This table characterises where the result will be send ( is he an outpatient? is he inside the hospital or the health center? from which doctor?) The tbl Ordered test has the test which must performed to the sample, (as i said this must be the tbl in which results should be stored.)
    - I m thinking to add multiple result fields, and hide the unnecessary with queries, this is the best and normalised solution. (numb, lookup, text)
    - I ve tried the append query and end up with a mess. I ve added a field group to tbl lab tests, made queries select for example group a, and run the append... result= mess!!! Run to the hills... as Iron Maiden say...run for your life for the ones near me....
    Steve I can ... live without panels of exams ... ( the other way i must go to have exams from a shrink), the main problem is with edapy group. This is how the user sees the test request or order if you like. My aim is the user to see the edapy group and "select " from this tbl and behind it to add the referd tests from tbl lab_tests (these are the real tests wich will be performed by the analysers). As you will probably understand edapy group has to do mostly with billing information. The mod you have createted is really close, exept the "multi" exams like bloodcount, urine tests, and two or three others. But if it works, even if there will be changes in the future, changing the realtions to edapy and lab_test the db will be working.

  5. #50
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    LABDB.zip

    This is the final ... (i hope) structure of the database (see attach above). *I ve added more than one result fields, i will hide them with some add result queries, so the user can see only the one which a value must be added.
    I have added three groups, one the wll known edapy and two more customized group fields.The two other have no table. I ve managed to append multiple tests but they are not linked to test request . I ve made a query to select all (ex bloodcount), changed it to append and select the ordered_tests as target table. The have added but not linked test request tbl. If you remember the form with all the exams (mod4), i ve added a button to run the append... but ... no linked records.
    Please help me .
    The best solution i think it will be a from which the user can select the group from a dropdown list, show the test which belong to the group, and add the tests with double click or with add all btn (from the group). The most matching group and easy of use is the custom group ( *in the db it has no table it is just a lookup field). In the ordered tests the user will see only the test name, the edapy group, the analyser field and the collector fields.
    With another forms based on queries (not showing the result fields not appropriate for the test) the user can add the results.
    Thank you for your time!!!!
    Click image for larger version. 

Name:	REL.jpg 
Views:	22 
Size:	159.7 KB 
ID:	31676

  6. #51
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    This is my progress so far
    Attached Files Attached Files

  7. #52
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    I ve made some tables so you can understand the structure and the relationships
    Attached Files Attached Files

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 11-27-2014, 09:18 AM
  2. Replies: 3
    Last Post: 01-24-2013, 01:38 PM
  3. Replies: 14
    Last Post: 12-06-2012, 10:25 AM
  4. Replies: 6
    Last Post: 05-10-2012, 07:20 PM
  5. Replies: 2
    Last Post: 09-18-2010, 06:52 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
  •  
Tech Forums: Microsoft Office Forums