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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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