• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

How do I add 2 columns from another table to this SQL script?

How/Where do I add the following to the SQL code below.

Clients.FName, Clients.Lname where Clients.SIN = S.SIN   (OR H.SIN, not sure the correct one)

I need to add Clients.FName, Clients.LName to the script below the the relation between the two is Clients.SIN = S.SIN or H.SIN.

I also need to order the entire script by Lname, Fname (instead of SIN which it is doing currently)

I cannot figure out the correct spot to place the above code. Any help would be greatly appreciated.
strSearch = " SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	EXP_REF, " & _
" 	REVENUE, " & _
" 	CINT, " & _
" 	CPBACK," & _
" 	CL, " & _
" 	CS, " & _
" 	SS," & _
" 	ADJ, " & _
" 	AR   " & _
" FROM    (SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	SUM(CSCH1) AS EXP_REF, " & _
" 	SUM(CREVREC) + SUM(INT_AMT2) AS REVENUE, " & _
" 	SUM(INT_AMT2) AS CINT, " & _
" 	SUM(PBack2) AS CPBACK, " & _
" 	SUM([CLIENT.REC2]) AS CL, " & _
" 	SUM([CLIENT.SHORT2]) AS CS, " & _
" 	SUM([SPOUSE.SHORT2]) AS SS, " & _
" 	SUM(ADJUSTMENT2) AS ADJ, " & _
" 	SUM(RevenueReceiptCount) AS RevenueReceiptCount, " & _
" 	SUM(ISNULL(CSCH1, 0)) + SUM(ISNULL(PBack2, 0)) - SUM(ISNULL(CREVREC, 0)) - SUM(ISNULL([CLIENT.REC2], 0)) - " & _
" SUM(ISNULL([CLIENT.SHORT2], 0)) - SUM(ISNULL([SPOUSE.SHORT2], 0)) + SUM(ISNULL(ADJUSTMENT2, 0)) AS AR" & _
" FROM          " & _
" ((SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	SUM(CASE WHEN [Adinfo] = 'P' THEN ISNULL(CKAMT, 0) ELSE 0 END) AS PBack2," & _
" 	SUM(ISNULL(C_SCH1_REFUND, 0)) AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    Disbursements AS Disbursements_1" & _
" WHERE   (CKDATE > '" & dateFiscalYearEndMinusOne & "') " & _
" AND (CKDATE <= '" & dateFiscalYearEnd & "') " & _
" AND (OFFICE ='" & aryStringOffice(intEachOffice) & "')" & _
" GROUP BY SIN, YEAR" & _
" UNION ALL" & _
" SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	SUM(ISNULL(AMT_REC, 0)) AS AMT_REC2, " & _
" 	SUM(ISNULL(INT_AMT, 0)) AS INT_AMT2, " & _
" 	SUM(CASE WHEN [Type] = 'RV' THEN ISNULL(AMT_REC, 0) - ISNULL(INT_AMT, 0) ELSE 0 END) AS CREVREC, " & _
" 	SUM(CASE WHEN [Type] = 'CL' THEN ISNULL(AMT_REC, 0) ELSE 0 END)  AS [CLIENT.REC2], " & _
" 	SUM(CASE WHEN [Type] = 'CS' THEN ISNULL(AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT2], " & _
" 	SUM(CASE WHEN [Type] = 'SS' THEN ISNULL(AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    Receipts AS Receipts_1 " & _
" GROUP BY SIN, TYPE, YEAR" & _
" UNION ALL" & _
" SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" CASE WHEN [Type] = 'RV' THEN COUNT(*) ELSE 0 END AS RevenueReceiptCount" & _
" FROM         Receipts AS Receipts_2" & _
" WHERE     (TYPE = 'RV') " & _
" GROUP BY SIN, YEAR, TYPE, DATE_REC" & _
" UNION ALL" & _
" SELECT     " & _
" 	strSIN, " & _
" 	strYear, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	SUM(decAMT) AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    tblAdjustments AS tblAdjustments_1 " & _
" WHERE (Date > '" & dateFiscalYearEndMinusOne & "') AND (Date <= '" & dateFiscalYearEnd & "')" & _
" GROUP BY strSIN, strYear)) AS HI" & _
" GROUP BY SIN, YEAR) AS S " & _
" WHERE     (AR > 0) AND (RevenueReceiptCount > 0)" & _
" ORDER BY SIN"

Open in new window

0
JohnnyBCJ
Asked:
JohnnyBCJ
  • 5
  • 4
1 Solution
 
pcelbaCommented:

strSearch = " SELECT     " & _
" 	Clients.FName, Clients.Lname " & _
" 	s.SIN, " & _
" 	s.YEAR, " & _
" 	s.EXP_REF, " & _
" 	s.REVENUE, " & _
" 	s.CINT, " & _
" 	s.CPBACK," & _
" 	s.CL, " & _
" 	s.CS, " & _
" 	s.SS," & _
" 	s.ADJ, " & _
" 	s.AR   " & _
" FROM    (SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	SUM(CSCH1) AS EXP_REF, " & _
" 	SUM(CREVREC) + SUM(INT_AMT2) AS REVENUE, " & _
" 	SUM(INT_AMT2) AS CINT, " & _
" 	SUM(PBack2) AS CPBACK, " & _
" 	SUM([CLIENT.REC2]) AS CL, " & _
" 	SUM([CLIENT.SHORT2]) AS CS, " & _
" 	SUM([SPOUSE.SHORT2]) AS SS, " & _
" 	SUM(ADJUSTMENT2) AS ADJ, " & _
" 	SUM(RevenueReceiptCount) AS RevenueReceiptCount, " & _
" 	SUM(ISNULL(CSCH1, 0)) + SUM(ISNULL(PBack2, 0)) - SUM(ISNULL(CREVREC, 0)) - SUM(ISNULL([CLIENT.REC2], 0)) - " & _
" SUM(ISNULL([CLIENT.SHORT2], 0)) - SUM(ISNULL([SPOUSE.SHORT2], 0)) + SUM(ISNULL(ADJUSTMENT2, 0)) AS AR" & _
" FROM          " & _
" ((SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	SUM(CASE WHEN [Adinfo] = 'P' THEN ISNULL(CKAMT, 0) ELSE 0 END) AS PBack2," & _
" 	SUM(ISNULL(C_SCH1_REFUND, 0)) AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    Disbursements AS Disbursements_1" & _
" WHERE   (CKDATE > '" & dateFiscalYearEndMinusOne & "') " & _
" AND (CKDATE <= '" & dateFiscalYearEnd & "') " & _
" AND (OFFICE ='" & aryStringOffice(intEachOffice) & "')" & _
" GROUP BY SIN, YEAR" & _
" UNION ALL" & _
" SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	SUM(ISNULL(AMT_REC, 0)) AS AMT_REC2, " & _
" 	SUM(ISNULL(INT_AMT, 0)) AS INT_AMT2, " & _
" 	SUM(CASE WHEN [Type] = 'RV' THEN ISNULL(AMT_REC, 0) - ISNULL(INT_AMT, 0) ELSE 0 END) AS CREVREC, " & _
" 	SUM(CASE WHEN [Type] = 'CL' THEN ISNULL(AMT_REC, 0) ELSE 0 END)  AS [CLIENT.REC2], " & _
" 	SUM(CASE WHEN [Type] = 'CS' THEN ISNULL(AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT2], " & _
" 	SUM(CASE WHEN [Type] = 'SS' THEN ISNULL(AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    Receipts AS Receipts_1 " & _
" GROUP BY SIN, TYPE, YEAR" & _
" UNION ALL" & _
" SELECT     " & _
" 	SIN, " & _
" 	YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" CASE WHEN [Type] = 'RV' THEN COUNT(*) ELSE 0 END AS RevenueReceiptCount" & _
" FROM         Receipts AS Receipts_2" & _
" WHERE     (TYPE = 'RV') " & _
" GROUP BY SIN, YEAR, TYPE, DATE_REC" & _
" UNION ALL" & _
" SELECT     " & _
" 	strSIN, " & _
" 	strYear, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	SUM(decAMT) AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    tblAdjustments AS tblAdjustments_1 " & _
" WHERE (Date > '" & dateFiscalYearEndMinusOne & "') AND (Date <= '" & dateFiscalYearEnd & "')" & _
" GROUP BY strSIN, strYear)) AS HI" & _
" GROUP BY SIN, YEAR) AS S " & _
" INNER JOIN Clients ON Clients.SIN = S.SIN " & _
" WHERE     (s.AR > 0) AND (s.RevenueReceiptCount > 0)" & _
" ORDER BY Clients.LName, Clients.FName"

Open in new window

0
 
JohnnyBCJAuthor Commented:
After copying and pasting your code I get the following error (which is a lot better than the errors I was getting)... time to find the needle in the haystack as they say.

Incorrect syntax near '.'. Incorrect syntax near the keyword 'AS'.




0
 
pcelbaCommented:
There is missing comma in the second line

"       Clients.FName, Clients.Lname, " & _

Was it working without Clients?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pcelbaCommented:
Yes, the missing comma is the problem, I reproduced this error on SQL2005.
0
 
JohnnyBCJAuthor Commented:
You are correct. I fixed the above issue.

Now I get the famous error

Ambiguous column name 'SIN'.

I know what causes this error (SIN being referenced by a number of tables and SQL not knowing which one to pick) but I have ZERO clue on how to fix it.

This is an error I was getting before.


0
 
JohnnyBCJAuthor Commented:
Thank you very much!!!!
When I attempted to do the same thing I got stuck on a number of errors The ambiguous error hurts my head.  I wish there was an SQL Syntax analyzer like there is for Vb.net  or C, or C# or maybe I should hit the books harder.

Thanks again!!
0
 
pcelbaCommented:
Is the ambiguous column solved? I don't see it. Each SELECT selects just from one table or from one subselect, so it should not report this bug.

I've added some local aliases and you may try it again
strSearch = " SELECT     " & _
" 	Clients.FName, Clients.Lname, " & _
" 	s.SIN, " & _
" 	s.YEAR, " & _
" 	s.EXP_REF, " & _
" 	s.REVENUE, " & _
" 	s.CINT, " & _
" 	s.CPBACK," & _
" 	s.CL, " & _
" 	s.CS, " & _
" 	s.SS," & _
" 	s.ADJ, " & _
" 	s.AR   " & _
" FROM    (SELECT     " & _
" 	HI.SIN, " & _
" 	HI.YEAR, " & _
" 	SUM(HI.CSCH1) AS EXP_REF, " & _
" 	SUM(HI.CREVREC) + SUM(HI.INT_AMT2) AS REVENUE, " & _
" 	SUM(HI.INT_AMT2) AS CINT, " & _
" 	SUM(HI.PBack2) AS CPBACK, " & _
" 	SUM([CLIENT.REC2]) AS CL, " & _
" 	SUM([CLIENT.SHORT2]) AS CS, " & _
" 	SUM([SPOUSE.SHORT2]) AS SS, " & _
" 	SUM(HI.ADJUSTMENT2) AS ADJ, " & _
" 	SUM(HI.RevenueReceiptCount) AS RevenueReceiptCount, " & _
" 	SUM(ISNULL(HI.CSCH1, 0)) + SUM(ISNULL(HI.PBack2, 0)) - SUM(ISNULL(HI.CREVREC, 0)) - SUM(ISNULL([CLIENT.REC2], 0)) - " & _
" SUM(ISNULL([CLIENT.SHORT2], 0)) - SUM(ISNULL([SPOUSE.SHORT2], 0)) + SUM(ISNULL(HI.ADJUSTMENT2, 0)) AS AR" & _
" FROM          " & _
" ((SELECT     " & _
" 	Disbursements_1.SIN, " & _
" 	Disbursements_1.YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	SUM(CASE WHEN [Adinfo] = 'P' THEN ISNULL(CKAMT, 0) ELSE 0 END) AS PBack2," & _
" 	SUM(ISNULL(C_SCH1_REFUND, 0)) AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    Disbursements AS Disbursements_1" & _
" WHERE   (CKDATE > '" & dateFiscalYearEndMinusOne & "') " & _
" AND (CKDATE <= '" & dateFiscalYearEnd & "') " & _
" AND (OFFICE ='" & aryStringOffice(intEachOffice) & "')" & _
" GROUP BY Disbursements_1.SIN, Disbursements_1.YEAR" & _
" UNION ALL" & _
" SELECT     " & _
" 	Receipts_1.SIN, " & _
" 	Receipts_1.YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	SUM(ISNULL(AMT_REC, 0)) AS AMT_REC2, " & _
" 	SUM(ISNULL(INT_AMT, 0)) AS INT_AMT2, " & _
" 	SUM(CASE WHEN [Receipts_1.Type] = 'RV' THEN ISNULL(AMT_REC, 0) - ISNULL(INT_AMT, 0) ELSE 0 END) AS CREVREC, " & _
" 	SUM(CASE WHEN [Receipts_1.Type] = 'CL' THEN ISNULL(AMT_REC, 0) ELSE 0 END)  AS [CLIENT.REC2], " & _
" 	SUM(CASE WHEN [Receipts_1.Type] = 'CS' THEN ISNULL(AMT_REC, 0) ELSE 0 END) AS [CLIENT.SHORT2], " & _
" 	SUM(CASE WHEN [Receipts_1.Type] = 'SS' THEN ISNULL(AMT_REC, 0) ELSE 0 END) AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    Receipts AS Receipts_1 " & _
" GROUP BY Receipts_1.SIN, Receipts_1.TYPE, Receipts_1.YEAR" & _
" UNION ALL" & _
" SELECT     " & _
" 	Receipts_2.SIN, " & _
" 	Receipts_2.YEAR, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	0 AS ADJUSTMENT2, " & _
" CASE WHEN [Receipts_2.Type] = 'RV' THEN COUNT(*) ELSE 0 END AS RevenueReceiptCount" & _
" FROM         Receipts AS Receipts_2" & _
" WHERE     (TYPE = 'RV') " & _
" GROUP BY Receipts_2.SIN, Receipts_2.YEAR, Receipts_2.TYPE, DATE_REC" & _
" UNION ALL" & _
" SELECT     " & _
" 	strSIN, " & _
" 	strYear, " & _
" 	0 AS EXP_REF, " & _
" 	0 AS AMT_REC, " & _
" 	0 AS INT_AMT, " & _
" 	0 AS REVENUE, " & _
" 	0 AS [CLIENT.REC], " & _
" 	0 AS [CLIENT.SHORT], " & _
" 	0 AS [SPOUSE.SHORT], " & _
" 	0 AS ADJUSTMENT, " & _
" 	0 AS PayBack, " & _
" 	0 AS PBack2, " & _
" 	0 AS CSCH1, " & _
" 	0 AS AMT_REC2, " & _
" 	0 AS INT_AMT2, " & _
" 	0 AS CREVREC, " & _
" 	0 AS [CLIENT.REC2], " & _
" 	0 AS [CLIENT.SHORT2], " & _
" 	0 AS [SPOUSE.SHORT2], " & _
" 	SUM(decAMT) AS ADJUSTMENT2, " & _
" 	0 AS RevenueReceiptCount" & _
" FROM    tblAdjustments AS tblAdjustments_1 " & _
" WHERE (Date > '" & dateFiscalYearEndMinusOne & "') AND (Date <= '" & dateFiscalYearEnd & "')" & _
" GROUP BY strSIN, strYear)) AS HI" & _
" GROUP BY HI.SIN, HI.YEAR) AS S " & _
" INNER JOIN Clients ON Clients.SIN = S.SIN " & _
" WHERE     (s.AR > 0) AND (s.RevenueReceiptCount > 0)" & _
" ORDER BY Clients.LName, Clients.FName"

Open in new window

0
 
JohnnyBCJAuthor Commented:
Yes the ambiguous column is solved.  I do not know how I got that error or really how it was solved. Thank you very much for your help!!! It's people like you that make experts exchange such an incredible service! A million thanks!
0
 
pcelbaCommented:
You are welcome Johnny!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now