MS Access 查询停滞 [英] MS Access Query Stalling

问题描述

对在 access 中编写查询还很陌生，希望我能在逻辑上得到一些帮助，以确定为什么它在 99% 时停滞不前.

Fairly new to writing queries in access and hoping that I can get some help with the logic to determine why its stalling at 99%.

概述:我正在运行的查询基于一个表 (Test1)，它有两列，Accounts &付款，大约有 75k+ 行.帐户编号可以重复多次(重复)，或显示一次(不同)——以及付款.[下表示例]

Overview: The query I am running is based on a table (Test1) which has two columns, Accounts & Payments, and has about 75k+ rows. Account #'s can be repeated more than once (Duplicates), or displayed one-time (Distinct) -- as well as the Payments. [Table example below]

table.redTable {
  border: 2px solid #000000;
  background-color: #FFFFFF;
  width: 100%;
  text-align: center;
  border-collapse: collapse;
}
table.redTable td, table.redTable th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
table.redTable tbody td {
  font-size: 13px;
}
table.redTable th:thead {
  background: #FFFFFF;
  }
table.redTable thead th {
  font-size: 19px;
  font-weight: bold;
  color: #FFFFFF;
  background: #A4A3A3;
  text-align: center;
  border-left: 2px solid #FFFFFF;
  border: 3px solid #000000;
}


table.redTable tfoot {
  font-size: 13px;
  font-weight: bold;
  color: #FFFFFF;
  background: #A40808;
}
table.redTable tfoot td {
  font-size: 1px;
  box-sizing: 0.1px;
  background: #FFFFFF;
  border: 1px solid #A53426;
}
table.redTable tr:nth-child(1) {
  background: #FFFFFF;
  border: 3px solid #A53426;
}
table.redTable tr:nth-child(2) {
  background: #FFFFFF;
  border: 3px solid #A53426;
}
table.redTable tr:nth-child(3) {
  background: #FFFFFF;
  border: 3px solid #A53426;
}
table.redTable tr:nth-child(4) {
  background: #FFFFFF;
  border: 3px solid #A53426;
}
table.redTable tr:nth-child(5) {
  background: #3C983F;
  border: 1px solid #000000;
}
table.redTable tr:nth-child(6) {
  background: #3C983F;
 }
table.redTable tr:nth-child(7) {
  background: #FFFFFF;
  border: 3px solid #A53426;
}

<table class="redTable">
<thead>
<tr>
<th>Accounts</th>
<th>Payments</th>
</tr>
</thead>
<tbody>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
</tbody>
<tbody>
<tr>
<td>42229</td>
<td>$40.00</td>
</tr>
<tr>
<td>42229</td>
<td>$40.00</td>
</tr>
<tr>
<td>55908</td>
<td>$25.00</td>
</tr>
<tr>
<td>55908</td>
<td>$25.00</td>
</tr>
<tr>
<td>55908</td>
<td>$25.00</td>
</tr>
<tr>
<td>55908</td>
<td>$5.00</td>
</tr>
<tr>
<td>103848</td>
<td>$35.00</td>
</tr>
</tbody>
</table>

查询目标:查询的结果是仅显示付款值不同的重复帐号.例如.帐户 12345 列出了 3 次，付款值为 20 美元、10 美元和 10 美元，查询应列出帐户 12345 两次，其中列出的付款为 20 美元和 10 美元.

Query Goal: The outcome of the query is to display only Duplicate Account Numbers where the Payment value is different. Ex. Account 12345 is listed 3 times with Payment value of $20, $10, and $10 the query should list Account 12345 twice with a listed Payment of $20 and $10.

问题:当尝试运行查询时，它会达到 99% 并且停止/永远不会完成.以下是我写的逻辑副本 - 任何帮助/提示将不胜感激.

Issue: When attempting to run the query it gets to 99% and stalls out/never completes. Below is a copy of logic I wrote -- any assistance/tips would be greatly appreciated.

SELECT DISTINCT  j.Accounts, j.Payments
FROM Test1 AS j
  INNER JOIN
  (SELECT Accounts, count(*) AS diffPayments FROM (
 SELECT DISTINCT  
     Accounts, 
     Payments
  FROM
      Test1
  WHERE
      Accounts in (
                 select Accounts from (
                     select Accounts, count(*) as [Count] from Test1 group by Accounts
                ) x where x.Count > 1
             )
         )   t GROUP BY Accounts
  )  AS z ON j.Accounts = z.Accounts
  WHERE (((z.diffPayments)>1))
   ;

推荐答案

考虑:

SELECT Accounts, Payments FROM Test1 WHERE Accounts IN(
    SELECT Accounts FROM (
        SELECT Accounts, Payments FROM Test1 GROUP BY Accounts, Payments)
    GROUP BY Accounts
    HAVING Count(*)>1)
GROUP BY Accounts, Payments;

或者:

SELECT DISTINCT Accounts, Payments FROM Test1 WHERE Accounts IN(
    SELECT Accounts FROM (
        SELECT Accounts, Payments FROM Test1 GROUP BY Accounts, Payments)
    GROUP BY Accounts
    HAVING Count(*)>1);

或者:

SELECT Test1.Accounts, Payments FROM Test1 INNER JOIN (
    SELECT Accounts FROM (
        SELECT Accounts, Payments FROM Test1 GROUP BY Accounts, Payments)
    GROUP BY Accounts
    HAVING Count(*)>1) AS Q1
ON Test1.Accounts=Q1.Accounts
GROUP BY Test1.Accounts, Payments;

或

SELECT DISTINCT Test1.Accounts, Payments FROM Test1 INNER JOIN (
    SELECT Accounts FROM (
        SELECT Accounts, Payments FROM Test1 GROUP BY Accounts, Payments)
    GROUP BY Accounts
    HAVING Count(*)>1) AS Q1
ON Test1.Accounts=Q1.Accounts;

这篇关于MS Access 查询停滞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！