Connect Excel to result of a query using iff or switch

[ruffhi]

I have a query that calculates incremental values from cumulative values (yes - I know I am doing it twice. Assuming I can fix my connection to excel issue - which is better? IIF or SWITCH?)

SELECT t1.*, 

IIF(t1.TriangleOPIndex = 1, 0, t1.TriangleCumValue - Nz(t2.TriangleCumValue, 0) ) AS incremental_value1, 

switch (
t1.TriangleOPIndex = 1, 0,
true,  t1.TriangleCumValue - Nz(t2.TriangleCumValue, 0)
) AS incremental_value2

FROM ModelTriangles AS t1 LEFT JOIN ModelTriangles AS t2 ON t1.ID = t2.ID + 1;

It works with either IIF or SWITCH.



However, my excel can't see this query if I include IIF or SWITCH in the query. Those queries are not in the list of items it presents when I try to form a connection to the access db. If I delete the IIF or SWITCH statement, it sees it.

Is this me and my limited query knowledge? Excel? Access? Solar flares?

[Micron]

I think there was a thread in here recently about such visibility or lack thereof. I think one suggestion was to use Excel side of things, such as Power Query. Another option (just me guessing) is to populate a table with either of those versions and link to that in Excel.

[ruffhi]

Thx for the post. I was thinking along the same lines ... either upload both cum and inc values or upload cum and then run an update query and add the inc.

[CJ_London]

What is excel doing with the data? It may be better to have a module to populate a recordset then loop through the recordset to populate excel?

[madpiet]

Or write it to Excel and use Excel to create the cumulative total...

DAX? Yeah, maybe not, but that's easy too... but then you're kinda stuck with DAX, and you may not want that.