Quote Originally Posted by Izandol View Post
4. I believe you are using the wrong program. if you must use Excel, the source data is better in a separate workbook.
I totally agree! But any IT professional has run into the situation where the technical architecture is dictated by a non-technical manager, or else by budget constraints. Perhaps I would benefit from a course in Negotiation and Influencing .

I'd rather do this in Access, SQL Server, or a "proper" database, but that's not an option.

Ok, so my actual query is more like this:

SELECT 
a.CaseId, 
a.Analyst, 
a.CovNo, 
a.Suffix, 
a.LastName, 
a.FirstName, 
a.Total, 
b.Applicable, 
d.Agreed,
(b.Applicable - d.Agreed) as WriteOff,
d.WriteOffReason, 
c.Paid,
coalesce(d.Agreed, b.Applicable)-d.Paid As Outstanding,
d.SettlementDate
FROM
(
(
(SELECT CaseId, Analyst, CovNo, Suffix, LastName, FirstName, SUM(BenefitPaid) AS Total FROM [Claims$] GROUP BY CaseId, Analyst, CovNo, Suffix, LastName, FirstName) AS a
LEFT JOIN
(SELECT CaseId, SUM(BenefitPaid) AS Applicable FROM [Claims$] WHERE Applicable="Y" GROUP BY CaseId) AS b
ON 
a.CaseId=b.CaseId
) 
LEFT JOIN
(SELECT CaseId, SUM(total) AS Paid FROM [Payments$] GROUP BY CaseId) AS c
ON
a.CaseId=c.CaseId
)
LEFT JOIN
(SELECT * FROM [Money$]) AS d
ON 
a.CaseId=d.CaseId
where Agreed could be Empty. If specified, it will always be less than Applicable. Outstanding = MIN(Agreed,Applicable)-Paid.

Excel is choking on the more advanced SQL. Google says Excel and Access don't support CASE WHEN (isn't CASE WHEN ANSI-standard SQL?), and to use SWITCH or IIF instead. I've tried both, neither work.

The SQL works if I remove WriteOff and Outstanding, or just SELECT * (i.e. the joins work ok).

Quote Originally Posted by Izandol View Post
3. B. the Listobject may have formulas added to it - they will automatically fill as the query table adjusts.
Perhaps I can get this working by adding formulas for the WriteOff and Outstanding calculations.

Googling now...