Hi, there was a post on
Posted - 2013-02-14 : 19:40:38, yes.. old. that I’ve just found and its exactly what I want to do, BUT I’m coming across an error that I have no idea about. This was the script that was suggested.
SELECT
g.HoleID,
CASE WHEN g.FromDepth > a.FromDepth THEN g.FromDepth ELSE a.FromDepth END [From],
CASE WHEN g.ToDepth > a.ToDepth THEN a.ToDepth ELSE g.ToDepth END [To],
g.RockType,
a.Grade
FROM
#Geology g
CROSS JOIN #Assay a
WHERE
g.HoleId = a.HoleId
AND g.FromDepth <= a.ToDepth AND g.ToDepth >= a.FromDepth
and this is the error I get.
Can someone please help?
Thanks in advance!!
I'm not a regular Microsoft Access user, but MS Access SQL does not support the CASE
statement commonly used in other database systems. Instead, you can achieve similar conditional logic using the IIf()
function or the Switch()
function, both of which are well-suited for handling multiple conditions within Access queries.
SELECT
g.HoleID,
IIf(g.FromDepth > a.FromDepth, g.FromDepth, a.FromDepth) AS [From],
IIf(g.ToDepth > a.ToDepth, a.ToDepth, g.ToDepth) AS [To],
g.RockType,
a.Grade
FROM#Geology AS g
CROSS JOIN #Assay AS a
WHEREg.HoleID = a.HoleIDAND g.FromDepth <= a.ToDepthAND g.ToDepth >= a.FromDepth;
Disclaimer
Since English is not my first language, I used Copilot to help refine my response and present it in a more professional manner, including this disclaimer 