Access Joining a TRANSFORM / PIVOT Query

Question

First of All, thank you to anyone that can help. I have done many joining queries over the years but for some cannot get this one to work. It is the first time I have used the Transform / Pivot commands at the same though.

Query 1:

TRANSFORM Max(price_btc)
SELECT year1, month1, day1, hour1, minute1
FROM HISTORY
WHERE [SYMBOL] = "XRP" OR [SYMBOL] = "EOS" OR [SYMBOL] = "NEO"
GROUP BY year1, month1, day1, hour1, minute1
PIVOT symbol;

Query 2:

SELECT year1, month1, day1, hour1, minute1, price_usd
FROM HISTORY
WHERE [symbol] = "BTC"
ORDER BY year1, month1, day1, hour1, minute1;

Both Query1 and Query work without problem. I would imagine that it would be possible to left join these two queries on year1, month1, day1, hour1, minute1 Therefore I have created a third query:

Query 3:

SELECT *
FROM
(
SELECT year1, month1, day1, hour1, minute1, price_usd
FROM HISTORY
WHERE [symbol] = "BTC"
ORDER BY year1, month1, day1, hour1, minute1
) AS A
LEFT JOIN
(
TRANSFORM MAX(price_btc)
SELECT year1, month1, day1, hour1, minute1
FROM HISTORY
WHERE [SYMBOL] = "XRP" OR [SYMBOL] = "EOS" OR [SYMBOL] = "NEO"
GROUP BY year1, month1, day1, hour1, minute1
PIVOT symbol
) AS B
ON A.year1 = B.year1 AND A.month1 = B.month1 AND A.day1 = B.day1 AND A.hour1 = B.hour1 AND A.minute1 = B.minute1;

However this generates an error stating "Syntax error in FROM clause"

I would like to upload an excel file of the individual query results but can't seem to do so.

Can anyone help? Your help is greatly appreciated.

Answer 1

As is always the way, I had an idea immediately after posting the question! The Transform / Pivot action must happen outside of the joining. This code snippet now works:

TRANSFORM First(price_btc)
SELECT A.year1, A.month1, A.day1, A.hour1, A.minute1, A.price_usd
FROM 
(
SELECT year1, month1, day1, hour1, minute1, symbol, price_btc
FROM HISTORY
WHERE [SYMBOL] = "XRP" OR [SYMBOL] = "EOS" OR [SYMBOL] = "NEO"
)  
AS B LEFT JOIN 
(
SELECT year1, month1, day1, hour1, minute1, price_usd
FROM HISTORY
WHERE [symbol] = "BTC"
ORDER BY year1, month1, day1, hour1, minute1
)  AS A 
ON (A.minute1 = B.minute1) AND (A.hour1 = B.hour1) AND (A.day1 = B.day1) AND 
(A.month1 = B.month1) AND (A.year1 = B.year1)
GROUP BY A.year1, A.month1, A.day1, A.hour1, A.minute1, A.price_usd
PIVOT [SYMBOL];

Answer 2

Access doesn't allow Transform + Pivot in subqueries. To work around that limitation, you need to store your crosstab query in a separate query, e.g.:

Query3:

TRANSFORM MAX(price_btc)
SELECT year1, month1, day1, hour1, minute1
FROM HISTORY
WHERE [SYMBOL] = "XRP" OR [SYMBOL] = "EOS" OR [SYMBOL] = "NEO"
GROUP BY year1, month1, day1, hour1, minute1
PIVOT symbol

Query4:

SELECT *
FROM
(
SELECT year1, month1, day1, hour1, minute1, price_usd
FROM HISTORY
WHERE [symbol] = "BTC"
ORDER BY year1, month1, day1, hour1, minute1
) AS A
LEFT JOIN
Query3 AS B
ON A.year1 = B.year1 AND A.month1 = B.month1 AND A.day1 = B.day1 AND A.hour1 = B.hour1 AND A.minute1 = B.minute1;