This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 8, 2012 8:50 AM by Andreas Weiden RSS

inserting multiple values on one column ?

newbi_egy Explorer
Currently Being Moderated
hi all ,
i have a table with two columns :
1- patient_name .
2- test_name .

there are patients will do more than one test , like cbc test and hemoglobin test ,
how can i insert these to values in the test_name column , how would it stored in the database , how
would it appear when i select it .

thanks
  • 1. Re: inserting multiple values on one column ?
    yoonas Expert
    Currently Being Moderated
    Hi,

    Hope you already have a table with patient details and another one with test details.
    Create a table which stores patient test details, for each test a patient takes you can stores the id of the patient and id of the test. so each time a patient takes a test you will insert one record in this table with patient id and test id.


    Regards
    Yoonas
  • 2. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    thanks , but unfortunately , you did not get me ,
    this is the same situation here , inserting one id the same as inserting one test name ,

    i do not want to insert one test , i want to insert multiple tests to one patient ,

    what you've said is insert one id to one patient , this is the same .

    thanks for your prompt reply , but please provide me with another help
  • 3. Re: inserting multiple values on one column ?
    Andreas Weiden Guru
    Currently Being Moderated
    Yoounus is right, you should create a detailtable for the tests on one patient. Putting detail information into one field will only cause trouble. If you really want to have it as one column in your table, you can use a nested table, but in fact its the same thing just in other terms, and more complicated to use in most cases.
  • 4. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    i do have a table for tests(TESTS) , but what i am talking about is inserting in the table , how?

    how do i insert two values or more for a particular patient ?


    sure i will insert the values in the (tests) column of (patients) table .


    regards
  • 5. Re: inserting multiple values on one column ?
    yoonas Expert
    Currently Being Moderated
    Hi,

    For each test a patient takes you can have a seperate record
    Insert into tests(patient_id,test_name) values(xxxx,'forst_test');
    Insert into tests(patient_id,test_name) values(xxxx,'second_test');
    Insert into tests(patient_id,test_name) values(xxxx,'third_test');

    Regards
    Yoonas
  • 6. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    do you want me to create (tests table) with columns (patient_id , test_name) , and the patient_id column is
    not a primary key column , then i insert in it like you've said ?
  • 7. Re: inserting multiple values on one column ?
    Andreas Weiden Guru
    Currently Being Moderated
    If you have a one table for patients and one for tests, create a third table patient_test, which only holds the valid combination of tests and patients (which patient has done which test).

    so you would have tables like
    CREATE TABLE TEST (
      ID NUMBER NOT NULL, as primary key
      NAME VARCHAR2(80)
    );
    
    CREATE TABLE PATIENT  (
      ID NUMBER NOT NULL, as primary key
      NAME VARCHAR2(80)
    );
    
    CREATE TABLE PATIENT_TESTS (
      ID NUMBER NOT NULL, as primary key
      PATIENT_ID NUMBER NOT NULL, as fk to patient (ID)
      TEST_ID NUMBER NOT NULL as fk to test (ID)
    );
  • 8. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    every patient come to the doctor to do a test , i will insert his data in a table (patient_name , test , ...) ,
    in which table i will insert these data , is it one of the mentioned tables ? or i have to build another
    table to hold these data ?

    Edited by: semsem on Nov 14, 2012 2:38 AM
  • 9. Re: inserting multiple values on one column ?
    yoonas Expert
    Currently Being Moderated
    Hi,

    See the post by Andreas, is it not very clear?

    What are the tables you have already?

    Basically you should have a table for pateints which will have information pertaining to patients. Another one for test details which will have info about each tests you have in your hospital
    The third one to store details of each tests taken by a patient, you can save patientid from patients table and testid from test table and if you want some other info that also. So each time a patient takes a test you will have patient id and test id inserted into this table.

    Regards
    Yoonas
  • 10. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    hi yoonus , to clear the whole issue ,
    i have a table named (patients) , it is dedicated to insert patients data , when they come to the hospital to do a test .
    some of these table are (patient_id as primary-key , patient_name , test(the test he will do) , there is a block in my application represents this table in which i will insert patients' data .


    the problem is any patient come to the hospital may perform more than one test , so how can i insert these tests , and
    assign them to this specific patient , and which item will i use to insert these multiple tests (text item , t-list , .... or what)

    this the first problem , let us solve it first , and the speech has a rest

    thanks in advance
  • 11. Re: inserting multiple values on one column ?
    Andreas Weiden Guru
    Currently Being Moderated
    Well, i would strongly suggest to read some stuff about data modelling and normalization. As one patient can do more than one test, these two informations do not belong into the same table. Thats why i suggested the datamodel with the three tables.
  • 12. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    i can agree with your suggestion andrea , but tell me how to insert these data , and in which table will i insert it .

    thanks
  • 13. Re: inserting multiple values on one column ?
    Andreas Weiden Guru
    Currently Being Moderated
    I don't understand your question. You insert the data as in any other table, either by using INSERT or by creating a datablock in forms.

    I'd say you have two block, PATIENTS is the master-block, PATIENT_TESTS is the detail-block, the PATIENTS-block has a relation to the PATIENT_TESTS-block. In the PATIENT_TEST-block, you have a lookup using a lov to select the appropiate record from TEST.
  • 14. Re: inserting multiple values on one column ?
    newbi_egy Explorer
    Currently Being Moderated
    i've a problem about analysis issue , so i searched for another solution , and i want to know , if there is a problem
    to use this solution or not ?

    i thought of adding 7 or 8 columns for tests in the patients table as follows :

    test1 , test2 , test3 , .......
    the first for the first test , the second for the second test , ............

    and each column of them represents a field in my form , all of them are invisible except test1 , and if patient needs to
    do another test , i'll show the test2's field , and so on .

    is there something wrong with this solution ?

    thanks again
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points