ms-access - MS-Access - 从两个表中查询用户

Question

我有一个名为All users的表，其中包含所有用户以及他们对系统的访问权限的总和。还有另外两张表，我从中制作了一张包含所有用户的表，它们基本上分散在两张表上。

我的All users表的问题是它没有与User对应的Name列。

tbl_sbc

Users Name
Foo  John

用户 JDF

Users Name
Bar  Andrew

所有用户

Users
Foo
Bar

我希望查询交叉匹配所有用户表上的用户并检索他们的姓名，该姓名可以在另外两个表之一中。

我试着做

SELECT [Users], [Name] ,Sum([Access]) AS Total
FROM (Select [Users], [Name] , [Access] from tbl_sbc
Union 
Select [Users], [Name] , [Access] from [Users JDF])
GROUP BY [Users]
ORDER BY [users] DESC;

但我得到Your query does not include the specified expression [Name] as part of an aggregate function 但没有 [Name] 它工作正常

标签： ms-access

Answer 1

拿着这个

 SELECT [Users], [Name] ,Sum([Access]) AS Total
 FROM (Select [Users], [Name] , [Access] from tbl_sbc
 Union 
 Select [Users], [Name] , [Access] from [Users JDF])
 GROUP BY [Users]
 ORDER BY [users] DESC;

并变成这个

 SELECT [Users], [Name] ,Sum([Access]) AS Total
 FROM (Select [Users], [Name] , [Access] from tbl_sbc
 GROUP BY [Users], [Name]
 Union 
 Select [Users], [Name] , [Access] from [Users JDF])
 GROUP BY [Users]
 ORDER BY [users] DESC;