How to update field with count of another tabel

Question

0.00/5 (No votes)

See more:

Hello Experts
This is my issue

I am using vb6 and ms access
I have two table

Table name: Customer
id name count
1 A
2 B
3 C
4 D
count is null field here

Table name: Gold
custid gold
1 1
1 1
2 1
4 1

Here i want to update the count field in customer table with counting of gold.... Result should be like this

Table name: Customer
id name count
1 A 2
2 B 1
3 C 0 or null
4 D 1

Please help to solve..

What I have tried:

Googled a lot not found any solution..... plz help

Posted 8-Sep-18 7:07am

Member 13977568

Updated 9-Sep-18 7:28am

Add a Solution

Comments

M4rLask 8-Sep-18 15:02pm

Show us your code, use the "Improve question" widget to edit your question and provide better information.

Jörgen Andersson 8-Sep-18 15:39pm

"Googled a lot not found any solution"
I wonder what you searched for then?
Try to google: "access update sql query from another table" and you will get several results that give you the info you need.
For example: https://www.techonthenet.com/access/queries/update2.php
Or: https://stackoverflow.com/questions/787186/access-db-update-one-table-with-value-from-another
Both on the first page.

1 solution

Add a Solution

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

Maciej Los · Answer 1 · 2018-09-09T07:28:00

Why do you want to update anything? Use simple join[^] instead:

SQL

SELECT C.id, c.[name], SUM(g.[gold]) AS [CountOfGold]
FROM Customer AS C INNER JOIN Gold AS G ON C.id = G.custid
GROUP BY C.id, c.[name]

For further details, please see:
Visual Representation of SQL Joins[^]
SQL Aggregate Functions (SQL)[^]

How to update field with count of another tabel

1 solution

Solution 1

Add your solution here

Preview 0

How to update field with count of another tabel

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us