subtrscting 2 different columns from 2 tables

[rzaxl]

I have 2 tables that are joined together on a one to one basis by the
primary/foreign key. Both tables consist of an integer field called wkno as
well as primary key called id. what I'm trying to do is subtract table2.wkno
from table1.wkno, display the result of each row and if possible find the
average of the result, have looked every where on the internet but all I keep
getting is how to subtract date fields, which i already know how to. Please
someone help me

 

[KARL DEWEY]

Try this --
SELECT table1.wkno, table2.wkno, (table1.wkno - table2.wkno) AS wkno_diff,
Null AS wkno_AVG
FROM table1 INNER JOIN table2 ON table1.id = table2.id
UNION SELECT Null, Null, Null, Sum(table1.wkno -
table2.wkno)/Count(table1.id) AS wkno_AVG
FROM table1 INNER JOIN table2 ON table1.id = table2.id
GROUP BY Null, Null, Null;

 

[rzaxl]

Unfortunatly the query did'nt work, what I'm trying to do is display the
results of a subtraction between the different fields from each row

e.g (tbl1.wkno - tbl2.wkno)

tbl1 (id , wkno, yrNo planYr, wonWkno)
tbl2 (fid, wkno,, yrNo, planYr, req_informed)

both table are joined automaticlly via id & fid

 

[KARL DEWEY]

Try it now using your latest post of table names and fields --
SELECT tbl1.wkno, tbl2.wkno, (tbl1.wkno - tbl2.wkno) AS wkno_diff,
Null AS wkno_AVG
FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.fid
UNION SELECT Null, Null, Null, Sum(tbl1.wkno -
tbl2.wkno)/Count(tbl1.id) AS wkno_AVG
FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.fid
GROUP BY Null, Null, Null;

If it does not work then explain results - what did you not gett - what did
you get that was wrong - etc.

Post sample data.