How to update quantity of a table in respect of another table data

Question

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 6-Jul-22 1:35am

Purnendu Paul

Updated 7-Jul-22 23:49pm

Add a Solution

1 solution

Add a Solution

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

CHill60 · Answer 1 · 2022-07-07T05:24:00

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

How to update quantity of a table in respect of another table data

1 solution

Solution 1

Add your solution here

Preview 0

How to update quantity of a table in respect of another table data

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us