Merging Tables with different intervals

1

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.

image
image946×146 7.15 KB

Can someone please help?

Thanks in advance!!

2

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 :slight_smile:

1 Like
3

Thanks for this. it gave me a result but it had missing samples. With what you had given me, I then asked ChatGPT, and I finally got a result. So thanks so much.

Admin, should I post the queries that worked, since other geologist may be asking the same question for Access?