asked on

Using SUM In MS-Access 2013

Hi Experts,

I faced a database query issue ... I tried to solve it myself (I'm using VB.Net VS 2015)but my solution i think that is not perform the mission well ...OK

I have a product put in more than one partition "say 2 partitions" (it must be more than 2 partitions)these partitions filled with the products as follow ...

product1 ==>>(partition1 = 5 pieces) and (partition2 = 1 pieces) and total pieces of product1 = 6 pieces...... and
product2 ==>>(partition10 = 1 pieces) and (partition15 = 0 pieces) and (partition3 = 0 pieces) and total pieces of product2 = 1 piece
product3 ==>>(partition10 = 0 pieces) and (partition2 = 1 pieces) and (partition1 = 0 pieces) and total pieces of product3 = 1 piece
product4 ==>>(partition2 = 2 pieces) and (partition3 = 1 pieces) and total pieces of product4 = 3 pieces
The question is how to query in my database to find if the (total pieces of specific product) became =1 piece
in order to make a report of the products that decreased and need to purchase

I think this photo is enough to indicate my needs ... The blue line indicates that ProductCode '6' has 3 partitions and total pieces = 4
The Red line indicates that ProductCode '8' has 2 partitions and total pieces = 1 ........................
when i made a query on 'StockNo' in the database i get all 'StockNo' that have 1 piece not the total pieces of 'StockNo' for specific product code.

Any Suggestions will be appreciated
Thanks in advance ............................

Fabrice Lambert

Hi,

create a query with the following SQL code:

SELECT	StockID,		ProductCode,
		ProductName,	Price,
		SellPrRice,		Category,
		Sum(StockNo),	UserName
FROM	Stock
GROUP BY	StockID,		ProductCode,
			ProductName,	Price,
			SellPrice,		Category,
			UserName;

Open in new window

Note: I removed the StockDate column as aggregating it here make no sens.

Amr Aly

ASKER

Your query has no WHERE condition how can i get sum for Every product code ????????????

 Using cn As New OleDbConnection(cs)
            cn.Open()
            'Using cmd As New OleDbCommand("SELECT (Product.ProductCode) AS [كود الصنف],
            '                                (Product.ProductName) AS [اسم الصنف], 
            '                                First((StockNo)) AS [الكمية بالباكية], 
            '                                First((Category)) AS [اسم الباكية],
            '                                First((Product.Price)) AS [سعر التكلفة], 
            '                                First((Product.SellPrice)) AS [سعر البيع],
            '                                First((StockDate)) AS [تاريخ دخول الصنف],
            '                                First((StockID)) AS [كود المخزون] 
            '                                FROM Product INNER JOIN Stock ON Product.ProductCode = Stock.ProductCode
            '                                WHERE Product.AVNo = -1
            '                                GROUP BY (Product.ProductCode), (Product.ProductName)
            '                                ORDER BY First(Stock.StockDate) DESC;", cn)
            'Dim str = "Select StockID,ProductCode,
            '      ProductName,Price,
            '      SellPrice,Category,
            '      Sum(StockNo) AS sumStockNo,
            '         User_Name 
            '            From Query2
            '            Where sumStockNo > 1
            '            GROUP BY StockID,ProductCode,
            '   ProductName,Price,
            '   SellPrice,Category,
            '   User_Name;"



            'Using cmd As New OleDbCommand("SELECT (ProductCode) AS [كود الصنف],
            '                                (ProductName) AS [اسم الصنف] 
            '                                FROM Product 
            '                                WHERE Product.AVNo = -1
            '                                ORDER BY ProductCode DESC", cn)
            Using cmd As New OleDbCommand("Select StockID,ProductCode,
		                                    ProductName,Price,
		                                    SellPrice,Category,
		                                    Sum(StockNo) AS StockNo,
	                                        User_Name 
                                            From QStock
                                            Where StockNo < 1
                                            GROUP BY StockID,ProductCode,
			                                ProductName,Price,
			                                SellPrice,Category,
			                                User_Name", cn)
                'cmd.Parameters.Add("@a", OleDbType.Integer).Value = Val(TextBox4.Text)
                Using ds As New DataSet, da As New OleDbDataAdapter(cmd)
                    da.Fill(ds, "QStock")
                    DataGridView2.DataSource = ds.Tables("QStock").DefaultView

                End Using
            End Using
        End Using

Open in new window

I used this before but it is useless ..... I made a query from your code and named it 'QStock' and this error occured
(" Circular reference caused by alias 'StockNo' in query definition's SELECT list. ")

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amr Aly

ASKER

your query result as the above extrat all 'StockNo' equal to 1 .... I used it as follow

SELECT  Stock.ProductCode, Stock.ProductName, Stock.Price, Stock.SellPrice, Stock.Category, Sum(Stock.StockNo) , Stock.User_Name
FROM Stock
GROUP BY  Stock.ProductCode, Stock.ProductName, Stock.Price, Stock.SellPrice, Stock.Category, Stock.User_Name
HAVING	 Sum(Stock.StockNo) <=1;

Open in new window

Amr Aly

ASKER

Wait I think you Solved My Issue ,. I removed username,price,sellprice and category the qurey became ....

SELECT Stock.ProductCode, Stock.ProductName, Sum(Stock.StockNo)
FROM Stock
GROUP BY Stock.ProductCode, Stock.ProductName
HAVING Sum(Stock.StockNo) <=1;

Open in new window

It's solved now ................ 8)
Many thanks sir

Amr Aly

ASKER

Thank you so much for help ......
Appreciate your efforts
Many thanks