Access Vb6 query

Question

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

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

Thanks in advance for your help

score 1 · Answer 1 · answered Dec 17 '20 at 22:39

1

Try using union all and then aggregating:

Select id, name, sum(tg) as tg, sum(th) as th
from (select id, name, amount as tg, 0 as th from tb1
      union all 
      select id, name, 0, amount from tbl2
     ) as t
group by id, name;

I'm not sure if all versions of MS Access support union all in the from clause like that. If not, that piece needs to be encapsulated in a view.

answered Dec 17 '20 at 22:39

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, unfortunately i tried this query but i have got an error , so i think it isn't supported. However i'm just triyng changing something. I will inform you later – Jayelef Dec 18 '20 at 09:01

Parfait · Accepted Answer · 2020-12-22T22:05:16.790

1

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);

Final Full Join Query returns following result:

edited Dec 22 '20 at 22:05

answered Dec 17 '20 at 23:24

Parfait

104,375
17
94
125

Hi @Parfait, thank you also for your answer. Your sql query gives a result, but it is different from my expectative, because it aggregate only the names in both tables. So if i have the scenario that i will illustrate below Tb1 1 John 150 2 Mike 300 1 John 200 Tb2 16 Tony 32 1 John 50 20 Anna 25 it gives only John with his correct amount in difference Give / Have, insteed i shoud want to find 1 John 350 50 2 Mike 300 0 20 Anna 0 25 Thanks – Jayelef Dec 18 '20 at 09:15
Thank you @Parfait, but i receive a syntax error in Join procedure. Obviously the two queries represented above work perfectly, it isn't so for the final query. i am still doing some testing to see if the error is caused by transcription errors – Jayelef Dec 18 '20 at 18:40
Thanks for your new answer, I had seen the forgetfulness and I had already corrected it in my test, the query works perfectly, unfortunately I have to tell you that even this formula does not produce the result I wanted. I get the names repeated in the same way as with the use of a normal INNER JOIN. – Jayelef Dec 19 '20 at 09:38
Thanks @Parfait, I have posted 3 explanatory pics ;-) – Jayelef Dec 20 '20 at 10:40
The third. First image is the table called Give, the second is the Have table. As you can see, in each table, you can find many indipendent movements. So, Bill have to receive money but he hasn't nothing to pay, others have records in both tables. The result could be the sinthesys of all movements in a column with an one row for each IDF – Jayelef Dec 21 '20 at 13:49
How can that last resultset be the outcome of this solution? The aggregate queries should return a single pairing of `Id` and `Name` with `Have` and `Give` aggregated to single values. I just tested in Access using your posted first two images and return 3 records for 1 John, 2 Anna, 3 Bill. (I assume 3rd row in 2nd image should be 'Bill'.) You may be using `ID` which is an autonumber in your images but the `ID` in this solution would correspond to your `IdF` and `IDB`. – Parfait Dec 21 '20 at 17:00
Did you use `IDF` and `IDB` then? Don't use `ID` anywhere in your version including aggregate and final full join queries. (Please delete earlier comments no longer relevant). – Parfait Dec 21 '20 at 22:23
Yes, as you suggested through your query, I used the IDF and IDB fields but the result is what you see in the third figure. If you then tell me that you have tried your query on access and it works, I have to see if by chance something has been modified badly by me. I have nothing else to think about. I try again – Jayelef Dec 22 '20 at 11:41
See my extended answer demonstrating solution with your posted data. I adjusted queries too to use `IDF` and `IDB`. – Parfait Dec 22 '20 at 16:23

score 0 · Answer 3 · answered Dec 24 '20 at 11:43

Using this DDL

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

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


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

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

This UNION ALL query works

SELECT      Name
            , SUM(Give) AS TotalGive
            , SUM(Have) AS YotalHave
FROM        (
            SELECT  Name, Give, 0 AS Have
            FROM    tb1
        
            UNION ALL 
        
            SELECT  Name, 0 AS Give, Have 
            FROM    tb2
            ) AS t
GROUP BY    Name;

Access Vb6 query

3 Answers3