Access SQL语法

Question

有人能帮我处理以下查询的语法吗？

我一直收到以下错误消息: Select语句包含拼写错误或丢失的保留字或参数名，或者标点符号不正确。

我看了一遍又一遍，还是找不到。我需要一双全新的眼睛：

SELECT [Locations].[Property],
  [Cores].Location, 
  [Cores].Core, 
  [Keys].Core,
  Count ([Keys].[Key Seq]) AS [CountOfKey Seq]
FROM [Cores], [Locations], [Keys]
WHERE (
   [Cores].Location = [Locations].Location
   and [Cores].Core = [Keys].Core)
   and (((Keys.[Key Seq])<>0))
GROUP BY Locations.[Property], [Cores].Location, [Cores].Core,[Keys].Core
HAVING (((Keys.Core)<>"No Lock"))
UNION SELECT  [Locations].[Property], 
   [Cores].Location, 
   [core_crosswalk].related_core as Core, 
   [Keys].[Key Seq], Count ([Keys].[Key Seq]) AS [CountOfKeySeq],
FROM   [Cores], [Locations], [Keys], [core_crosswalk]
WHERE (
   [Cores].Location = [Locations].Location,
   and [Cores].Core = [core_crosswalk].core,
   and [core_crosswalk].related_core = [Keys].Core),
   and (((Keys.[Key Seq])<>0))
GROUP BY Locations.[Property], Cores.Core
HAVING (((Keys.Core)<>"No Lock"))
ORDER BY 1, 2, 4;

Answer 1

尝试以下几点：

SELECT [Locations].[Property],
  [Cores].Location, 
  [Cores].Core as Core, 
  Count ([Keys].[Key Seq]) AS [CountOfKey Seq]
FROM ([Cores] INNER JOIN [Locations] ON [Cores].Location = [Locations].Location)
INNER JOIN [Keys] ON [Cores].Core = [Keys].Core
WHERE 
   (Keys.[Key Seq]<>0 AND Cores.Core<>"No Lock")
GROUP BY Locations.[Property], [Cores].Location, [Cores].Core
UNION 
SELECT  [Locations].[Property], 
   [Cores].Location, 
   [cores].Core, 
   Count ([Keys].[Key Seq]) AS [CountOfKeySeq]
FROM  ( ([Cores] INNER JOIN [Locations] ON  [Cores].Location = [Locations].Location)
INNER JOIN [core_crosswalk] ON [Cores].Core = [core_crosswalk].core)
INNER JOIN [Keys] ON [core_crosswalk].related_core = [Keys].Core 
WHERE (Keys.[Key Seq]<>0 AND Keys.Core<>"No Lock")
GROUP BY Locations.[Property], Cores.Core,[Cores].Location
ORDER BY 1, 2, 4;

正如你所看到的，我已经改变了很多，所以你需要检查它是否给了你你想要的。最大的改变是使用联接语法，而不是在哪里。这使得SQL更加可读性更强，也是我更改某些字段的原因--如果您在字段上加入，那么使用哪个表的值并不重要，因为它们是相同的！

为了整洁起见，我把地方和食物结合在一起，但你可以把它们分开。

同样在一个联盟中，两半的类型和数量必须匹配。我不确定键Seq的类型，听起来就像一个整数索引。如果是，则不能在SELECT中(这意味着它必须在组BY中)，如果您想要键Seq的计数，否则计数将始终是1。

现在，如果将这两部分拆分成单独的查询，您将能够在Access queries中查看它们--如果需要重新排列，您可能会发现这很有用。