CODEWITHSUNDEEP
ms-access
Matheus Souza
Matheus Souza

Reputation: 127

MS-Access - Query users from two tables

I have a table named All users which holds all users and the sum of their access to a system. And two more tables from which I made the one with all the users, which are basically scattered over the two tables.

The problem with my All users table is that it doesn't have the Name column that corresponds with the User.

tbl_sbc

Users Name
Foo  John

Users JDF

Users Name
Bar  Andrew

All Users

Users
Foo
Bar

and I want the query to crossmatch the users on the all users table and retrieve their name which can be in one of the two other tables.

I tried doing

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;

But I get Your query does not include the specified expression [Name] as part of an aggregate function but without the [Name] it works fine

Upvotes: 0

Views: 34

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

Take this 

 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;

AND Turn into this

 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;

Upvotes: 1

Related Questions