Page 3 of 3 FirstFirst 123
Results 31 to 39 of 39

Add selected records from one table to another table

  1. #31
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,842
    If multiple people are using the dB at the same time, you have a high possibility and probability of corruption. (Not if, just when )



    If the dB is multi-user, the dB should be split into a FE (front end) and a BE (back end).
    The BE has ONLY the tables.
    The FE has everything else (forms, queries,reports and modules (code).
    Google "split database".


    It is easy to split a dB.
    Read everything below before starting the process......


    Make a copy of the dB. Make a 2nd copy of the dB. Make 3rd copy of the dB - this is your backup... just in case. Put it away in a safe place.
    With the other two: Rename one with "FE" in the name. The other should have "BE" in the name.
    Examples: "MyLab4_FE.accdb" and "MyLab4_BE.accdb"

    Put the BE somewhere everyone has access - a computer on the LAN or on the server.

    Open the BE dB. Close the form.
    Go to FILE/OPTIONS/Current Database.
    Change the DISPLAY Form option to NONE. Click OK.
    Now delete ALL of the forms. Then Delete ALL of the queries. Close and save the BE.


    Open the FE. Close the form.
    Delete ALL of the tables EXCEPT "ExamsLocal". This table needs to be in the FE (ie local).
    Click in "External Data" in the Access menu/ribbon.
    Click on the ACCESS icon.
    Browse to the folder where you put the BE file. Select the BE.
    Click on the bottom radio button: "Link to the data source...."
    Select ALL of the tables EXCEPT "ExamsLocal".
    Click OK.
    Close/minimize the Navigation pane.
    Close the FE.
    Open the FE. Tada!! You now have a split dB.

    EACH user should have a copy of the FE (the accdb - not a shortcut) on their local computer.


    As always... the 3 rules of computing: Rule 1) Back up, Rule 2) Back up and ........ Rule 3) Back up!!




    As it is, make sure you have frequent back-ups.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  2. #32
    TONYWALKER is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    21
    Thank you for the complite guide!! One question, if i made changes for ex add some fields to exams, i must do the same changes to ExamsLocal (add the new fields too)?

  3. #33
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,842
    From the PM......
    I've looked at the relationships and am confused. It looks like you have added (at least) 3 junction tables, plus 2 other tables.

    The main problem is probably the linking. I marked up a picture of the relationships...
    Click image for larger version. 

Name:	Relationship1.png 
Views:	21 
Size:	198.2 KB 
ID:	30820
    The main question was about getting a FK in a query. The 3 tables are using the wrong linking fields.

    Exams.ExamID_PK should be linking to URINE_EXAMS.EXAMLINK and BLOODCOUNT_EXAMS.EXAMLINK,
    NOT Exams.Exam linked to URINE_EXAMS.EXAMLINK and BLOODCOUNT_EXAMS.EXAMLINK.
    (I see this linking error quite often)

    Not sure those tables should be linked....I don't understand the data enough to see the relationships.

    Also I noticed you have added quite a few look up fields in some tables. Not a good idea... see http://access.mvps.org/access/lookupfields.htm

    Another thing I was looking at .... it looks to me that the ANALYSER (sic) fields are in too many tables. If you order an exam, do you specify the analyzer or is the analyzer part of the test results??


    You have added tables for BloodCount and Urine tests. What other/how many tests/exams are there? Are you going to add a results table (junction) for each of the other tests/exams?
    From the EXAMS table, it looks like there are 54 total tests (so far)........

  4. #34
    TONYWALKER is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    21
    Quote Originally Posted by ssanfu View Post
    From the PM......
    I've looked at the relationships and am confused. It looks like you have added (at least) 3 junction tables, plus 2 other tables.

    The main problem is probably the linking. I marked up a picture of the relationships...
    Click image for larger version. 

Name:	Relationship1.png 
Views:	21 
Size:	198.2 KB 
ID:	30820
    The main question was about getting a FK in a query. The 3 tables are using the wrong linking fields.

    Exams.ExamID_PK should be linking to URINE_EXAMS.EXAMLINK and BLOODCOUNT_EXAMS.EXAMLINK,
    NOT Exams.Exam linked to URINE_EXAMS.EXAMLINK and BLOODCOUNT_EXAMS.EXAMLINK.
    (I see this linking error quite often)

    Not sure those tables should be linked....I don't understand the data enough to see the relationships.

    Also I noticed you have added quite a few look up fields in some tables. Not a good idea... see http://access.mvps.org/access/lookupfields.htm

    Another thing I was looking at .... it looks to me that the ANALYSER (sic) fields are in too many tables. If you order an exam, do you specify the analyzer or is the analyzer part of the test results??


    You have added tables for BloodCount and Urine tests. What other/how many tests/exams are there? Are you going to add a results table (junction) for each of the other tests/exams?
    From the EXAMS table, it looks like there are 54 total tests (so far)........
    Thank you for the reply !!!!

    The Exams Table is perfect, thanks to you!
    The "logical" path ( i know its hard to understand, but this Greece..) of exams 50 and 51 is that when a user select them, the are linked to the two other tables, because they have more than one results (exam that includes a group of exams). One thought was to create two tables to store the results for exams 50 and 51, link them with the pk of exams (one to one, the yellow lines, yes wrong the should be linked to pk) and the results must be linked to the fk from exam_order. The missing link you refer to ... ()

    Now the problem is that two of the exams include more exams (50 and 51), (one exam to many exams, each one with results). For example, exam with pk 50 is bloodcount exam which includes(i use number because description is at Greek), White blood count(WBC), Red Blood count(RBC), Platelets(PLT), Hb, Hct etc, with different result each. So the result for exam(50) bloodcount " includes" more results. The same thing happens to exam with pk 51, urine examination wich includes about 9 parameters with results. Thats why i linked the two exams to tables bloodcount_exams and urine_exams, with one to one relation.
    The analyser... big story, if you think that should be text, and no look up, i ll cange it. It is used to group results, its a quite critical table, but from the link above i see it could cause mailfunctions. The final results given to the patient are grouped by the analyser.
    One thought is to make a table for each analyser, i think that solves problems.(open to suggestions) The difference will be that all the exams from the new tables will be added, there will be no selection for the user.The problem with this solution is logistical because even though they are orderd as one exam and paid as one, they include a group of tests with results,one analyser (..sic) is linked to specific results. I think one counter solves this problem(counting orders to bloodcout and urine). If this solution works better i will make the new tables.

    I really thank you for the time spend and i dont have words to express my respect, im greatfull!!!

  5. #35
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,842
    I hid a lot of tables to make the images clearer.

    This is the relationships for UA:
    Click image for larger version. 

Name:	UA1.png 
Views:	17 
Size:	160.8 KB 
ID:	30830

    Here is the relationships for BC:
    Click image for larger version. 

Name:	BC1.png 
Views:	17 
Size:	167.4 KB 
ID:	30831


    First, In the patients table there is a field for ICD10 codes. I suggest that field be moved to the EXAMS table. Does a patient have an ICD10 code or do tests/Exams have ICD10 codes?

    Second, I would rename a few tables.
    Table "EXAMS": Instead of Exams, I would use Tests or Lab Tests. (I think of Exams as more like Physicals.) So maybe rename the table "Exams Available" or "TestsAvailable: or "ListOfExams" or "ListOfTests".
    Table "EXAM_ORDER": this is a junction table between "Patients" and "Doctors". I would use something like "jctPatientDoctorVisit". (jct = junction)
    Table "ORDER_RESULT": this is a junction table between tables "Exams" and "jctPatientDoctorVisit". I would use something like "jctPatientDoctorVisit".

    Another question. For example, a Urinalysis (PK = 51) test/Exam, could there be two different Analyzers used for UA tests?? If Yes, then the field "Analyser_FK" in table EXAMS" should be removed and be in the results table.



    So lets set up a hypothetical situation.
    There is a patient, "Luigi" (PK = 100) (Luigi’s Pizza)
    There is a doctor, "Dr. Milano" (PK= 120) (actually, Milano's is a Pizzeria here mmmmmm )

    Follow on the UA relationship image----

    On 19 Oct, 2017, Luigi has an appointment with Dr. Milano.
    In table "EXAM_ORDER", the record would be
    ExamOrderID_PK = 10
    PatientID_FK = 100
    DoctorID_FK = 120
    ORDER_DATE = 19/10/2017 (dd/mm/yyyy)

    The Dr orders two tests(Exams): Urinalysis (PK = 51) and Blood Count (PK = 50).
    In the table "ORDER_RESULT", there would be 2 records:
    Record 1
    ------------
    OrderResultID_PK =11
    ExamOrderID_FK = 10
    EXAMID_FK = 51 (Urinalysis)
    RESULT = ???
    RESULTDATE = ??


    Record 2
    ------------
    OrderResultID_PK =12
    ExamOrderID_FK = 10
    EXAMID_FK = 50 (Blood Count)
    RESULT = ???
    RESULTDATE = ??



    Maybe you need a "Results" table:
    Click image for larger version. 

Name:	Results1.png 
Views:	16 
Size:	61.0 KB 
ID:	30829

    The record for Exam = 50 would look like
    LabResultsID_PK = 20
    OrderResultsID_FK = 11
    LabValue = 10
    Units = "mg/L"
    LabDesc = "White blood count"

    LabResultsID_PK = 21
    OrderResultsID_FK = 11
    LabValue = 15
    Units = "mg/L"
    LabDesc = "Red blood count"

    LabResultsID_PK = 22
    OrderResultsID_FK = 11
    LabValue = 11
    Units = "mg/L"
    LabDesc = "Platelets"


    LabResultsID_PK = 22
    OrderResultsID_FK = 11
    LabValue = 11
    Units = "mg/L"
    LabDesc = "Hb"


    LabResultsID_PK = 23
    OrderResultsID_FK = 11
    LabValue = 11
    Units = "mg/L"
    LabDesc = "Htc"


    Same type of records for EXAM = 51 (UA), but would be:
    LabResultsID_PK = 24
    OrderResultsID_FK = 12
    LabValue = 11
    Units = "mg/L"
    LabDesc = "SomeThing"

    LabResultsID_PK = 25
    OrderResultsID_FK = 12
    LabValue = 20
    Units = "mg/L"
    LabDesc = "AnotherThing"
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #36
    TONYWALKER is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    21
    Click image for larger version. 

Name:	solution 2.png 
Views:	16 
Size:	64.3 KB 
ID:	30835
    And how you can tell each time wich Orderesultid refers to exams 50 and 51? I ve lost it somewhere ...Lab results is for all the exams or just the two.. tricky ones bloodcount and urine?
    I was thinking of a table for each analyser ( to program the exams and expect result). For example Analyser1Results (select exams-biochemystry) , analyser2results(select exams-anosological), analyser3results(all exams-bloodcount), analyser4results(all exams- urinexam).And all of the above under the same Fk (patientdoctorvisit) exam_order for me. This way no look up for analysers ( but seperated). I will be back with the new structure.
    One doctor (Tree top id, exam order) asks exams from several different analysers,(with whatever combination) all linked to the tree top order_id.

    solution 2(as a sample for relations)
    i start with the doctor, because the things with patient are clear ... Doctor for some reasons (icd10) orders Pizza (analyser1) with musrooms, ham, tomato... (exams select) , carbonara(analyser2) always with mushroom, garlic, ham, cream- wich can have also with pepper but not avaliable yet (standard exams- avaliable selection) etc ... (these are the two options)
    so.. 4 tables 2 with multi selection like the one you have made , and 2 urine and bloodcount with default all exams.( or selected - the ones who are avaliable)
    If it is ok with this sructure, there will be 5 tables, 3 with selection and 2 with all the exams of the table( i will add one more table coagulation_exams)

    Questions to you as a programer,
    -Is it better to have one big table, or three smaller ones?
    yes: i ll split the tables to three.
    no: i ll put all the exams to one table, keep the look up for analysers and try to find a way to hide all accosiated exams under one click, like a batch entry.

    -Are look up so evil?For example if i make a query to sort exams 1)by patient 2)by order 3) by analyser will it be messy??? If i use bult in look up, will it be more flexible?(for example, seven names for the... sic analysers ,not with linked tables, just type the values)
    Whats your opinion for mult select look up? (ICD10)


    ps.. i dont know for the pizza, or carbonara but i really need some beers, ive already started painkillers for headache !!!!!!!!!
    Attached Files Attached Files
    Last edited by TONYWALKER; 10-16-2017 at 02:13 PM.

  7. #37
    ssanfu is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,842
    Sorry about the pizza - it was late and I was hungry....
    but i really need some beers, ive already started painkillers for headache !!!!!!!!!
    I thought the beer WAS the painkiller!!!

    (some of ) My rules:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.

    IMO, look up FIELDS are only useful if you work directly with tables. Since I never allow access to tables.......
    multi-value fields are not a normalized structure. They hard to work with; Access sets up a normalized structure (junction table) behind the scenes, but you can't easily use the MVF.
    calculated fields are better done in queries.


    I would think that the ICD10 code would be in the EXAM table for the specific exam/test. With it in the EXAM_ORDER (visits) table, there can only be 1 ICD10 code. The tables for all of the EXAMS/tests ordered for that visit is gone/missing.


    With the structure proposed in the previous post, I think you have gone backwards in the design.
    BLOODCOUNT_Exams, biochemical_exams, URINE_EXAMs and anosology_exams are all EXAMs and should be in the same table (IMHO). These are all tests/EXAMs that can be ordered.

    Each test/EXAM has many results, but one result set is for one EXAM/test. This is a 1-to-many relationship, not a many-to-many which requires the junction table (which you have as Bllodcount_result). The table "BLOODCOUNT_Exams" already has general data entered - where/which table do the lab results go into???
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #38
    TONYWALKER is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    21
    Imagine you are going to do a rutine checkup, you go to a doctor which gives a paper with exams/tests

    workflow
    1 Patient (with demographics, personal data ) comes to the lab
    2 Brings paper ( for me is like an order, with price etc) with writen exams from his doctor, for some reasons= ICD10 ( more than one value- probably subform) - Doctor name, order id, date.The exams have specific coding.For me products are the results. (ICD10 refers to patient, the medical reason for the test yes, but refers to order or patient an not to exam/test.)
    3. One machine do a batch of exams and each exam/test is done only by one analyser. There are more than one machines. You select the exams from the paper above and each machine does the selected exams and gives results.
    -The paper above may have exams done by many machines. Thats why analyser table is important.
    -Exam referd to the paper as bloodcount and urine examination has a specific batch of exams. To make it clear bloodcount= WBC,RBC,PLT,MCV, LYMPH,MONO,EOS,PLCR, HCT,HB,MCHC, ( a group of products = group of results, but each on with values such as upper lower limit, units) and urine has 10 exam parameters.
    3. I recieve the results from the machines and write them to db for each test. (data- input)
    4 The final product is a paper with results for all the exams ordered (thats why all the exams refer to order id) from the doctor for the specific patient. For the better view of the exam result, they form a group, most times following the kind of the macine (analyzer) that gives the results , or the group of exams described above (bloodcount and urine exam). There are many orders to many exams/results, many to many relation.

    Rolling back, the problem i faced was the group of exams , bloodcount and urine. Even though the are ordered as one exam (and paid as one) they include many exams with results. This group is very specific though, done by one machine for each group.
    I think the very first design was the best and more logic. One order with many exams, with one result for each.
    The whole picture
    One patient with many orders, and each order has a number of exams with results. Tables include data that repeat many times, and junction tables new data (results) creating a link between order and results, at least... thats my point of view after ..some beers.

    - i liked the comment for the pizza, no need to say sorry!!!!
    Last edited by TONYWALKER; 10-18-2017 at 12:48 AM.

  9. #39
    TONYWALKER is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    21
    I came up with the idea to use one to one relationship for the multi value exams/tests.(like the example with the mishmached links....)
    One exam linked with a table with the related tests....
    Bloodcount , holds a unique value through primary key, assoume it is 1. The same thing happens to urine exam, assoume it is 4.
    Is it possible by using one to one relation to link the with the tests, these exams include?
    For example a table named tblbloodcount_tests with the fields :
    BloodcountTests_PK, FK the number of Bloodcount (1) linked with exams table with one to one (defualt price for this field), low, high, meas_units.

    For the tblurine_tests:
    Urinetests_PK , FK the number of urine exam(4) linked with exams table with one to one (defualt price for this field), low, high etc

Page 3 of 3 FirstFirst 123
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