Results 1 to 36 of 36

Thread: [RESOLVED] Need help with a querry

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Resolved [RESOLVED] Need help with a querry

    Hello Vbforums
    First I have to thank szlamany, Arnoutdv and Zvoni who did their best to help me in DataBase Development forum.
    Unfortunately I was unable to solve my issue.
    Sincerely the querries they proposed were a bit complicated regarding my very basic knowledge.
    So I took another approach.

    Code:
    StrSql = "Select max(Aut), remain, deposit, Total_deposit  from  Trans where  acquit  = 0 And  id = 1 Group by Item_Id"
    Set Rs = Cnn.OpenRecordset(StrSql)
    Dim Payment As Integer
    Dim Balance As Integer
    Payment = 80
    Rs.MoveFirst
    Balance = Payment - Rs("remain")
    Do While Balance > 0
    Balance = Payment - Rs("remain")
    Debug.Print Balance
    If Balance > 0 Then
    Rs!deposit = Rs!deposit + Balance
    Rs.UpdateBatch
    Payment = Balance
    Rs.MoveNext
    End If
    Loop
    Name:  06-04-2021 00-41-31.png
Views: 413
Size:  18.5 KB

    This is the code for creating table.

    Code:
    Cnn.Execute "Create Table IF NOT EXISTS Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)"
    Cnn.Execute " Insert into Trans values " & _
    "(1,1,1,100,10,10,90,0)," & _
    " (2,1,1,100,10,20,80,0)," & _
    " (3,1,1,100,20,40,60,0)," & _
    " (4,1,2,20,4,4,16,0)," & _
    " (5,1,2,20,6,10,10,0)," & _
    " (6,1,3,30,5,5,25,0)," & _
    " (7,1,3,30,10,15,15,0)," & _
    " (8,1,4,20,20,20,0,1)"

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Need help with a querry

    The code and the results do not make much sense to me. The column names also do not seem to fit what you seem to be doing.
    deposit implies that the account balance would grow but it appears this is actually a payment toward a balance due which of course is the opposite of a typical deposit.

    I am also not sure how you are getting the colored values nor why you expect one of them to be different than what they are.
    This line seems odd
    Code:
    Balance = Payment - Rs("remain")
    A balance after a payment is balance - payment, not the other way around.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    The code and the results do not make much sense to me. The column names also do not seem to fit what you seem to be doing.
    deposit implies that the account balance would grow but it appears this is actually a payment toward a balance due which of course is the opposite of a typical deposit.
    English is not my native language
    Deposit = payment

    This line seems odd
    Code:
    Code:
    Balance = Payment - Rs("remain")
    I want to distribute the '80' on the three items.
    Have you got any other simple approach?
    Thank you

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Need help with a querry

    I've looked at your table for the last 10min, and I think you should be using 2 Tables
    the first Table for your items (price)
    the second Table for your Transactions (Deposits; Payments)

    well like Datamiser, for me also it doesn't really make sense, think about a diffrent Table design
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Need help with a querry

    Quote Originally Posted by newbie2 View Post
    I want to distribute the '80' on the three items.
    Have you got any other simple approach?
    Thank you
    What 80 and on what 3 items? You'll need to be more precise as to what you are trying to do.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Need help with a querry

    Quote Originally Posted by DataMiser View Post
    The code and the results do not make much sense to me. The column names also do not seem to fit what you seem to be doing.
    deposit implies that the account balance would grow but it appears this is actually a payment toward a balance due which of course is the opposite of a typical deposit.

    I am also not sure how you are getting the colored values nor why you expect one of them to be different than what they are.
    This line seems odd
    Code:
    Balance = Payment - Rs("remain")
    A balance after a payment is balance - payment, not the other way around.
    DM, here is the original Thread: https://www.vbforums.com/showthread....rhaps-possible!
    If you look at my aircode in Post 16, you'll see there is a line with "Payment=Abs(Balance)"
    I did it that way, to have the correct value for Balance when the code enters the ELSE-Part

    But i agree, that his approach doesn't make much sense.
    Why is he UPDATING, when a deposit occurs?
    Usually, if you make a deposit, you have a Timestamp, and you INSERT a new Record representing this deposit.
    Even in his example he has different records for each deposit, and now he wants to update the last one?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    Now I changed the fields names to become more meaningful
    Attachment 180960
    Name:  08-04-2021 21-50-27.png
Views: 425
Size:  5.9 KB
    In this scenario a customer whose ID is 1 has bought four items.
    Item1 whose price is 100. the customers made 3 payments 10 .... 10 .... 20. the remainder is 60
    Item2: price is 20. the customers made 2 payments 4 .... 6 remainder 10
    Item3: price is 30. the customers made 2 payments 5 .... 10 remainder 15
    So each item has each own payments , total payments and remainders.
    Now lets imagine this scenario :
    The customer made a payment of 80 without specifying the item.
    knowing that the total remainder of the three items is 85.
    Therefore I want my program to first acquit item1.
    item1 New payment (80) - item1 remaide (60) = remainder of new payment (20)
    item2 remainder of new payment (20) - item2 remaider (10) = remainder of new payment (10)
    item3 remaider (15) - remainder of new payment (10) = remainder of item3 (5)
    I wish one of you would understand me.
    Last edited by newbie2; Apr 8th, 2021 at 06:01 PM.

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Need help with a querry

    I understand all up until your last four lines:

    item1 New payment (80) - item1 remaide (60) = remainder of new payment (20)
    item2 remainder of new payment (20) - item2 remaider (10) = remainder of new payment (10)
    item3 remaider (15) - remainder of new payment (10) = remainder of item3 (5)
    I wish one of you would understand me.
    What you are saying is that the guy still owes 90 bucks (but in reality it is 85 Washingtons that he owes (60 + 10 + 15), but he can only come up with 80. So you want to APPLY (not acquit) that 80 smackaroos in some manner. You have chosen to pay by item number (lowest first). In this case, the dude owed 60 clams on Item 1; so you apply 60 to that, leaving 20 to apply to the next item in line, in this case, Item 2.
    Because the balance on Item 2 is 10 bucks, if you apply 10 of the 20 to item 2, you are left with 10 to apply to item 3. Because the cheapskate owes 15 on Item 3, for some reason he can only come up with 10 (80 - 60 -10). So you apply that to Item 3, the client still owes a Lincoln (5 dollar bill).

    Have I got that about right? (YOUR error was adding 60 and 10 and 15 and assuming that was 90)

    So, what IS your problem/question?
    Sam I am (as well as Confused at times).

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    What you are saying is that the guy still owes 90 bucks (but in reality it is 85 Washingtons that he owes (60 + 10 + 15), but he can only come up with 80.
    that's exactly sir
    thank you for correcting my calculations
    and thank you for putting the scenario in native english
    In this case, the dude owed 60 clams on Item 1; so you apply 60 to that, leaving 20 to apply to the next item in line, in this case, Item 2.
    Because the balance on Item 2 is 10 bucks, if you apply 10 of the 20 to item 2, you are left with 10 to apply to item 3. Because the cheapskate owes 15 on Item 3, for some reason he can only come up with 10 (80 - 60 -10). So you apply that to Item 3, the client still owes a Lincoln (5 dollar bill).

    Have I got that about right?
    yes you're right sir
    So, what IS your problem/question?
    I need a code to do that.
    thank you
    Last edited by newbie2; Apr 8th, 2021 at 06:08 PM.

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Need help with a querry

    Typically you would have an invoice or sales order that contains one or more items, whatever was bought at that time. Generally I use two tables for this, One is the header which contains customer info, sales order number, date of sale and so on. The other is the detail which contains a line item for each item that was part of that sale.
    If I were going to track payments I would use a third table which would have the sales order the payment was for, the amount of the payment and the date it was made.
    This way you have all the info you need and it is not being duplicated on multiple rows, simple to get total of payments made, balance due as well as determine late payments and additional fees incurred from said late payments if any.

    Having multiple rows with the info like you have there creates some headaches and causes the db to grow larger than it needs to be.

  11. #11
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Need help with a querry

    Quote Originally Posted by DataMiser View Post
    If I were going to track payments I would use a third table which would have the sales order the payment was for, the amount of the payment and the date it was made..
    Yes, this is standard accounting and very wise decision *not* to track payments per document row item in the sales order but by the whole sales order document but to vindicate OP's db design one might think about each of the item's as being in separate sales order on a single line. So for sales order "item 1" there are 3 payments already, for sales order "item 2" there two and so on.

    What OP wants to implement is a standard payment reconciliation algorithm which can probably be implemented with a single db query if the RDBMS used supports window functions (like ROW_NUMBER/SUM ... OVER ...) to be able to provide running sums on the payments up to and including the current row in this table.

    cheers,
    </wqw>

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Need help with a querry

    well here a query done in access with just the one Table you have, but I think it really hurt's doing what your doing
    I called the Table 'Trans' with your sample Data

    the query in the Access Database
    here the SQL
    Code:
    SELECT T.item_id, T.ID, T.price, (SELECT Sum(TU.Payment)
              FROM   Trans AS TU
              WHERE  TU.price <=T.price
              AND    TU.item_id = T.item_id) AS Kumuliert, [price]-[Kumuliert] AS rest, T.Payment
    FROM Trans AS T
    ORDER BY T.Aut, T.item_id;
    and a pic
    Name:  qryNewbie2.jpg
Views: 354
Size:  30.5 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    ChrisE thanks a lot
    but how can I add my new payment ?
    PLease have a look on post 7.

  14. #14
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Need help with a querry

    I have relooked, and relooked, at this thread, yet still, I am confused. I understand how you want monies to go to pay for items; but this is not the way to handle payments. If I were the customer and I owed money on, say, three separate items I had purchased (on credit, obviously), I would not want some computer to tell ME which Items were paid for as you are doing (paying lowest Item ID first and progressing via Item Id in a sequential manner). As mentioned before, your table design is not a good one.

    So, let me ask you these: WHO is entering these payments? What does the screen input look like? Is the data entry person communicating with the customer when a payment is received? I suggest you start over (completely) and redesign your database as well as your project.

    For example, If I were inputting data (payments, in this example), I would first, want to know how much the payment would be (I'd have to be communicating with the customer), and then I would want to know, from the customer, which items he/she wants to be paid using those funds.

    So, this (below) is not the optimum method for entering bill payment information, but it makes a lot more sense than some data inputter to 'automatically' start paying off items based upon a total number.

    In this very simplified input screen, the user would select the Customer Name. Then would select one of the items toward which the customer would want to pay off. Then, he would type in the amount the customer wants applied toward that item, and then apply the payment (once clicking the Apply Payment button, the database (and grid) would be updated to reflect the total payments so far and open balance on that particular item.

    Name:  Capture.jpg
Views: 392
Size:  10.8 KB
    Sam I am (as well as Confused at times).

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Need help with a querry

    Quote Originally Posted by newbie2 View Post
    ChrisE thanks a lot
    but how can I add my new payment ?
    PLease have a look on post 7.
    I'm confused just like Sam, so I made a sample
    see if it helps

    you have to create the tables, I only made the Update routine to show the calculations

    here the code
    Code:
    Option Explicit
    
    'fields tblTransactions:
    'TR_ID = Autoincrement
    'TR_Amount = Double
    'TR_RestAmount = Double
    'TR_Date = Date
    'TR_Text = Text
    'TR_Payment = Double
    'Categorie = Integer (Foreign Key tblCategorie)
    'TR_RunningSum = Double
    
    'fields tblCategory:
    'CatID = Autoincrement
    'CatText = Text
    
    
    
    Dim adoConnection As ADODB.Connection
    
    
    
    Private Sub Command1_Click()
    Dim rst As ADODB.Recordset
    Dim curLaufendeSumme As Double
    Dim cAmount As Double
    Dim cRest As Double
    Dim strSQL As String
    
    
    strSQL = "SELECT tblTransactions.TR_ID,tblTransactions.TR_Text,"
    strSQL = strSQL & " tblTransactions.TR_Date, tblTransactions.TR_Payment,"
    strSQL = strSQL & " tblTransactions.TR_RunningSum, tblCategory.CatID,"
    strSQL = strSQL & " First(tblTransactions.TR_Amount) AS FirstVal,"
    strSQL = strSQL & " tblTransactions.TR_Amount,tblTransactions.TR_RestAmount"
    strSQL = strSQL & " FROM tblCategory INNER JOIN tblTransactions ON"
    strSQL = strSQL & " tblCategory.CatID = tblTransactions.Categorie"
    strSQL = strSQL & " GROUP BY tblTransactions.TR_Text, tblTransactions.TR_Date,"
    strSQL = strSQL & " tblTransactions.TR_Payment, tblTransactions.TR_RunningSum,"
    strSQL = strSQL & " tblCategory.CatID,tblTransactions.TR_ID, tblTransactions.TR_Amount,"
    strSQL = strSQL & " tblTransactions.TR_RestAmount"
    strSQL = strSQL & " Having (((tblTransactions.TR_Text) = 'arg3'))"
    strSQL = strSQL & " ORDER BY tblTransactions.TR_Text, tblTransactions.TR_Date;"
    
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open strSQL, adoConnection, adOpenKeyset, adLockOptimistic
    
    With rst
        cAmount = rst!FirstVal 'get the First Value (Opening Balance arg1 = 500,00 )
            Do While Not rst.EOF
                cRest = cAmount - rst!TR_Payment
                curLaufendeSumme = curLaufendeSumme + rst!TR_Payment
                rst!TR_RunningSum = curLaufendeSumme
                rst!TR_RestAmount = cRest
                
            rst.Update
             cAmount = cRest
        rst.MoveNext
     Loop
    End With
    rst.Close
    End Sub
    
    
    Private Sub Form_Load()
    
      If (Not openTheDatabase()) Then
            Label1.Caption = "Database Error"
        Exit Sub
    End If
    End Sub
    
    Public Function openTheDatabase() As Boolean
    Dim sConnectionString As String
    On Error GoTo dbError
       Label1.Caption = "Datenbank open"
    Set adoConnection = New ADODB.Connection
    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                     & "Data Source=E:\Adressen.mdb"
    adoConnection.Open sConnectionString
    openTheDatabase = True
    Exit Function
    dbError:
    MsgBox (Err.Description)
    openTheDatabase = False
    End Function
    image before
    Name:  Nbefore.jpg
Views: 332
Size:  15.8 KB

    and image after the calculation
    Name:  nAfter.jpg
Views: 326
Size:  18.9 KB

    the calculation is for 'arg1' as you see in the table,
    also you see I only add the opening Balance once (for arg1 = 500,00)
    Last edited by ChrisE; Apr 10th, 2021 at 01:06 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    ChrisE
    I thank you very much
    I 've just come home and it's very late now.
    Tomorrow I'll test your codes and let you know
    thanks a lot

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    SamOscarBrown
    thank you for the screenshot

    what if the customer (Sam Brown) made a payment of of 100 without specifying the item.
    Is it possible for the program to distribute this sum on the three items and calculate the new balance?

    sony headset 50 + 35 = 85 (balance = 0)
    del keyboard 40 + 20 = 60 (balance = 0)
    hp monitor 110 + 45 = 155 (balance = 35)

  18. #18
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Need help with a querry

    Yes it is possible but that is not really the way sales and payments work. If a person bought those 3 items then there would be 1 balance not 3. the balance would be the total price of the 3 items plus any tax and possibly finance charge or payment fees. The only time a payment goes toward a single item is when you only buy 1 item. A payment goes toward the loan balance which is the total purchase price plus any additional taxes and fees that may be incurred. No item is considered paid for until the full balance has been paid.

  19. #19
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Need help with a querry

    No item is considered paid for until the full balance has been paid.
    Amen! I hope you, newbie2, understand what DM said.

    Now, IF (and it is a big IF), these three items (for example) were on a LAYAWAY PLAN, and that plan allowed a customer to 'pickup' individual items when enough money is received by the company running the layaway plan, then what you are trying to code makes a little bit of sense. But I know of no layaway plan that allows that...you pick up everything in the layaway plan at the same time when EVERYTHING is paid for.

    Also, maybe your company allows people to 'buy' new items when they still owe on something they had already bought. I know of no company that does that. But if one did, then you 'might' try to implement your design. Just imagine you going to Lowes to buy a lawnmower. You pay 500 toward a mower which costs 800. Do you think Lowes would let you put that mower on a trailer and take it away. No. And if you still owed on that mower and went back to them to get an outdoor grill, and plan to put 400 down on the grill, do you think Lowes would do that? NO! They would make you pay for the lawnmower first, then they might let you put the difference (100 (400-300)) on the grill. But that would not be the way you are TRYING to set it up in your program.

    So, think about real life application here....ponder how companies work.

    So, I don't really know what you are trying to set up....is this a homework assignment, or do you plan to actually use this program for real-life application? If the latter, you really need to redesign your project as well as your database.
    Sam I am (as well as Confused at times).

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    Yes it is possible
    Good News.
    but that is not really the way sales and payments work.
    In fact my current program is dealing with each item separately. It looks like the screenshot submitted by Mr SamOscarBrown.



    However one of my users suggestst the scenario mentined above.
    So could you put the (100 payment) in a variable and distrbute it on the three items to produce this outcome?
    ony headset 50 + 35 = 85 (balance = 0)
    del keyboard 40 + 20 = 60 (balance = 0)
    hp monitor 110 + 45 = 155 (balance = 35)
    (100) - 35 - 20 - 45 )

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    ChrisE
    thank you again and again
    I examined your sample and I figured out that it is dealing with each item seperately.
    What I'mlooking for is making a payment that deals with the three items in one shot.
    thank you a lot

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    SamOscarBrown
    I put your message into google translate and if I understand you well, what you think is not applicable as real scenario, I can assure you that it is very possible and very common where I live.
    thank you

  23. #23
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Need help with a querry

    Quote Originally Posted by newbie2 View Post
    ChrisE
    thank you again and again
    I examined your sample and I figured out that it is dealing with each item seperately.
    What I'mlooking for is making a payment that deals with the three items in one shot.
    thank you a lot
    do some research on For Each....
    Code:
      For Each xx In nn
             If someCondition Then
                 GotID = True
                Exit For
             End If
      Next
    EDIT:
    I can only say again, think about your Table design!!!
    what if there are 4 items or 24?
    what about if somebody doesn't pay a payment? do you have a late fee?
    what if somebody returns an item?
    I could go on an on...
    Last edited by ChrisE; Apr 11th, 2021 at 09:17 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    what if there are 4 items or 24?
    the new payment is distributed on the 1st item, 2nd item, 3rd item, 4 th item ..... until the new payment runs out.
    The new payment may expire with the 1st item if the debt related to the first item is equal .to the new payment.
    what about if somebody doesn't pay a payment? do you have a late fee?
    what if somebody returns an item?
    All these scenarios are not possible in the case I'm dealing with.
    thanks

  25. #25
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Need help with a querry

    Quote Originally Posted by newbie2 View Post
    the new payment is distributed on the 1st item, 2nd item, 3rd item, 4 th item ..... until the new payment runs out.
    The new payment may expire with the 1st item if the debt related to the first item is equal .to the new payment.

    All these scenarios are not possible in the case I'm dealing with.
    thanks
    You say it is not possible that someone doesn't pay a payment? Are you sure you are in the same real world as the rest of us. People miss payments all the time, maybe they just forget about it, maybe that do not have the money, maybe they never intended to pay. Definitely not a good idea to assume that everyone will pay like they are supposed and pay on time.

    As for distributing the payment you could do this easily with a loop, just select the rows for that customer that have a balance>0 then loop through, apply payment to item(s) and adjust balance as required.

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    As for distributing the payment you could do this easily with a loop, just select the rows for that customer that have a balance>0 then loop through, apply payment to item(s) and adjust balance as required.
    This is what I have been trying to do for a week but no success.

    Code:
    StrSql = "Select max(Aut), remain, payment, Total_payment  from  Trans where  paid= 0 And  id = 1 Group by Item_Id"
    Set Rs = Cnn.OpenRecordset(StrSql)
    Dim Payment As Integer
    Dim Balance As Integer
    Payment = 80
    Rs.MoveFirst
    Balance = Payment - Rs("remained")
    Do While Balance > 0
    Balance = Payment - Rs("remained")
    Debug.Print Balance
    If Balance > 0 Then
    Rs!deposit = Rs!payment + Balance
    Rs.UpdateBatch
    Payment = Balance
    Rs.MoveNext
    End If
    Loop

  27. #27
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Need help with a querry

    Here is a possible solution for you....a small example:

    Notice the change in table format (the grid resembles the table format).

    After Form Load:
    Name:  Capture.jpg
Views: 276
Size:  13.4 KB

    After clicking button:
    Name:  Capture2.jpg
Views: 273
Size:  12.9 KB

    Project:
    newbieExample.zip
    Sam I am (as well as Confused at times).

  28. #28

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help with a querry

    SamOscarBrown
    All the words of thanks do not fulfill what you really deserve.
    Million thanks
    that's exactly what I was looking for .
    May God Bless you

  29. #29
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: [RESOLVED] Need help with a querry

    it is very rudimentary....you will have to make checks and do other stuff to make it a presentable program, but at least I am glad it gave you the idea on HOW to do what you asked. I, of course, did not take time to do all the other stuff you will want to do (add new items/new customers/remove customers/change prices, etc etc etc.
    Sam I am (as well as Confused at times).

  30. #30

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: [RESOLVED] Need help with a querry

    SamOscarBrown
    Thank you again
    Unfortunately I'm having big trouble to convert your codes to SQlite
    I failed to update the appropriate rows.
    I think it has to do with the cursor location.
    Code:
    rs.Open "update trans2 set remaining_balance = 0 , total_payments = " & CStr(originalPrice) & ", paidfor = 'Y' where item_id = " & CStr(itemNumber), cnn, adOpenKeyset, adLockPessimistic
    I really don't know how to solve the cursor location issue.

  31. #31
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: [RESOLVED] Need help with a querry

    If your data looks anything like what you have in your first post then that query is not going to work no matter what db you are using. It would in effect update all rows that have an item id of 1. So in your sample the three first rows and if you had say 100 customers you bought item 1 then it would update all of those as well making your data invalid and basically worthless.

    From the way you show the data in your first post it would appear that you would want to add a new row rather than update and existing row but if you do want to update an existing row then you need a more specific where clause to make it target only the rows you want it to target.

  32. #32
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: [RESOLVED] Need help with a querry

    @DM...I know you are not directing that comment to me, but just to clarify for newbie...my update (using an .accdb database) was a very simple example. OF COURSE, if someone else bought the same item, my query would update everyone's purchase of that item...one (newbie) would have to add customer_ID in the the update query. I just did my example an a shoestring.....

    As far as using SQlite versus Access, newbie, do you know how to update your tables? If so, use the syntax in place of my Access update query. But again, as DM pointed out, make sure you further clarify your update by all applicable fields, including customer_id.
    Sam I am (as well as Confused at times).

  33. #33

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: [RESOLVED] Need help with a querry

    SamOscarBrown
    You are a great gentelmen
    I was making a slight mistake in my update codes that's why it did not work properly.
    Million thanks sir.
    solved

  34. #34

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: [RESOLVED] Need help with a querry

    If your data looks anything like what you have in your first post then that query is not going to work no matter what db you are using. It would in effect update all rows that have an item id of 1. So in your sample the three first rows and if you had say 100 customers you bought item 1 then it would update all of those as well making your data invalid and basically worthless.
    of course I'm quite aware of all that.
    Thank you

  35. #35
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: [RESOLVED] Need help with a querry

    Quote Originally Posted by newbie2 View Post
    of course I'm quite aware of all that.
    Thank you
    That's good. I could not tell from your posts. I see that Sams example has one record per item as such the update would work. In the samples you posted there were more than one record per item and the update query would not work in that case and you said it was not working but gave no real details so I assumed you still had the multiple rows and all of them were updating.

    What happens when the same person buys more than 1 of the same item? There is no qty field.

  36. #36

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: [RESOLVED] Need help with a querry

    What happens when the same person buys more than 1 of the same item? There is no qty field.
    I have modified Sam's updating codes.
    My updating is based on the auto increment field not on the item_id.

    Everything is allright now

    thank you all of you

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