Questions

Select (claculated field) Where (calculated field) >= t1.field2

Tags:
+
0 Votes
Locked

Select (claculated field) Where (calculated field) >= t1.field2

shilyn
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
  • +
    0 Votes
    ch3cking

    Hi Shily,

    I see three problems here: One, you are comparing the value of your calculated field to a field in a third table, t3 which is not listed in your from clause. To be able to reference t3.[minimumNumber] your query should read: SELECT Sum(t1.val1)-Sum(t2.val1) AS [on hand]
    FROM t1, t2, t3
    WHERE [on hand])<=[t3].[MinimumNumber];.
    The Second problem is that you are referring to the alias "[on hand]" in your Where clause. This is not an allowed SQL syntax. Thus, your query should read:SELECT Sum(t1.val1)-Sum(t2.val1) AS [on hand] FROM t1, t2, t3 WHERE
    Sum(t1.val1)-Sum(t2.val1)<=[t3].[MinimumNumber];.
    The third problem with your query is that your tables do not appear to be properly joined. Using the join syntax you have opted for, your where clause should implement a join between tables t1, t2 and t3 as follows:
    SELECT Sum(t1.val1)-Sum(t2.val1) AS [on hand] FROM t1, t2, t3
    WHERE [t1].[keyColumn]=t2.[Keycolumn]
    AND [t2].[keyColumn]=[t3].[keyColumn]
    AND (Sum(t1.val1)-Sum(t2.val1))<=[t3].[MinimumNumber];.
    [KeyColumn] above of course refers to which ever column in your three tables hold values common to a column in the others.

    Hope this helps

    Mabs

    +
    0 Votes
    msi77

    select [item nr], tv1-tv2 AS res
    from
    (select [item nr] AS [id1], sum(val1) AS t1v from t1 group by [item nr]) AS s1
    join
    (select [item nr] AS [id2], sum(val1) AS t2v from t2 group by [item nr]) AS s2
    on id1=id2
    join t3
    on id1=t3.[item nr]
    where tv1-tv2<=[t3].[MinimumNumber]
    <br/><br/>
    <a href="http://www.sql-ex.ru/"> SQL Exercises </a>

    +
    0 Votes
    robert.mcmurray

    I have found UNIONs to be of great speed. UNION all three tables together and group on the item nr. Use the negative values from t2 and t3 to find all items that go below the minimum qty.

    SELECT MAX(tTemp.item_nr) as item, SUM(on_hand) as balance FROM
    (
    SELECT +sum(on_hand) as on_hand, item_nr FROM t1 GROUP BY item_nr
    UNION ALL
    SELECT -sum(on_hand) as on_hand, item_nr FROM t2 GROUP BY item_nr
    UNION ALL
    SELECT -min_qty as on_hand, item_nr FROM t3
    ) AS tTemp
    GROUP BY tTemp.item_nr
    HAVING SUM(on_hand) < 0

  • +
    0 Votes
    ch3cking

    Hi Shily,

    I see three problems here: One, you are comparing the value of your calculated field to a field in a third table, t3 which is not listed in your from clause. To be able to reference t3.[minimumNumber] your query should read: SELECT Sum(t1.val1)-Sum(t2.val1) AS [on hand]
    FROM t1, t2, t3
    WHERE [on hand])<=[t3].[MinimumNumber];.
    The Second problem is that you are referring to the alias "[on hand]" in your Where clause. This is not an allowed SQL syntax. Thus, your query should read:SELECT Sum(t1.val1)-Sum(t2.val1) AS [on hand] FROM t1, t2, t3 WHERE
    Sum(t1.val1)-Sum(t2.val1)<=[t3].[MinimumNumber];.
    The third problem with your query is that your tables do not appear to be properly joined. Using the join syntax you have opted for, your where clause should implement a join between tables t1, t2 and t3 as follows:
    SELECT Sum(t1.val1)-Sum(t2.val1) AS [on hand] FROM t1, t2, t3
    WHERE [t1].[keyColumn]=t2.[Keycolumn]
    AND [t2].[keyColumn]=[t3].[keyColumn]
    AND (Sum(t1.val1)-Sum(t2.val1))<=[t3].[MinimumNumber];.
    [KeyColumn] above of course refers to which ever column in your three tables hold values common to a column in the others.

    Hope this helps

    Mabs

    +
    0 Votes
    msi77

    select [item nr], tv1-tv2 AS res
    from
    (select [item nr] AS [id1], sum(val1) AS t1v from t1 group by [item nr]) AS s1
    join
    (select [item nr] AS [id2], sum(val1) AS t2v from t2 group by [item nr]) AS s2
    on id1=id2
    join t3
    on id1=t3.[item nr]
    where tv1-tv2<=[t3].[MinimumNumber]
    <br/><br/>
    <a href="http://www.sql-ex.ru/"> SQL Exercises </a>

    +
    0 Votes
    robert.mcmurray

    I have found UNIONs to be of great speed. UNION all three tables together and group on the item nr. Use the negative values from t2 and t3 to find all items that go below the minimum qty.

    SELECT MAX(tTemp.item_nr) as item, SUM(on_hand) as balance FROM
    (
    SELECT +sum(on_hand) as on_hand, item_nr FROM t1 GROUP BY item_nr
    UNION ALL
    SELECT -sum(on_hand) as on_hand, item_nr FROM t2 GROUP BY item_nr
    UNION ALL
    SELECT -min_qty as on_hand, item_nr FROM t3
    ) AS tTemp
    GROUP BY tTemp.item_nr
    HAVING SUM(on_hand) < 0