Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    4

    Unanswered: Show record in column in new rows

    Hi everyone,


    Can any one help me with this? I've developed a system and nearly complete it. In the report part, I found that I need to change the existing report.

    -------------------------------------------------------------------

    a.The table_CustomerInfo (which is used to create report)

    1.SystemJobNumber
    2.ReportedCode
    3.ReportedSymptom
    4.FailureCode
    5.FailureDescription
    6.RepairCode
    7.RepairDescription
    8.SerialNumber
    and other fields (total 40 fields)

    --------------------------------------------------------------------


    b. The record in table_CustomerInfo after inserting data is like below.--------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
    --------------------------------------------------------------------
    10000001 |Code1;Code2; |Symptom1; |FCode1; |
    |Code3; |Symptom2: |FCode2;FCode3;|
    | |Symptom3: | |
    ----------------------------------------------------------------------


    ***** Notice that there are many data in 1 single column **********
    ***** The User can input until 10 data in one column **********


    ----------------------------------------------------------------------

    My problem is the user wants the report to show 1 data in one new row.
    Like the below example.



    --------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
    --------------------------------------------------------------------
    10000001 |Code1; |Symptom1; |FCode1; |
    ---------------------------------------------------------------------
    10000001 |Code2; |Symptom2; |FCode2; |
    ----------------------------------------------------------------------
    10000001 |Code3; |Symptom3; |FCode3; |
    -----------------------------------------------------------------------


    OK.

    I don't want to change the database table design but prefer to show the record in asp pages .This is because in the insert new record page, the user submit the form only once and 1 column can insert many data.

    --------------------------------------------------------------------
    A part of the coding


    Dim rsWeeklyRR
    Dim rsWeeklyRR_numRows

    Set rsWeeklyRR = Server.CreateObject("ADODB.Recordset")
    rsWeeklyRR.ActiveConnection = MM_conDallabTrack_STRING
    rsWeeklyRR.Source = "SELECT tbCustomerInfo.* FROM tbCustomerInfo WHERE (Model = '" + Replace(rsWeeklyRR__strModel, "'", "''") + "') And (ShipDateTime Between '" + Replace(rsWeeklyRR__strStartD, "'", "''") + "' And '" + Replace(rsWeeklyRR__strEndD, "'", "''") + "') And (ServiceCentre='" + Replace(rsWeeklyRR__strSC, "'", "''") + "') ORDER BY SystemJobNum DESC"
    rsWeeklyRR.CursorType = 0
    rsWeeklyRR.CursorLocation = 2
    rsWeeklyRR.LockType = 1
    rsWeeklyRR.Open()
    --------------------------------------------------------------------------



    Can anyone help me ????

    Thanks in advance.

  2. #2
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    4
    Hi again, (the table is not so clear)

    Can any one help me with this? I've developed a system and nearly complete it. In the report part, I found that I need to change the existing report.

    -------------------------------------------------------------------

    a.The table_CustomerInfo (which is used to create report)

    1.SystemJobNumber
    2.ReportedCode
    3.ReportedSymptom
    4.FailureCode
    5.FailureDescription
    6.RepairCode
    7.RepairDescription
    8.SerialNumber
    and other fields (total 40 fields)

    --------------------------------------------------------------------


    b. The record in table_CustomerInfo after inserting data is like below.
    --------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
    --------------------------------------------------------------------
    10000001 |Code1;Code2; |Symptom1; |FCode1; |
    |Code3; |Symptom2: |FCode2;FCode3;|
    | |Symptom3: | |
    ----------------------------------------------------------------------


    ***** Notice that there are many data in 1 single column **********
    ***** The User can input until 10 data in one column **********


    ----------------------------------------------------------------------

    My problem is the user wants the report to show 1 data in one new row.
    Like the below example.


    --------------------------------------------------------------------SystemJobNumber |ReportedCode |ReportedSymptom |FailureCode |.....
    --------------------------------------------------------------------
    10000001 |Code1; |Symptom1; |FCode1; |
    ---------------------------------------------------------------------
    10000001 |Code2; |Symptom2; |FCode2; |
    ----------------------------------------------------------------------
    10000001 |Code3; |Symptom3; |FCode3; |
    -----------------------------------------------------------------------


    OK.

    I don't want to change the database table design but prefer to show the record in asp pages .This is because in the insert new record page, the user submit the form only once and 1 column can insert many data.

    --------------------------------------------------------------------
    A part of the coding


    Dim rsWeeklyRR
    Dim rsWeeklyRR_numRows

    Set rsWeeklyRR = Server.CreateObject("ADODB.Recordset")
    rsWeeklyRR.ActiveConnection = MM_conDallabTrack_STRING
    rsWeeklyRR.Source = "SELECT tbCustomerInfo.* FROM tbCustomerInfo WHERE (Model = '" + Replace(rsWeeklyRR__strModel, "'", "''") + "') And (ShipDateTime Between '" + Replace(rsWeeklyRR__strStartD, "'", "''") + "' And '" + Replace(rsWeeklyRR__strEndD, "'", "''") + "') And (ServiceCentre='" + Replace(rsWeeklyRR__strSC, "'", "''") + "') ORDER BY SystemJobNum DESC"
    rsWeeklyRR.CursorType = 0
    rsWeeklyRR.CursorLocation = 2
    rsWeeklyRR.LockType = 1
    rsWeeklyRR.Open()
    --------------------------------------------------------------------------



    Can anyone help me ????

    Thanks in advance.

  3. #3
    Join Date
    May 2004
    Posts
    125
    To be honest, I would really think hard about changing the db design. I understand the wanting to not change it but in the long run you would probably thank youself. Being lazy on a db design could cost you days of pain down the road.

    But to get back to your question, I would probably use arrays and two loops and build your table. Some like this

    Code:
    do until eof
      arrCol1 = split(column1, delimiter)
      arrCol2 = split(column2, delimiter)
      arrCol3 = split(column3, delimiter)
      'etc for each of the multi value columns
      for x = o to ubound(arrCol1)
         'start building table here to show user
      next 'x
    loop

    Hope this gives some ideas on where I'm going on this. There will probably be a lot of checks and stuff but this may work. If I get more time I'll post back with a better example.

    Good luck
    Last edited by DMWCincy; 01-03-05 at 22:02.

  4. #4
    Join Date
    Dec 2004
    Location
    Malaysia
    Posts
    4

    Wink Thanks DMWCincy

    Hi DMWCincy,

    This example is good already. With a bit adjustment I manage to do the report.

    (If I get more time I'll post back with a better example. ---- )

    And for your advice, I will correct the db design. I'll do a better db design next time.

    Thanks for your advice too.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •