Of course we need the join(s) that you origional had. My first query most certainly includes that join.
We breaking this down into bite sized parts. So we first learned/used/adopted/read about how to replace the IIF(). SQL server does have a iif() now, but the CASE is oh so much easer to read and maintain anyway.
Next, we then dealt with the dlookup.
dlookup("ColumnName","Table name", SQL WHERE)
So, if we have:
dlookup("HotelName","tblHotels","ID = 5")
Then above becomes
(SELECT HotelName from tblHotels WHERE ID = 5) as some column name
So adopting/using the CASE is a "one to one" conversion process for the iif().
And converting the dlookup() to the sub query select? Again, a one to one conversion process. nothing hints, suggests or implies that we joing to remove or dump or change the sql query in regards to joins etc. They have to remain - my first example (while
air code) did and does include the joins. So yes, you certainly want to keep and need the joins and they should remain and work as before. The converting of the iif() and the converting of the dlookup() are discrete and isolated changes you make. If
we adopt and do this correctly, then we ONLY change the iif() to a CASE.
And if we do this correctly, we ONLY change the dlookup() to a sub - query. Everything else thus should remain as before. And in fact, if we adopt the conversion process, then we don't even had to grasp or understand or even change the rest of the query.
So my first example - (which likely needs some work) including this at the end:
END
AS [Other1-Name],
[Vwcapability Now with RAG_Crosstab].Other1,
Vwcapability Future with RAG_Crosstab].Other1 AS [Other1-Future]
FROM
table 1 INNER JOIN Table 2 ON Table1.ProjID = Table2 .ProjReturnID
So there is no hint, suggestion, implication that we going to change the basic layout and logic and intent of the query. We ONLY going to un-plug the iif() and plug in our CASE. And we ONLY going to un-pluge the dlookup and insert the sub query. But the
rest of the sql will remain as before, and hopefully un-touched without the need to change. So we not really changing the basic query and the joins - only un-plugging the iff() and unplugging the dlookup(). Everything else should remain. In fact, one might
even remove the ii(), remove the dlookup and get the base query working. Once it seems to work, then we introduce the iif() - get that working.
Once that works, then we introduce the dlookup() replacement.
So the old saying?
How do you eat a elephant?
Answer: One bite at a time!
So consider building the base query (with the joins), but the iif() and dlookup() removed. Then introduce the CASE, and if that works, then introduce the sub-query. So we are swapping out a few parts of the basic query - but the joins and the rest of the
query should be rather similar to the original query - including the joins.
And the original dlookup() did not have a join in it a all - so the replacement for the dlookup will be just like the above hotels example, or similar to what I posted. What and how the dlookup() does, and our sub query will BOTH do the exact same thing.
That dookup() to my knowledge was not based on some "join" but had a single query (or table) specified with criteria - and you do the same with the sub query.
So we really don't re-write the basic logic and layout of the original query. As noted, I would consider getting that query working (including the joins) without the iff(), and the dlookup(). Then we introduce these two new parts to that original query with
the joins and everything else it had before.
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
