Click here to Skip to main content
15,498,943 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Respected,
In a stock Management project (VB6.0) I'm fetching a problem during updation of a table in respect of another table in same database (MS ACCESS).

Netstock Table:
Category Item Variant Brand P_Qty S_Qty Unit
Gel Pen Agni Gel Pen 0.6 MM Agni 0 0 No.
Ball Pen Agni Ball Pen 0.5 MM Agni 0 0 No.
Ball Pen Montex Ball Pen 0.5 MM Montex 0 0 No.
Ball Pen Cello Ball Pen 0.5 MM Cello 0 0 No.
Gel Pen Linc Gel Pen 0.6 MM Linc 0 0 No.

Inward Table:
Bill_No Bill_Date Category Item Variant Brand P_Qty Unit P_Rate
A001 07/02/2022 Ball Pen Agni Ball Pen 0.5 MM Agni 200 No. ₹ 1.25
A001 07/02/2022 Gel Pen Agni Gel Pen 0.6 MM Agni 300 No. ₹ 2.20
A001 07/02/2022 Ball Pen Cello Ball Pen 0.5 MM Cello 200 No. ₹ 2.30
B001 07/04/2022 Gel Pen Agni Gel Pen 0.6 MM Agni 400 No. ₹ 2.20
B001 05/07/2022 Ball Pen Agni Ball Pen 0.5 MM Agni 300 No. ₹ 1.25

After Enter data into Inward Output in Netstock Should be:

Category Item Variant Brand P_Qty S_Qty Unit
Gel Pen Agni Gel Pen 0.6 MM Agni 700 0 No.
Ball Pen Agni Ball Pen 0.5 MM Agni 500 0 No.
Ball Pen Montex Ball Pen 0.5 MM Montex 0 0 No.
Ball Pen Cello Ball Pen 0.5 MM Cello 200 0 No.
Gel Pen Linc Gel Pen 0.6 MM Linc 0 0 No.


Kindly help me to resolve the same. There are no Primary Key Used as well as tables are not linked in the database.

What I have tried:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset


Private Sub Form_Load()

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database.mdb;"

rs.CursorLocation = adUseClient

rs.Open "UPDATE Netstock SET Netstock.P_Qty = (SELECT Inward.Item, Inward.Category, Sum (Inward.P_Qty) From Inward GROUP BY Inward.Item, Inward.Category, Inward.Brand) Where Netstock.Item = Inward.Item AND Netstock.Category=Inward.Category AND Netstock.Brand=Inward.Brand;", con, adOpenStatic, adLockOptimistic

rs.Open "Select * From Netstock;", con, adOpenStatic, adLockOptimistic

Set DataGrid1.DataSource = rs

End Sub
Posted
Updated 8-Jul-22 0:49am

1 solution

Look at your sub-query
SQL
SELECT Inward.Item, Inward.Category, Sum (Inward.P_Qty) From Inward GROUP BY Inward.Item, Inward.Category, Inward.Brand
It is returning 3 columns in the result set, yet you are trying to assign all three values to a single column in Netstock with SET Netstock.P_Qty =, which you simply can't do.

If this was SQL then you could simply write this query
SQL
UPDATE n
SET P_Qty = temp.total
FROM 
Netstock n 
INNER JOIN (SELECT Item, Category, Sum (P_Qty) as total From Inward GROUP BY Item, Category, Brand) temp ON temp.Item = n.item and temp.Category = n.Category and ...
but you have also tagged Access and that query won't work in Access - it will complain that the JOIN is not an updateable query, despite the fact you are only attempting to update one of the tables.

Instead you should write a QUERY for the sub-query
SQL
SELECT Item, Category, Sum (P_Qty) as total From @Inward GROUP BY Item, Category, Brand
and redirect the output into another table e.g. TempResults
SQL
SELECT Inward.Item, Inward.Category, Sum(Inward.P_Qty) AS total INTO TempResults
FROM Inward
GROUP BY Inward.Item, Inward.Category, Inward.Brand;
You can then use that table in the join
SQL
UPDATE n
SET P_Qty = temp.total
FROM 
Netstock n 
INNER JOIN tempResults t ON t.Item = n.item and t.Category = n.Category and ...
The next step is to look to normalise your tables - you are repeating too much information about each item across multiple tables
 
Share this answer
 
Comments
Purnendu Paul 8-Jul-22 5:51am    
ried but still not solved. Is it necessary to create a Relationship between two tables in MS Access?
CHill60 8-Jul-22 5:56am    
No relationship is required. When you say "not solved" what exactly is happening?
Purnendu Paul 8-Jul-22 6:14am     CRLF
Also tried with the both of the following. Both shows "Operation must use an updateable query". Is it necessary to create a Relationship between two tables in MS Access before apply this code? Please Help: 1) rs.Open "UPDATE Netstock N Inner Join (Select Item, Sum(Qty) As TotalQty From Inward Group By Item)t On t.Item = N.Item Set PQty=TotalQty;", con, adOpenDynamic 2) rs.Open "UPDATE Netstock N SET N.PQty=(SELECT SUM(I.Qty) FROM Inward I WHERE I.Item = N.Item AND I.Brand = N.Brand);", con, adOpenDynamic
CHill60 8-Jul-22 8:28am    
Please read my solution carefully. I told you that you would get that error .. Quote: "that query won't work in Access - it will complain that the JOIN is not an updateable query, despite the fact you are only attempting to update one of the tables" You MUST redirect the output from the sub-query into a new table and join to that table.
Purnendu Paul 8-Jul-22 6:16am    
I just change the Field Name from P_Qty to PQty in Netstock Table & Qty in Inward Table

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900