Need to add two fields and substract one other field

I have three tables: sales,item,inventoryadjust and i want to add  the quantityadded field in inventoryadjust table to the initialstockfield in the item table then substract the quantitysold field in sales table ,all this would be grouped by the itemdescription in the item table. but it was giving me some lousy numbers.
deepsee34Asked:
Who is Participating?
 
Arthur_WoodConnect With a Mentor Commented:
SELECT Item.Description, Sum(Sales.QtySold) AS QtySold, Sum([InventoryAdjust.QtyPurchase]+[Item.InitialStock]) AS TotalInventory, (Sum([InventoryAdjust.QtyPurchase]+[Item.InitialStock])-Sum(Sales.QtySold)) AS QuantityOnHand
FROM Inventoryadjust, Item, Sales where Inventoryadjust.itemdescription= Item.itemdescription and
Inventoryadjust.itemdescription= sales.itemdescription
GROUP BY Item.Description;

Using itemdescription as the field to link (relate) the tables is probably not the best idea.  YOu should have a Field, call it ItemID, in the Item Table, with the corresponfding field ItemID in the other two tables, to link the tables together

AW
0
 
GRayLCommented:
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;
0
 
deepsee34Author Commented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
GRayLCommented:
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;
0
 
GRayLCommented:
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?
0
 
deepsee34Author Commented:
its giving me syntax error(missing operator) in query expression  'a.Description = b.Description INNER JOIN Sales AS c ON a.Description = c.Description'
0
 
Arthur_WoodCommented:
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
0
 
GRayLCommented:
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";
0
 
Arthur_WoodCommented:
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
0
 
GRayLCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.