Results 1 to 2 of 2

Thread: Array Help

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Array Help

    Table "tbl_DistList_DEP_Duplicate" I use to sum amount from table tbl_DistList_DEP thru query and after sum i put the values
    back from table tbl_DistList_DEP_Duplicate into table tbl_DistList_DEP. Instead of copying values into 2nd table "table
    tbl_DistList_DEP" i want to put the query result into an array. My question is how to i create an array which hold the query
    results and then how to i put the values back into table tbl_DistList_Dep?

    Thanks.


    sDataSource = "SELECT CLT_ID, Sum(CUR_BAL) AS SumOfCUR_BAL, Count(Name1) As DuplicateCount ,List_date, " & _
    "Debtor_id , Name1, Name2, Worklist, Oldworklist, next_Contact " & _
    "From tbl_DistList_DEP GROUP BY CLT_ID, List_date, Debtor_ID, Name1, " & _
    "Name2, Worklist, Oldworklist, next_Contact "

    Set rsADOSQL = cnADOSQL.Execute(sDataSource)
    cnADOSQL.Execute ("DELETE tbl_DistList_DEP_Duplicate")
    oDBUtil.OpenADORecordSet "tbl_DistList_DEP_Duplicate", cnADOSQL, rsADO, 0, adCmdTableDirect
    rsADOSQL.MoveFirst
    Do While Not rsADOSQL.EOF
    rsADO.AddNew
    rsADO!Clt_id = rsADOSQL!Clt_id
    rsADO!Cur_bal = rsADOSQL!SumOfCUR_BAL
    rsADO!List_date = rsADOSQL!List_date
    rsADO!Debtor_id = rsADOSQL!Debtor_id
    rsADO!Name1 = rsADOSQL!Name1
    rsADO!Name2 = rsADOSQL!Name2
    rsADO!Worklist = rsADOSQL!Worklist
    rsADO!Oldworklist = rsADOSQL!Oldworklist
    rsADO!Next_Contact = rsADOSQL!Next_Contact
    rsADO!Duplicate_Count = rsADOSQL!DuplicateCount
    rsADO.Update
    rsADOSQL.MoveNext
    Loop

    sDataSource = "SELECT * from tbl_DistList_Dep_Duplicate"
    Set rsADOSQL = cnADOSQL.Execute(sDataSource)
    cnADOSQL.Execute ("DELETE tbl_DistList_DEP")
    oDBUtil.OpenADORecordSet "tbl_DistList_DEP", cnADOSQL, rsADO, 0, adCmdTableDirect
    rsADOSQL.MoveFirst
    Do While Not rsADOSQL.EOF
    rsADO.AddNew
    rsADO!Clt_id = rsADOSQL!Clt_id
    rsADO!Cur_bal = rsADOSQL!Cur_bal
    rsADO!List_date = rsADOSQL!List_date
    rsADO!Debtor_id = rsADOSQL!Debtor_id
    rsADO!Name1 = rsADOSQL!Name1
    rsADO!Name2 = rsADOSQL!Name2
    rsADO!Worklist = rsADOSQL!Worklist
    rsADO!Oldworklist = rsADOSQL!Oldworklist
    rsADO!Next_Contact = rsADOSQL!Next_Contact
    rsADO!DuplicateCount = rsADOSQL!Duplicate_Count
    rsADO.Update
    rsADOSQL.MoveNext
    Loop

    --------------
    sDataSource = "SELECT CLT_ID, Sum(CUR_BAL) AS SumOfCUR_BAL, List_date, Debtor_ID, Name1, " & _
    "Name2, Worklist, Oldworklist, next_Contact " & _
    "From tbl_DistList_DEP GROUP BY CLT_ID, List_date, Debtor_ID, Name1, " & _
    "Name2, Worklist, Oldworklist, next_Contact "

    Set rsADOSQL = cnADOSQL.Execute(sDataSource)
    cnADOSQL.Execute ("DELETE tbl_DistList_DEP_Duplicate")
    oDBUtil.OpenADORecordSet "tbl_DistList_DEP_Duplicate", cnADOSQL, rsADO, 0, adCmdTableDirect
    rsADOSQL.MoveFirst
    Do While Not rsADOSQL.EOF
    rsADO.AddNew
    rsADO!Clt_id = rsADOSQL!Clt_id
    rsADO!Cur_bal = rsADOSQL!SumOfCUR_BAL
    rsADO!List_date = rsADOSQL!List_date
    rsADO!Debtor_id = rsADOSQL!Debtor_id
    rsADO!Name1 = rsADOSQL!Name1
    rsADO!Name2 = rsADOSQL!Name2
    rsADO!Worklist = rsADOSQL!Worklist
    rsADO!Oldworklist = rsADOSQL!Oldworklist
    rsADO!next_Contact = rsADOSQL!next_Contact
    rsADO.Update
    rsADOSQL.MoveNext
    Loop

    sDataSource = "SELECT * from tbl_DistList_Dep_Duplicate"
    Set rsADOSQL = cnADOSQL.Execute(sDataSource)
    cnADOSQL.Execute ("DELETE tbl_DistList_DEP")
    oDBUtil.OpenADORecordSet "tbl_DistList_DEP", cnADOSQL, rsADO, 0, adCmdTableDirect
    rsADOSQL.MoveFirst
    Do While Not rsADOSQL.EOF
    rsADO.AddNew
    rsADO!Clt_id = rsADOSQL!Clt_id
    rsADO!Cur_bal = rsADOSQL!Cur_bal
    rsADO!List_date = rsADOSQL!List_date
    rsADO!Debtor_id = rsADOSQL!Debtor_id
    rsADO!Name1 = rsADOSQL!Name1
    rsADO!Name2 = rsADOSQL!Name2
    rsADO!Worklist = rsADOSQL!Worklist
    rsADO!Oldworklist = rsADOSQL!Oldworklist
    rsADO!next_Contact = rsADOSQL!next_Contact
    rsADO.Update
    rsADOSQL.MoveNext
    Loop

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    hmmmm... not sure what's going on here.

    Looks like users add detail records, but after a certain period of time
    you're no longer interested in detail, but only in summary data?

    You can save yourself a lot of processing time by not looping through the recordsets and appending one record at a time.
    Use the INSERT INTO instead.

    Code:
    ...
    cnADOSQL.Execute ("DELETE tbl_DistList_DEP_Duplicate")
    sDataSource = "INSERT INTO TBL_DISTLITS_DEP_DUPLICATE (CLT_ID, " _
        & "CUR_BAL, DUPLICATE_COUNT, LIST_DATE, DEBTOR_ID, NAME1, "_
        & "NAME2, WORKLIST, OLDWORKLIST, NEXT_CONTACT) " _
        & "SELECT CLT_ID, Sum(CUR_BAL) AS SumOfCUR_BAL, Count(Name1) " _ 
        & " AS DuplicateCount ,List_date, Debtor_id , Name1, Name2, Worklist," _
        & " Oldworklist, next_Contact From tbl_DistList_DEP " _
        & "GROUP BY CLT_ID, List_date, Debtor_ID, Name1, "  _
        & "Name2, Worklist, Oldworklist, next_Contact "
    cnAdoSql.Execute (sDataSource)
    ...
    'repeat to put the summary data back into the original table
    Inspiration Through Fermentation

Posting Permissions

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