CODEWITHSUNDEEP
ms-access
J.Red
J.Red

Reputation: 3

MS Access Query Stalling

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%.

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>

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.

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))
   ;

Upvotes: 0

Views: 90

Answers (1)

June7
June7

Reputation: 21379

Consider:

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;

Or:

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);

Or:

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;

Or

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;

Upvotes: 1

Related Questions