Jump to content

Help For Sql Query


Guest sazd1
 Share

Recommended Posts

I am working on a Stock calculation query.I am working in VB express 2008 with MsAccess as database. I searched some forums and with the help of that i am able to form the following query to calculate the balance of Stock.My tables in MsAccess are:ItemsTable ITId ItemId Description 1 1 Coca Cola Normal 2 2 Coca Cola Zero PurchaseTable PId PDate ItemId Price Quantity Amount 1 28/8/2009 1 1,00 10 10,00 2 28/8/2009 2 1,00 5 5,00 3 29/8/2009 1 1,00 5 5,00 4 29/8/2009 2 1,00 10 10,00 5 30/8/2009 1 1,00 10 10,00 6 30/8/2009 2 1,00 5 5,00 SalesTable SId SDate ItemId Price Quantity Amount 1 30/8/2009 1 2,70 2 5,40 2 30/8/2009 2 2,70 3 7,10 3 31/8/2009 1 2,70 1 2,70 4 31/8/2009 2 2,70 2 2,70 My query is as under:

 Dim cmdText As String = "Select a.ItemId,i.Description,Sum(QuantityPurchased) AS QuantityPurchased,Sum(QuantitySold) AS QuantitySold, " & _		"(Sum(QuantityPurchased) - Sum(QuantitySold)) as Balance  " & _		"From " & _		"( SELECT pt.PDate as TransactionDate, pt.ItemId, pt.Quantity AS QuantityPurchased, " & _		"0 AS QuantitySold FROM PurchaseTable pt " & _		"UNION ALL " & _		"Select st.SDate as TransactionDate, st.ItemId, 0 AS QuantityPurchased, " & _		"st.Quantity AS QuantitySold " & _		"From SalesTable st ) a " & _		"Join ItemsTable i On a.ItemId = i.ItemId  " & _		"WHERE a.TransactionDate Between @START and @END " & _		"GROUP BY a.ItemId, i.Description "

The above query is giving error as "Error at From clause". The query will calculate the balance of stock between interval of two dates and will display it in LIstview.Please advise what i am doing wrong with this query.

Edited by sazd1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...