Select (claculated field) Where (calculated field) >= t1.field2
Hi,
I am having difficulty with an SQL query in Access.
I have 3 tables 2 with quantity value's and one with minimum qty value.
I want to calculate the sum of records from t1 and t2 grouped by their "item nr"
SELECT t1.val1-t2.val1 AS val3
Then I want to only display the val3 records which is smaller or equal to the value in table 3.
WHERE val3(calculated field) <= t3.val1
I did an SQL like below
SELECT SUM(t1.val1)-SUM(t2.val1) AS [on hand]FROM t1,t2 WHERE [on hand] <= t3.[minimum qty] GROUP BY t1.[item nr],t2.[item nr]
But I keep getting errors I tried using a subq but it displays that a max of 1 record can be displayed by the subq
Please help not at all profficient in SQL.
This is for an MS Access DB though.
Thanks