locked
Consolidating multiple records into a single record RRS feed

  • Question

  • Hi Everyone,

    I have a single table which I'm trying to consolidate multiple records with the same account number into a single record.  Here's a description of what I'm trying to do.  Any help is much appreciated.

    ======================

    record: 1

    acctno: 12345

    Name: John Doe

    Child: John Doe Jr

    Medplan: ABCD

    ======================

    record: 2

    acctno: 12345

    Name: John Doe

    Child: Jane Doe

    Medplan: EFJK

    =======================

    New Record

    acctno: 12345

    Name: John Doe

    Child1: John Doe Jr.

    Medplan1: ABCD

    Child2: Jane Doe

    Medplan2: EFJK

    Thursday, February 16, 2012 4:19 AM

Answers

  • Hi,

    like Naomi I don't see a reason for consolidating your records for another reason than displaying them.

    On the other hand, if you indeed hold your data that way in one table, then it might be a good idea to give you some ideas on data/table normalisation.

    Ad hoc, you could split your one table into four different tables to hold your data.

    1. Table: adresses. It will contain name, adress, etc. AND: it will also contain records for childs, wife, husband, etc. as those are adresses, too.

    2. Table: patients. It will contain your primary key and the patients accountnumber

    3. Table: medplans. It will contain the patients primary key (as foreign key) and all medplans (1 record each) including a timestamp and comments. its more like a history table.

    4. Table: relatives. It will contain the patients primary key (as foreign key) and all adressnumbers of the known relatives (1 adressnumber per row) and a status field that defines the degree of relationship. i.e. 1=wife,2=husband,3=child,etc.

    And every time, you are in need of additional data to store about a patient, think about how often you will have to store these infos. As soon as you recognize, that this will be more than once: Add another table, create the recerence to the master table (-> patients) via primary key as foreign key and store your data there.

    creating your database that way will make your life a lot easier. That way you won't get into sweat, in case a patient has more than two childs or you have to store sister, brother, uncle, grandmother , spouse or whatever relationships.


    Gruss / Best regards -Tom 010101100100011001010000011110000101001001101111011000110110101101110011


    • Edited by Tom BorgmannEditor Thursday, February 16, 2012 7:24 AM foreign key concept added
    • Proposed as answer by Naomi N Thursday, February 16, 2012 2:15 PM
    • Marked as answer by Pavel Celba Saturday, February 25, 2012 10:13 PM
    Thursday, February 16, 2012 7:21 AM
    Answerer

All replies

  • I'm guessing you're talking about presenting the information this way, not by actually storing the info the specified way. For presenting you just need to ensure correct ORDER BY in your select statement to logically group records together.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, February 16, 2012 4:37 AM
  • Please write a program to do this

    set talk off

    set dele on

    set date brit

    set cent on

    sele 1

    use tbl1

    index on acctno tag acctno addit

    sele acctno,count(*) from tbl1 group by acctno into dbf tbl2.dbf

    sele tb2

    use

    sele 2

    use tbl2 exclusive alias tbl2

    go top

    do while !eof()

          sele tbl1

          set orde to acctno

          go top

          seek tbl2.acctno

          do while tbl2.acctno = tbl1.acctno

               for i = 1 to tbl12.cnt

             j = 1

            k = 1

              var1 = 'CHILD'+allt(str(j))

              var2 = 'MEDPLAN'+allt(str(k))

                sele tbl3

                appe blank

                repl acctno with tbl1.acctno

                repl name   with tbl1.name

                repl (var1)   with tbl1.child

                repl (var2)  with tbl1.medplan

                j = j + 1

                k =  k + 1

             next i

         sele tbl2

         if !eof()

            skip

         endif

         enddo

    using macro substitution here and already you would have created tbl3 with pre defined fields like child1, child2, child3 etc.  this you can find by using select stmt of how many child exist and how many med plan exist ?


    Lakshmi

    Thursday, February 16, 2012 6:27 AM
  • Hi,

    like Naomi I don't see a reason for consolidating your records for another reason than displaying them.

    On the other hand, if you indeed hold your data that way in one table, then it might be a good idea to give you some ideas on data/table normalisation.

    Ad hoc, you could split your one table into four different tables to hold your data.

    1. Table: adresses. It will contain name, adress, etc. AND: it will also contain records for childs, wife, husband, etc. as those are adresses, too.

    2. Table: patients. It will contain your primary key and the patients accountnumber

    3. Table: medplans. It will contain the patients primary key (as foreign key) and all medplans (1 record each) including a timestamp and comments. its more like a history table.

    4. Table: relatives. It will contain the patients primary key (as foreign key) and all adressnumbers of the known relatives (1 adressnumber per row) and a status field that defines the degree of relationship. i.e. 1=wife,2=husband,3=child,etc.

    And every time, you are in need of additional data to store about a patient, think about how often you will have to store these infos. As soon as you recognize, that this will be more than once: Add another table, create the recerence to the master table (-> patients) via primary key as foreign key and store your data there.

    creating your database that way will make your life a lot easier. That way you won't get into sweat, in case a patient has more than two childs or you have to store sister, brother, uncle, grandmother , spouse or whatever relationships.


    Gruss / Best regards -Tom 010101100100011001010000011110000101001001101111011000110110101101110011


    • Edited by Tom BorgmannEditor Thursday, February 16, 2012 7:24 AM foreign key concept added
    • Proposed as answer by Naomi N Thursday, February 16, 2012 2:15 PM
    • Marked as answer by Pavel Celba Saturday, February 25, 2012 10:13 PM
    Thursday, February 16, 2012 7:21 AM
    Answerer