访问加入转换/透视查询

Question

首先，感谢任何可以提供帮助的人。多年来，我做了很多加入查询，但对于某些人来说，无法让这个查询工作。这是我第一次同时使用转换/透视命令。

查询 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;

查询 2：

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

查询 1 和查询都可以正常工作。 我想可以在第 1 年、第 1 个月、第 1 天、第 1 天、小时 1、第 1 分钟上左联接这两个查询 因此，我创建了第三个查询：

查询 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;

但是，这会生成一个错误，指出"FROM 子句中的语法错误">

我想上传单个查询结果的 excel 文件，但似乎无法这样做。

谁能帮忙？非常感谢您的帮助。

Answer 1

Access 不允许在子查询中使用Transform+Pivot。要解决该限制，您需要将交叉表查询存储在单独的查询中，例如：

查询3：

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

查询4：

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;

Answer 2

和往常一样，我在发布问题后立即有了主意！转换/透视操作必须在联接之外进行。此代码片段现在可以工作：

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];