我可以比较Access中同一个表中的两行，只输出一行吗？ [英] can I compare two row in same table in Access and output only one?

问题描述

我有查询输出两个不同处理器的下一个结果：

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

如果处理器有多行，我需要选择一个缺少DateFilled（最小New_Grand_Total）的行并保留此记录。如果处理器有一行，我需要保留它。

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 只访问和输出其中一个。

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

推荐答案

您好
ElenaVarganova，

您曾提到过，"如果处理器有多行，我需要选择一个缺少DateFilled（最小New_Grand_Total）的行并保留此记录。如果处理器有一行我需要保留它。"

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）选择一个缺少DateFilled

(1) select one that had missing DateFilled

（2）最小New_Grand_Total

(2) smallest New_Grand_Total

然后我创建一个表单和On_Load事件我把代码放在下面。

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

输出：

以上只是一个给你一个想法的例子。您需要根据您的要求修改查询，代码和逻辑。如果你正在触发相同的查询，那么你不需要多次执行它，你可以显示第一个
查询的结果。如果您要执行多个查询，则可以根据上述代码中的要求进行设置。

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.

问候

Deepak

这篇关于我可以比较Access中同一个表中的两行，只输出一行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！