However, when I popped the SQL in the one of my favorite Access add-ins, the truth was revealed immediately. Consider the following:
Starting on the 7th line in this formatted view, we have an inline subquery. Formatted this way, that's fairly clear. When viewed in the undifferentiated mass within the native Access query editor, that just wasn't clear at all.
SELECT [Reporting Codes Master].[Reporting Code]
,[Reporting Codes Master].[Reporting Code Description]
,FINAL.[No of Claims]
,FINAL.[Revised Debited Amountx]
,FINAL.[Original Debited Amountx] AS [Debit Amountx]
FROM [Reporting Codes Master]
LEFT JOIN (
SELECT REV.[Reporting Code]
,REV.[No of Claims]
,REV.[Revised Debited Amountx]
,ORIG.[Original Debited Amountx]
FROM (
SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
,COUNT([Pull List].[VIN]) AS [No of Claims]
,sum([Pull List].[Mediated Amount]) AS [Revised Debited Amountx]
FROM [Pull List]
LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code F] = [Deviation Code Master].[Deviation Code]
WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
AND [Pull List].[Deviation Code F] <> "82"
AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
GROUP BY [Deviation Code Master].[Reporting Code]
HAVING [Deviation Code Master].[Reporting Code] NOT IN (
""
,"P"
)
) AS REV
INNER JOIN (
SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
,sum([Pull List].[Debit Amount]) AS [Original Debited Amountx]
FROM [Pull List]
LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code] = [Deviation Code Master].[Deviation Code]
WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
AND [Pull List].[Deviation Code] <> "82"
AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
GROUP BY [Deviation Code Master].[Reporting Code]
HAVING [Deviation Code Master].[Reporting Code] NOT IN (
""
,"P"
)
) AS ORIG ON REV.[Reporting Code] = ORIG.[Reporting Code]
UNION ALL
SELECT REV.[Reporting Code]
,REV.[No of Claims]
,REV.[Revised Debited Amountx]
,ORIG.[Original Debited Amountx]
FROM (
SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
,COUNT([Pull List].[VIN]) AS [No of Claims]
,sum([Pull List].[Mediated Amount]) AS [Revised Debited Amountx]
FROM [Pull List]
LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code F] = [Deviation Code Master].[Deviation Code]
WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
AND [Pull List].[Deviation Code F] <> "82"
AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
GROUP BY [Deviation Code Master].[Reporting Code]
HAVING [Deviation Code Master].[Reporting Code] NOT IN (
""
,"P"
)
) AS REV
LEFT JOIN (
SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
,sum([Pull List].[Debit Amount]) AS [Original Debited Amountx]
FROM [Pull List]
LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code] = [Deviation Code Master].[Deviation Code]
WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
AND [Pull List].[Deviation Code] <> "82"
AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
GROUP BY [Deviation Code Master].[Reporting Code]
HAVING [Deviation Code Master].[Reporting Code] NOT IN (
""
,"P"
)
) AS ORIG ON REV.[Reporting Code] = ORIG.[Reporting Code]
WHERE ORIG.[Reporting Code] IS NULL
UNION ALL
SELECT ORIG.[Reporting Code]
,0 AS [No of Claims]
,0 AS [Revised Debited Amountx]
,ORIG.[Original Debited Amountx]
FROM (
SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
,sum([Pull List].[Debit Amount]) AS [Original Debited Amountx]
FROM [Pull List]
LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code] = [Deviation Code Master].[Deviation Code]
WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
AND [Pull List].[Deviation Code] <> "82"
AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
GROUP BY [Deviation Code Master].[Reporting Code]
HAVING [Deviation Code Master].[Reporting Code] NOT IN (
""
,"P"
)
) AS ORIG
LEFT JOIN (
SELECT DISTINCTROW [Deviation Code Master].[Reporting Code]
,COUNT([Pull List].[VIN]) AS [No of Claims]
,sum([Pull List].[Mediated Amount]) AS [Revised Debited Amountx]
FROM [Pull List]
LEFT JOIN [Deviation Code Master] ON [Pull List].[Deviation Code F] = [Deviation Code Master].[Deviation Code]
WHERE [Pull List].[Debit Flag] <> "Cleared Debit"
AND [Pull List].[Deviation Code F] <> "82"
AND [Deviation Code Master].LANGUAGE = Forms!AuditResults!cmbLanguage
AND [Deviation Code Master].Country = Forms!AuditResults!cmbCountry
GROUP BY [Deviation Code Master].[Reporting Code]
HAVING [Deviation Code Master].[Reporting Code] NOT IN (
""
,"P"
)
) AS REV ON ORIG.[Reporting Code] = REV.[Reporting Code]
WHERE REV.[Reporting Code] IS NULL
) AS FINAL ON FINAL.[Reporting Code] = [Reporting Codes Master].[Reporting Code]
WHERE [Reporting Codes Master].[Reporting Code] NOT IN (
""
,"P"
)
AND [Reporting Codes Master].LANGUAGE = Forms!AuditResults!cmbLanguage
ORDER BY [Reporting Codes Master].[Reporting Code];
Leaving aside the question of how well this SQL works, whether it can be better optimized, and so on, the ability to apply "pretty formatting" to it, paid off handsomely in helping the poster past a nasty conundrum.
The tool?
It's called "Access SQL Editor", from Field Effect, LLC.
When I bought it, the license was only $5.00 (US) and it's paid for itself over and over and over in situations just like this.
I can't recommend it highly enough.
 | ||
| Access SQL Editor "About" dialog |
