SELECT No_ AS [No.],
       [Description] AS [Description],
       [Standard Cost] AS [Price],
       [Vendor No_] AS [Vendor],
       [Stores] AS [Stores]
FROM   DATABASE$Item  AS Item_Entry
       INNER JOIN (
                SELECT [Item No_] AS [Item No_],
                       SUM([Remaining Quantity]) AS [Stores]
                FROM   [DATABASE%Item Entry]
                WHERE  [Location Code] = 'STORES'
                GROUP BY
                       [Item No_]
            ) AS Inv
            ON  Inv.Item.No_ = [Item Entry].[Item No_]

SELECT No_ AS [No.],
       [Description] AS [Description],
       [Standard Cost] AS [Price],
       [Vendor No_] AS [Vendor],
       [Stores] AS [Stores]
FROM   DATABASE$Item
       INNER JOIN (
                SELECT [Item No_] AS [Item No_],
                       SUM([Remaining Quantity]) AS [Stores]
                FROM   [DATABASE%Item Entry]
                WHERE  [Location Code] = 'STORES'
                GROUP BY
                       [Item No_]
            ) AS Inv
            ON  DATABASE$Item.Item.No_ = Inv.[Item No_]

quote:

---

Originally posted by okjam

I have 2 Tables.

1 Is DATABASE$Item
1 is DATABASE$Item Entry

Item = Item information
Item Entry = Ledger of transactions

So what i want to do is join the SUM OF remaining to the current table so i can have item info + inventory (SUM OF remaning from Item entry)

---