Need to add two fields and substract one other field

How does this run?

select a.initialstock, b.quanityadded, c.quantitysold, a.initialstock + b.quantityadded - c.quantitysold as modifiedstock from sales as a inner join inventoryadjust as b on a.itemdescription = b.itemdescription inner join sales as c on a.itemdescription = c.itemdescription;

i have 3 seperate table:

sales:itemdescription,quantitysold,pricesold,receiptno,total
inventoryadjust:itemdescription,quantitypurchase
item:itemdescription,itemid,initialstock,costprice

i wanted to run a query:

SELECT Item.Description, Sum(Sales.QtySold) AS QtySold, Sum([InventoryAdjust.QtyPurchase]+[Item.InitialStock]) AS TotalInventory, ([TotalInventory]-[QtySold]) AS QuantityOnHand
FROM Inventoryadjust, Item, Sales
GROUP BY Item.Description;

but it was giving some very lousy number , the tables are not related, i dont know what is wrong , please urgent

If each of the tables has to have the field itemdescription otherwise nothing would make sense, so then you must join them as I showed you in my first post - with these additions:

SELECT a.Description, Sum(a.InitialStock + b.QtyPurchase) AS TotalInventory, Sum(c.QtySold)  AS QtySold, TotalInventory-QtySold as QuantityOnHand  FROM Sales AS a INNER JOIN InventoryAdjust AS b ON a.Description = b.Bescription INNER JOIN Sales AS c ON a.Description = c.Description GROUP BY a.Description;

deepsee34:  Please confirm all three tables contain the field description or some other field which is the same between the three tables.  Otherwise how do you know what your are buying and selling?

its giving me syntax error(missing operator) in query expression  'a.Description = b.Description INNER JOIN Sales AS c ON a.Description = c.Description'

1) you cannot use ALIASES as fields in the SAME SQL command - you can use them if they are in a Sub-query.

2) you needed to link the common recordss between the three tables - as written, you would get a 'cartesian join' of the three tables.

AW

AW - I guess this won't work;-)

1) Select a.FName & " " & a.LName FROM IveBeenWrongBefore AS a WHERE a.FName = 'Arthur' AND a.LName = "Wood";

Yes, that will work (and that is NOT what I was saying).  But you CANNOT declare aliases for fields in the query, and then use THOSE same aliases in the SAME sql:

Select a.FName & " " & a.LName as FullName FROM IveBeenWrongBefore AS a WHERE FullName = "Arthur Wood"

or

Select (a.X + A.Y) as Value, (Value - 3) as Another_Value  FROM IveBeenWrongBefore AS a where Another_Value > 5

is likewise not allowed by Access SQL.

AW

Gottcha.  Any calculated field given an alias in the SELECT clause cannot be referred to anywhere after the FROM clause by that alias.  Thanks for the clarification.

Ray