Calculated field

[Mac1512]

Hi, Very new to access but thought I was trying to create a simple query!

I have 2 tables

Tble1 ID Store TYpe Vale 1 1 System1 50 2 1 System1 100 3 1 System1 150 4 2 System1 50 5 3 System1 50 6 4 System1 100 <tbody> </tbody>

<tbody>
</tbody>

Tble2
ID	Store	Type	Val
1	1	System2	45
2	1	System2	35
3	2	System2	50
4	3	System2	60

<tbody>
</tbody>

I am trying to show the difference between the Total of system1 less Total System 2, grouped by store.

Query should be like the below

Tble1			Tble2
Store	TYpe	Vale	Store	Type	Val	CalculatedField
1	System1	300	1	System2	80	-220
2	System1	50	2	System2	50	0
3	System1	50	3	System2	60	10
4	System1	100				-100

<tbody>
</tbody>

When I try to write a query I get duplicated entries see below query
SELECT Tble1.Store, Tble1.Vale, Tble2.Store, Tble2.Val, [Tble1]![Vale]-[Tble2]![Val] AS Expr1
FROM Tble1 INNER JOIN Tble2 ON Tble1.Store = Tble2.Store;

Thanks in advance for any help you can give me

 

[Comfy]

I'm learning to, so be gentle. This is probably really bad!!!!


SELECT T1.Store, SUM(T1.System1Val) As S1Val, SUM(T1.System2Val) As S2Val, S2Val - S1Val As Variance
FROM (SELECT Tble1.Store, Tble1.Val As System1Val, 0 As System2Val
FROM Tble1
UNION ALL
SELECT Tble2.Store, 0 As System1Val, Tble2.Val As System2Val
FROM Tble2) As T1
GROUP By T1.Store

 

[Mac1512]

That works - Excellent many thanks