0

It is straight forward to create a calculated field in a table that uses data IN the table... due to the fact that the expression builder is straight forward to use. However, it appears to me that the expression builder for the calculated field only works with data IN the table;

i.e: expression builder in table MYTABLE works with fields FIELD1.MYTABLE, FIELD2.MYTABLE etc.

Inventory Problem

My problem is that I have two 'count' fields that result from my queries that apply to INPUTQUERY and OUTPUTQUERY (gives me a count of all input data added and a count of all output data added) and now I want to subtract the two to get a stock.

I can't link the table that was created from my query because it wont be able to continually update do the relationship itself, and thus i'm stuck either using the expression builder/SQL.

First question:

Is it possible to have the expression builder reference data from other tables?

i.e expressionbuilder for:

MAINTABLE CALCULATEDFIELD.MAINTABLE = INPUTSUM.INPUTTABLE - OUTPUTSUM.OUTPUTTABLE

(which gives a difference of the two)?

Second question:

if the above isn't possible, can I do this through an SQL code ?

i.e 

SELECT(data from INPUTSUM) 
FROM(INPUTTABLE)
- 
SELECT(data from OUTPUTSUM) 
FROM(OUTPUTTABLE)
David Lee
  • 2,040
  • 17
  • 36
TylerStudent
  • 15
  • 3

1 Answers1

0

Try this:

SELECT SUM(T.INPUTSUM) - SUM(T.OUTPUTSUM) AS RESULTSUM 
FROM
    (
    SELECT INPUTSUM, 0 AS OUTPUTSUM
    FROM INPUTTABLE
    UNION 
    SELECT 0 AS INPUTSUM, OUTPUTSUM
    FROM OUTPUTTABLE
) AS T
Kostas K.
  • 8,293
  • 2
  • 22
  • 28