Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 52
  1. #31
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.

  2. #32
    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 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 Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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:	55 
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's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    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:	55 
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 Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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:	52 
Size:	160.8 KB 
ID:	30830

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

Name:	BC1.png 
Views:	51 
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:	50 
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"

  6. #36
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Click image for larger version. 

Name:	solution 2.png 
Views:	49 
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 03:13 PM.

  7. #37
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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???

  8. #38
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    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 01:48 AM.

  9. #39
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    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

  10. #40
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Dear members of the forum,
    - This is the design i ve end up. I have added a table (tbl_testgroup) to join test order and tests. There are some concerns about circular reference (probalbly to test select and results)
    - I ve used multiple fields for results, because some of them are numeric, other text and other lookup values (IMHO it is the simplest solution for me)
    I want to help create a combo form wich will add the tests under each test group, to jtblresults. I ve asked for help to other post (design) but some members deny to help me,( i dont blame them at all, they really tried to help by sending an example but it was too complicateted fro me to analyse) I really need some help because it is too hard for me and i will save a lot of time.
    Attached Files Attached Files

  11. #41
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked at your relationships and am a little confused on a couple of tables.
    Here is the original relationship diagram (with comments):
    Click image for larger version. 

Name:	OrgRelationship.png 
Views:	38 
Size:	166.8 KB 
ID:	31143

    I don't understand why there are tables "jtblOrderTests" and "tblTests". I would combine them.
    Consider this relationship diagram:
    Click image for larger version. 

Name:	SuggestedRelationship.png 
Views:	39 
Size:	171.9 KB 
ID:	31145

    What is the purpose of "jtblNormal_Values"?


    A patient can have a visit, with a doctor, on a specific date, in a healthcare_unit.
    A doctor can order multiple tests, that is in a testgroup, with specific analyzers.
    Each test can have multiple results? Is a TEST_NAME the same as GR_Description?

  12. #42
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    Thank you Steve, really!!!
    A person comes to laboratory with a paper from the doctor. In this paper Doctor from a healthcare unit, writes down the tests for the person (patient). One patient could have several orders (one to many) wich can ahve several tests (one to many).(...many to many for patient - tests) . Sometimes i only have the blood sample and the test order paper 9external sample) so i must know where to send the report= to the doctor from the healthcare unit.
    You must be very healthy!!! ... havent you ever done test to a medical laboratory?... its a standard routine procedure.
    There is a code system for the doctor and me for the tests (edapy , i dont use it as a PK because it changes all the time...). The problem with the code system is that some tests include more tests ( the borring bloodcount example ).. wich must have results. Some test have numeric results, some lookup ( like possitive or negative) and some text
    Finaly the the person (patient) must have back the test results (printed) with the the normal values depending on male- female- child(tblgroup) which are stored to tblnormalvalues

    How results are linked to testorder, in your structure?? (P
    robably the name confused you, jtbl_testorder= jtblorder , linked with jtblOrderexams)
    - Lets start from the easy part, yes gr_description is the test name in Greek, i ve added the field so you can understand the fields.I will keep international names for tests. Mothername and fathername are the name of the father and mother of the patient ( i have orders to have them as fields) .Med_history is a field to write some info about the patient(as comments.. but with more ... emphasis)
    - The tblnormal values now.... There are different normal values for man, woman and child for almost each test (i ve named it group and not gendre because of the child record)
    - Testgroup can have one or more tests, if you have seen the records, there are really only 5 or 6 testgroups(more than one test). Thats because i have to follow the Greek code system for ordering test. As - You can see that testgroup has to do with test prices. For ex. The user at perscription (order) see " Bloodcount", but there must be programed and have results for 20 tests(wbc, rbc, hct, hb,lym,neut,mix..etc)
    -Each test(and test group) belong to a specific analyzer( or 'sic' analyser.. lol)
    -Each ordered test can have only one result, but this could be number, text, or lookup selection.Thats why there are many result fields. Of course i will have blank fields, but i couldnt find another way to solve it (at least with my knowledge). So there will be a value only to one of the 5 result fields. I will hide the fields not used at the form.
    Can i use names for PK?(like residence and socseq) or i must follow pk=autonumb, field etc?
    i ve upload the new structure, do you think that i must put results to jtblordertests?
    And if you remember the combo form you have created... instead of adding tests to test order, the user select from tbltestgroup_edapy and add all the tests under this group... is that possible?

    (Sorry about the reserved words and spelling... i work almost 12-15 hours a day and three children wait for me at home... i almost sleep walk when im writting...dash.. underscore... look the same..just a shift apart)
    Attached Files Attached Files
    Last edited by TONYWALKER; 11-08-2017 at 12:30 PM.

  13. #43
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can i use names for PK?(like residence and socseq) or i must follow pk=autonumb, field etc?
    Since you are storing a text field, yo can use residence and socseq as field names. But since you are storing the text value, there is no need to link the tables.

    In table "tblPatients", there is a field named "YEAR". What is this for? I renamed it "YEAR_Patient" - "YEAR" is a reserved word.

    In table "tblAnalyzers", there are two fields - I renamed "Name" to "Analyzer_Name" and I renamed "Date" to "Analyzer_Date". What is the date field for?

    In table "tblDoctors", there is a field named "Number" - I renamed to "Doctor_Number". What is the field "Number" for?

    Is "tblPatients.HEALTHCENTER_ID" different than "tblHEALTHCareUnit.Monda"? (Monda" is the closest I can come to your field name )



    So now I have:
    Click image for larger version. 

Name:	NewRelationship1.png 
Views:	32 
Size:	115.5 KB 
ID:	31207

    A person comes to laboratory with a paper from the doctor. In this paper Doctor from a healthcare unit, writes down the tests for the person (patient). One patient could have several orders (one to many) wich can ahve several tests (one to many)
    So if I go to the laboratory (Yes, I am healthy -rarely visit the doctor), do I have only 1 order or could I have 4 orders, each with 6 tests? Do the orders have ID numbers? (like an invoice number)
    If I have multiple orders from a doctor, are the orders grouped or are they individual orders?


    Does a doctor work at one healthcare unit or many healthcare units?

  14. #44
    TONYWALKER's Avatar
    TONYWALKER is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2017
    Location
    Greece
    Posts
    43
    I REALLY HOPE YOU STAY THAT WAY STEVE!!!
    1) You could have any combination of orders, at the same time, from multiple doctors.So yes there is an invoice number (dapy field at jtblorders).
    2) Analyzer year, is the manufacture year of the machine.
    3) A number uniqe for the doctor
    4) patients have some other ids from other health care institutions.... paperwork ids...
    5) A doctor could be at several healthcare units (departments), a healthcare unit is like to which department the result could go. Its linked to order and it has not have any fixed relation with the doctor.

    *****)This is a very good question!!! How the orders are grouped. They group under a sample ... so before the jtblorder there must be a table sample,with fields : sample_id, drown date.
    This way .... there comes up a new question, how many and what kind of samples. This depends on selected tests. Each analyser must have a specific kind of sample (mainly whole blood, blood, urine..see uploaded db). So you know you have sample, but you dont know how many and what kind until you input to db the tests. These samples could have the same sample id. Ex. one patient could have blood , whole blood , urine as samples which refer to the same order, but hey all reffer to sample.
    As for the results the final printout are grouped by analyzer.( for the sample)
    Click image for larger version. 

Name:	dbmed.png 
Views:	27 
Size:	76.3 KB 
ID:	31209
    Attached Files Attached Files

  15. #45
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1) You could have any combination of orders, at the same time, from multiple doctors.So yes there is an invoice number (dapy field at jtblorders).
    1) So if I walk in with 6 orders (even though I don't know what an order looks like) from 3 doctors, are there 6 DAPY numbers (invoice number) or is there 1 DAPY number?
    (It finally sunk in that you are in a laboratory, not a Doctor's office. Doh!! )

    If each order has its own DAPY number, then table "tblSamples" is not needed.
    If all orders are under 1 DAPY number, then table "tblSamples" is needed.

    Another question
    2) ICD-10 codes.... My understanding is that the codes are for specific procedures or tests. Why do you have them linked to Patient?


    At the 10,000 foot level, how does this look? Red arrows are data entry flow.... (Note: many tables are hidden)
    Enter the sample(s) for a patient. Enter the orders. Enter the tests.
    1 sample has many orders. Each order has many tests.

    Click image for larger version. 

Name:	Relationship2.png 
Views:	26 
Size:	152.3 KB 
ID:	31211

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

Similar Threads

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