none
can I compare two row in same table in Access and output only one? RRS feed

  • Question

  • I have query that output the next results for two different Processor:

    DateReceived Processor new_refund New_Grand_Total   New_Tot_Without_Refund DateFilled
    01/08/2018 Blair Hedrick $0.00 $1,700.00   1700
    01/08/2018 Blair Hedrick $40.00 $1,740.00   1700 01/08/2018
    DateReceived Processor new_refund New_Grand_Total   New_Tot_Without_Refund DateFilled
    01/08/2018 Carmen Mendoza $0.00 $500.00     500 01/08/2018

    If Processor has more than one row I need select one that had missing DateFilled (smallest New_Grand_Total) and keep this record. If Processor has one row I need to keep it.

    I am not sure how compare row i  Access and output only one of them.


    Wednesday, January 10, 2018 11:06 PM

All replies

  • Hi ElenaVarganova,

    you had mentioned that,"If Processor has more than one row I need select one that had missing DateFilled (smallest New_Grand_Total) and keep this record. If Processor has one row I need to keep it."

    i try to create a Table like yours.

    from your description it looks like you have 2 conditions.

    (1) select one that had missing DateFilled

    (2) smallest New_Grand_Total

    then i create one form and On_Load Event i put code below.

    Option Compare Database
    
    Private Sub Form_Load()
    Dim rs, rs1 As DAO.Recordset
     Dim db As Database
      Dim strSQL, strSQL1 As String
    
      Set db = CurrentDb
    
     strSQL = "SELECT * from product_data"
    
      Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount >= 1 Then
    
    strSQL1 = "SELECT * from product_data where New_Grand_Total=(select min(New_Grand_Total) from product_data ) and DateFilled is null;"
    Set rs1 = db.OpenRecordset(strSQL1)
    Me.Text0.Value = rs1!DateReceived
    Me.Text2.Value = rs1!Processor
    Me.Text4.Value = rs1!new_refund
    Me.Text6.Value = rs1!New_Grand_Total
    Me.Text8.Value = rs1!New_Tot_Without_Refund
    Me.Text10.Value = rs1!DateFilled
    Else
    
    Me.Text0.Value = rs!DateReceived
    Me.Text2.Value = rs!Processor
    Me.Text4.Value = rs!new_refund
    Me.Text6.Value = rs!New_Grand_Total
    Me.Text8.Value = rs!New_Tot_Without_Refund
    Me.Text10.Value = rs!DateFilled
    
    End If
    
       Set rs = Nothing
        Set rs1 = Nothing
    End Sub

    Output:

    above is just an example to give you an idea. you need to modify the query, code and logic based on your requirement. if you are firing the same query then you not need to execute it multiple times and you can display the result from the first query. if you have multiple query to execute then you can set it as per your requirement in code above.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, January 11, 2018 6:09 AM
    Moderator
  • Thanks. I will try it.
    Thursday, January 11, 2018 4:29 PM
  • Hi ElenaVarganova,

    if your issue get solved, then i suggest you to mark the suggestion as an answer.

    it will help us to close this thread and it will help other community members in future who will face same kind of issue like yours.

    if the issue is still persist then you can let us know about it with code that you had tried on your side.

    we will try to provide you further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 12, 2018 6:19 AM
    Moderator