?
Solved

Need to add two fields and substract one other field

Posted on 2006-05-08
11
Medium Priority
?
185 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:deepsee34
  • 5
  • 3
  • 2
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 16632081
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
 

Author Comment

by:deepsee34
ID: 16632445
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16632854
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 44

Expert Comment

by:GRayL
ID: 16633897
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
 

Author Comment

by:deepsee34
ID: 16637836
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 2000 total points
ID: 16638248
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16638252
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16640608
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16641716
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16642174
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question