Search code examples
sqlms-accessvb6

Access Vb6 query

I need your help building a sql query using vb6 and a access db. Here is the scenario: 2 Tables, Give and Have Tb1 fields Id, Name, Amount Tb2 Id, Name, Amount I need to have the total amount for each name in both tables so to have total Give column and total have column but my query doesn't function

Select tb1.id,tb1.name,sum(tb1.amount) as TG, tb2.id,tb2.name,sum(tb2.amount) as TH
from tb1 inner join 
     tb2
     on tb1.id=tb2.id
group by... Etc

If i have 10 records where id = 1 on tb1 and 3 records on tb 2 the total amount on tb2 is wrong (it repeats the sum on tb2 for each record on tb1)

I have tried also using Union obtaining a correct result in row but i should want to obtain something like

Id Name Have Give
1 John Doe 200,00 76,00

I hope to explain better by pics

Tb1

Tb2

Result of query by @Parfait

Triyng @Parfait suggest, the result obtained is very similar to the query I wrote previously.

Thanks in advance for your help

Solution

  • Consider joining aggregates of both tables separately by id:

    Aggregate Queries (save as stored Access queries)

    SELECT tb1.idF
     , tb1.[name]
     , SUM(tb1.Give) AS TG
FROM tblGive tb1
GROUP BY tb1.idF
       , tb1.[name] 

    SELECT tb2.IDB
     , tb2.[name]
     , SUM(tb2.Have) AS TH
FROM tblHave tb2
GROUP BY tb2.IDB
       , tb2.name

    Final Query (running Full Join Query to return all distinct names in either tables)

    SELECT NZ(agg1.idF, agg2.idB) AS [id]
     , NZ(agg1.name, agg2.name) AS [name]
     , NZ(agg2.TH, 0) AS [Have]
     , NZ(agg1.TG, 0) AS [Give]
FROM tblGiveAgg agg1
LEFT JOIN tblHaveAgg agg2
   ON agg1.idF = agg2.idB

UNION 

SELECT NZ(agg1.idF, agg2.idB) AS [id]
     , NZ(agg1.name, agg2.name) AS [name]
     , NZ(agg2.TH, 0) AS [Have]
     , NZ(agg1.TG, 0) AS [Give]
FROM tblGiveAgg agg1
RIGHT JOIN tblHaveAgg agg2
   ON agg1.idF = agg2.idB;

    To demonstrate with below data

    CREATE TABLE tblGive (
   ID AUTOINCREMENT,
   IdF INTEGER,
   [Name] TEXT(10),
   Give INTEGER
);

INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (1, 'JOHN', 37);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (2, 'ANNA', 10);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (3, 'BILL', -37);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (2, 'ANNA', 116);
INSERT INTO tblGive (IdF, [Name], [Give]) VALUES (1, 'JOHN', 120);


CREATE TABLE tblHave (
   ID AUTOINCREMENT,
   IDB INTEGER,
   [Name] TEXT(10),
   Have INTEGER
);

INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (1, 'JOHN', 200);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (2, 'ANNA', 400);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (3, 'BILL', 150);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (1, 'JOHN', 25);
INSERT INTO tblHave (IDB, [Name], [Have]) VALUES (1, 'JOHN', 70);

    Create Table Output

    Final Full Join Query returns following result:

    Full Join Query Output