I went through by hand and aliased your query. I noticed a couple places where the table name was not specified, which isn't a big deal as long as there's no ambiguity. However, I also noticed this bit -
Code:
[dbo_IA_FIRM]![IA_FIRM_NAME] & " (" & [IA_FIRM_STATE_ABBR] & ")" AS Firm,
That's probably legal, but it's not in the same format as the rest, so I fixed it. Here's the resulting query SQL:
Code:
SELECT
T1.ESTIMATE_ID,
T1.CLAIM_NUMBER AS Claim,
TC1.CR_NUMBER AS PQS,
TF.IA_FIRM_NAME,
TF.IA_FIRM_NAME & " (" & TF.IA_FIRM_STATE_ABBR & ")" AS Firm,
T1.CONTRACTOR,
T1.DATE_ASSIGNED_TO_IA,
T1.DATE_RECEIVED_BY_PQS,
T1.DATE_APPROVED_BY_IA,
Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy") AS MonthYear,
T1.IA_ESTIMATE_AMT AS EstmtAmount,
T1.APPROVED_IA_ESTIMATE_AMT AS ApprvdAmount,
T1.SUPPLEMENT,
T1.VAR_SCOPE,
T1.VAR_OTHER,
T1.VAR_PROTOCOL,
T1.VAR_COVERAGE,
T1.VAR_PRICING,
T1.VAR_MATERIAL,
T1.VAR_METHOD,
T1.NOTES
FROM
( (dbo_IA_ESTIMATE AS T1
LEFT JOIN dbo_IA_FIRM AS TF
ON T1.IA_FIRM_ID = TF.IA_FIRM_ID)
LEFT JOIN dbo_CLAIM_CR AS TC0
ON T1.CR_ID = TC0.CR_ID)
LEFT JOIN dbo_CLAIM_CR AS TC1
ON T1.PQS_ID = TC1.CR_ID
WHERE
(((TF.IA_FIRM_NAME)=[Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![FirmName])
AND ((T1.CONTRACTOR)=False)
AND ((Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy"))
Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartMonth]
And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndMonth]));
Once I got finished aliasing, I noticed that TC0, the first copy of dbo_Claim_CR, is not being used for anything in that query. So this one should achieve the same thing slightly more efficiently.
Code:
SELECT
T1.ESTIMATE_ID,
T1.CLAIM_NUMBER AS Claim,
TC1.CR_NUMBER AS PQS,
TF.IA_FIRM_NAME,
TF.IA_FIRM_NAME & " (" & TF.IA_FIRM_STATE_ABBR & ")" AS Firm,
T1.CONTRACTOR,
T1.DATE_ASSIGNED_TO_IA,
T1.DATE_RECEIVED_BY_PQS,
T1.DATE_APPROVED_BY_IA,
Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy") AS MonthYear,
T1.IA_ESTIMATE_AMT AS EstmtAmount,
T1.APPROVED_IA_ESTIMATE_AMT AS ApprvdAmount,
T1.SUPPLEMENT,
T1.VAR_SCOPE,
T1.VAR_OTHER,
T1.VAR_PROTOCOL,
T1.VAR_COVERAGE,
T1.VAR_PRICING,
T1.VAR_MATERIAL,
T1.VAR_METHOD,
T1.NOTES
FROM
(dbo_IA_ESTIMATE AS T1
LEFT JOIN dbo_IA_FIRM AS TF
ON T1.IA_FIRM_ID = TF.IA_FIRM_ID)
LEFT JOIN dbo_CLAIM_CR AS TC1
ON T1.PQS_ID = TC1.CR_ID
WHERE
(((TF.IA_FIRM_NAME)=[Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![FirmName])
AND ((T1.CONTRACTOR)=False)
AND ((Format$(T1.DATE_APPROVED_BY_IA,"mmm yyyy"))
Between [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![StartMonth]
And [Forms]![Main Menu PQU Reporting]![NavigationSubform].[Form]![EndMonth]));