Results 1 to 9 of 9

Thread: How to Update the access table rows with relative rows from another table

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    How to Update the access table rows with relative rows from another table

    Dear All,
    I am creating a simple banking application for my college project in VB.net 2010 with access 2003 as back end.
    I have created four tables as CashDepositReg, CashDepositTran, CashWidrawReg, CashWidrawTran which is nothing its like register tables and its transactions tables.
    If user deposit money it will save in CashDepositReg and its transaction details will save in CashDepositTran
    If user withdraw his deposited money those info will save in CashWidrawReg and CashWidrawTran.
    now my problem is, if user delete his cash withdraw in CashWidrawReg the withdraw amt should update in CashDepositTran.WidrawAmt against each respective transactions

    Table: CashDepositReg
    CRegID | CashDepDate | UserID | DepAmt
    1 | 09/Mar/2013 | 123 | 10500

    Table: CashDepositTran
    CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
    1 | 1 | 09/Mar/2013 | 123 | 10000 | 10000
    2 | 1 | 09/Mar/2013 | 123 | 500 | 500

    Table: CashWidrawReg
    WRegID | CashWidDate | UserID | WidrawAmt
    15 | 10/Mar/2013 | 123 | 10500

    Table: CashWidrawTran
    WTranID | WRegID | CTransID | CashWidDate | UserID | WidrawAmt
    17 | 15 | 1 | 10/Mar/2013 | 123 | 10000
    18 | 15 | 2 | 10/Mar/2013 | 123 | 500

    I use the following query to get the CashWidrawTran details and now i need to pass this output to the update query to update the CashDepositTran.WidrawAmt as 0 because user cancel the particular cash withdraw details in CashWidrawReg

    Code:
    Dim conn123 As New OleDbConnection("My connection string")
    
    Dim Query As String = "SELECT (B.WidrawAmt) AS test FROM CashWidrawReg A INNER JOIN CashWidrawTran B on A.WRegID = B.WRegID WHERE A.WRegID = 15"
    
    Dim Cmd As New OleDbCommand(Query, conn123)
    
    Dim Dr As OleDbDataReader
    Dim outPutString As String = ""
    
    conn123.Open()
    
    Dr = Cmd.ExecuteReader
    With Dr
    If .HasRows = True Then
    While .Read
    outPutString += .Item(0).ToString & ","
    End While
    End If
    End With
    Dr.Close()
    
    Output
    10000,500,

  2. #2
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: How to Update the access table rows with relative rows from another table

    if user delete his cash withdraw in CashWidrawReg
    If proper accounting rules are applied (and if this is a banking application they should be) transactions must never be deleted. Any errors must be rebalanced by an appropriate credit or debit.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to Update the access table rows with relative rows from another table

    This is not the pure banking application its just a trade concept and i need this to be done in my app

    Quote Originally Posted by dunfiddlin View Post
    If proper accounting rules are applied (and if this is a banking application they should be) transactions must never be deleted. Any errors must be rebalanced by an appropriate credit or debit.

  4. #4
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: How to Update the access table rows with relative rows from another table

    Quote Originally Posted by tnncprojects View Post
    This is not the pure banking application its just a trade concept and i need this to be done in my app
    Not really sure how that makes any difference. The correct way to balance an error is still to offset not to delete. In any case, this ...

    Table: CashDepositTran
    CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
    1 | 1 | 09/Mar/2013 | 123 | 10000 | 10000
    2 | 1 | 09/Mar/2013 | 123 | 500 | 500

    ... makes no sense. I see no reason for there to be a Withdraw amount in this table at all, let alone one which matches the Deposit! And why is the deposit amount split into two amounts and given the same CRegID? The only reason I can imagine for such a device would be to keep an account of different paying methods (cash, cheque etc.) in which case this should be registered as two different amounts in the Cash Deposit table with a further column to indicate the nature of the deposit. If it comes to that, what is the purpose of having two tables with identical information at all? If this is meant to be a double entry system you have completely failed to understand how this is done. Otherwise it's just total redundancy.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to Update the access table rows with relative rows from another table

    Thanks for your reply...

    In CashDepositTran table i am showing the transaction details for a entry it may be 1 or more entries so i am identifying those trans by CRegID so there will be a ref for the entries in both the tables (CashDepositReg and CashDepositTran) and its easy for me to get the details by comparing CRegID.
    WidrawAmt column in CashDepositTran is to know about each transaction status so that I will bind this table in DGV.

    And what i am doing is not the banking process, by some logic/requirement i am doing this app...

  6. #6
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: How to Update the access table rows with relative rows from another table

    And what i am doing is not the banking process, by some logic/requirement i am doing this app...
    Ah right. I was under the foolish impression that you might want to get a good mark for this assignment. Do forgive me.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to Update the access table rows with relative rows from another table

    Its a kind of application with my college guidance and I regret for saying that this is banking or trade application...
    And this also helps me to learn how to over come this scenario...
    I am waiting for the solution from this forums...

    Thank you all...

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to Update the access table rows with relative rows from another table

    Any suggestions or advice please...

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to Update the access table rows with relative rows from another table

    Dear all...
    After trying and find the below update query but i am not able to achieve what i required.

    Code:
    Dim conn1 As New OleDbConnection("My Connection String")
    
    Dim query1 As String = "UPDATE CashDepositTran SET CashDepositTran.WidrawAmt  = CashDepositTran.WidrawAmt  - '" & outPutString & "' WHERE CashDepositTran.CTranID IN (1,2)"
    
    Dim Cmd1 As New OleDbCommand(query1, conn1)
    conn1.Open()
    With Cmd1
    .ExecuteNonQuery()
    End With
    Cmd1.Dispose()
    conn1.Close()
    After excuting the UPDATE query its updating by concatenate the output values (mentioned bold in table)

    Table: CashDepositTran (Before UPDATE Query)
    CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
    1 | 1 | 09/Mar/2013 | 123 | 10000 | 10000
    2 | 1 | 09/Mar/2013 | 123 | 500 | 500

    10000500 and updating in each rows as below

    Table: CashDepositTran (After UPDATE Query)
    CTranID | CRegID | CashDepDate | UserID | DepAmt | WidrawAmt
    1 | 1 | 09/Mar/2013 | 123 | 10000 | -9990500
    2 | 1 | 09/Mar/2013 | 123 | 500 | -10000000
    Last edited by tnncprojects; Apr 28th, 2013 at 09:43 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width