SQL Problem

[Michael Puk]

The format sent earlier is not so clear,
I sent it again.

Hi ,
I want to write a SQL in ms Access : Insert into Tab_C by
Select Tab_A Inner Join with Tab_B
The examples are list below. The result is shown in Tab_C

Grateful if any expert tell me how to write this SQL
Thank you.

Tab_A Tab_B
Ac_No A_Item A_Total Ac_No B_Item B_Total
1 CAT 20 1 CAT 12
1 CAT 5 2 CAT 3
2 CAT 11 2 CAT 17
2 DOG 6 2 DOG 21
2 DOG 2 3 CAT 26
3 CAT 7 3 CAT
2
4 DOG 15 4 DOG 11

Tab_C
Ac_No A_Item A_Total B_Item B_Total
1 CAT 25 CAT 12
2 CAT 11 CAT 20
2 DOG 8 DOG 21
3 CAT 7 CAT 28
4 DOG 15 DOG 11

 

[Roger Carlson]

I can't think of a way to do it in one query. I would use 3:

qryTab_A:
SELECT Tab_A.Ac_No, Tab_A.A_Item, Sum(Tab_A.A_Total) AS SumOfA_Total
FROM Tab_A
GROUP BY Tab_A.Ac_No, Tab_A.A_Item;

qryTab_B:
SELECT Tab_B.Ac_No, Tab_B.B_Item, Sum(Tab_B.B_Total) AS SumOfB_Total
FROM Tab_B
GROUP BY Tab_B.Ac_No, Tab_B.B_Item;

finally qryTab_A_Tab_B:
SELECT qryTab_A.Ac_No, qryTab_A.A_Item, qryTab_A.SumOfA_Total,
QryTab_B.B_Item, QryTab_B.SumOfB_Total
FROM qryTab_A INNER JOIN QryTab_B ON (qryTab_A.A_Item = QryTab_B.B_Item) AND
(qryTab_A.Ac_No = QryTab_B.Ac_No)
GROUP BY qryTab_A.Ac_No, qryTab_A.A_Item, qryTab_A.SumOfA_Total,
QryTab_B.B_Item, QryTab_B.SumOfB_Total;

The problem with doing it all in one query like this:
SELECT Tab_A.Ac_No, Tab_A.A_Item, Sum(Tab_A.A_Total) AS SumOfA_Total,
Tab_B.B_Item, Sum(Tab_B.B_Total) AS SumOfB_Total
FROM Tab_A INNER JOIN Tab_B ON (Tab_A.A_Item = Tab_B.B_Item) AND
(Tab_A.Ac_No = Tab_B.Ac_No)
GROUP BY Tab_A.Ac_No, Tab_A.A_Item, Tab_B.B_Item;

Is that they don't get grouped separately and you come up with results like
this:

Ac_No A_Item SumOfA_Total B_Item SumOfB_Total\
------- -------- --------------- ------- ---------------
1 CAT 25 CAT 24
2 CAT 22 CAT 20
2 DOG 8 DOG 21

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

 

[Michael Puk]

It works with 3 Queries.
Thank you very much, Roger,