英文:
Syntax error in EXTRACT() query, what operator is missing?
问题
我有一个在MS Access中的表格，用于跟踪我们游泳池会员使用游泳通行证签到的情况，看起来像这样：
我尝试生成一个报告，以显示数据，以便我可以看到每个运营年度我们有多少“签到”次数，如下所示：
季节 访问次数
2021 432
2020 123
2019 354
等等。
我的计划是从CHECKINTIME中提取年份，然后计算该年份中唯一ID的数量。
我正在使用以下SQL进行查询：
选择
EXTRACT(YEAR FROM tblCheckIn.[CHECKINTIME]) AS Season,
COUNT(tblCheckIn.[ID]) AS Visits
FROM tblCheckIn
GROUP BY (YEAR FROM tblCheckIn.[CHECKINTIME]);
现在我得到以下错误：查询表达式中的语法错误（缺少运算符）'EXTRACT(YEAR FROM tblCheckIn.[CHECKINTIME])' 我一直在查看EXTRACT()的文档，但我无法弄清楚我做错了什么。我猜想这可能是特定于MS Access的问题？
英文:
I have a table in MS Access that tracks when members of our pool check in with their pool pass that looks like this:
I'm trying to generate a report that displays the data so I can see how many 'Check-Ins' we've had for each year of operation like so:
Season Visits
2021 432
2020 123
2019 354
etc.
My plan is to extract the year from the CHECKINTIME, then count the number of unique ID's in that year.
I'm using the following SQL for my query:
SELECT
EXTRACT(YEAR FROM tblCheckIn.[CHECKINTIME]) AS Season,
COUNT(tblCheckIn.[ID]) AS Visits
FROM tblCheckIn
GROUP BY (YEAR FROM tblCheckIn.[CHECKINTIME]);
Right now I'm getting the following error: Syntax error (missing operator) in query expression 'EXTRACT(YEAR FROM tblCheckIn.[CHECKINTIME]) I've been pouring over the documentation for EXTRACT() but I can't figure out what I'm doing wrong. I'm guessing it's specifically an MS Access thing?
答案1
得分: 2
Ms Access使用YEAR函数而不是extract：
SELECT
YEAR(tblCheckIn.[CHECKINTIME]) AS Season,
COUNT(tblCheckIn.[ID]) AS Visits
FROM tblCheckIn
GROUP BY YEAR(tblCheckIn.[CHECKINTIME]);
或者您可以使用：
SELECT DatePart("yyyy",tblCheckIn.[CHECKINTIME]) AS NewDate FROM tblCheckIn;
英文:
Ms Access uses a YEAR function and not extract
SELECT
YEAR(tblCheckIn.[CHECKINTIME]) AS Season,
COUNT(tblCheckIn.[ID]) AS Visits
FROM tblCheckIn
GROUP BY YEAR(tblCheckIn.[CHECKINTIME]);
or you can use
SELECT DatePart("yyyy",tblCheckIn.[CHECKINTIME]) AS NewDate FROM tblCheckIn;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论