SQL

[Wantula]

I decided to repost this question because the response i got didnt work, it
was giving me this error" Syntax error in the FROM clause".
Lynn Trap, thanks(atleast I have i have an idea), please give me another
syntax or check the syntax you gave me.

I have two SQL statements, one is giving me the total Quantity
available(from Stock table) and the other one is giving me the total number
of Quantity sold out.(from Sales Table). All these are GROUPED BY Item Name
e.g Handbags 3

Shoes 10
Is there a way of creating another SQL statement that will subtract the
Quantity sold from the total Quantity available so that I know the STOCK
REMAINING.
I need the syntax

 

[Evan Camilleri]

1) Make sure in both queries you have StockCode

2) Make new query and insert both queries, link by StockCode

3) Add fields to query: StockCode, QtyAvail, QtySold, (QtyAvail-QtySold)


Evan Camilleri

 

[Lynn Trapp]

Can you post the exact SQL statement you tried so someone here can see what
the syntax error might be?

 

[David Seeto via AccessMonster.com]

I've replied to Wantula's question in what I suspect is his original post - please don't post twice, as it gets confusing. You'd be much better off finding your original post and adding a new comment to it - then, people can see everyone's comments.

I think the problem here is that you're trying to do the whole thing in one step. I suspect it's possible, but it would lead to a very complex SQL statement. I personally think you would be better off saving your two SELECT statements as individual queries and then, as the others here and I suggest in the other thread, Join those and do your calculation there.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-tablesdbdesign/6300
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=04e5498cc44a438b95a3d781fef5872e
*****************************************