Calculating Gross from multiple Tables

[Guest]

I really need someone’s help. I have two tables, one has employee’s
allowances and the other has their basic pay. I need to calculate their
gross. The tables look like this:

Allowances Table
Emp# Surname Firstname AllowanceName Amt
1 Doe John Cola $60.00
1 Doe John Traveling $1,300.00
1 Doe John Acting All. $500.00

Basic Pay Table
Emp# Surname Firstname BasicPay
1 Doe John $5,000.00
2 Henry Brent $3,000.00

Nothing I try seems to work

 

[Guest]

You need a union query to first join the two tables together. Plug in the
proper table and column names.

SELECT [Emp#], First(Surname) as Surnames, First(FirstName) as FirstNames,
Sum(Amt) as Amounts
FROM (SELECT Allowances.[Emp#], Allowances.Surname,
Allowances.FirstName, Allowances.Amt
FROM Allowances
UNION ALL
SELECT [Basic Pay].[Emp#], [Basic Pay].Surname,
[Basic Pay].Firstname, [Basic Pay].BasicPay
FROM [Basic Pay]) As AllOfIt
GROUP BY [Emp#]
ORDER BY [Emp#] ;