Grouping and getting Variance from 2 tables for selected Items

[pedie]

Hi All, I'm trying to group and get a Variance from 2 tables for selected Items.
I have the sample table formats and expected result table format below.
Please help me out.

Regards,
Pedie

Sheet4 B C D E F G H I J K L M 1 table-1 table-2 expected result 2 ID INV-TCK Amt ID INV-TCK Amt INV-TCK Amt-Tb1 Amt-Tb2 Var 3 1 SD-HHH-JK-1 2092 1 SD-HHH-JK-1 6138 SD-HHH-JK-1 6138 11333 -5195 4 2 SD-HHH-JK-1 2044 2 SD-HHH-JK-2 3878 SD-HHH-JK-2 3878 18162 -14284 5 3 SD-HHH-JK-1 7197 3 SD-HHH-JK-3 4840 SD-HHH-JK-3 4840 15948 -11108 6 28 SD-HHH-JK-2 7228 4 SD-HHH-JK-4 5981 SD-HHH-JK-4 5981 16207 -10226 7 29 SD-HHH-JK-2 7140 5 SD-HHH-JK-5 7509 SD-HHH-JK-5 7509 7748 -239 8 30 SD-HHH-JK-2 3794 6 SD-HHH-JK-6 4288 SD-HHH-JK-6 4788 15231 -10443 9 31 SD-HHH-JK-3 7443 6 SD-HHH-JK-6 500 10 32 SD-HHH-JK-3 6210 11 33 SD-HHH-JK-3 2295 12 34 SD-HHH-JK-4 7083 13 35 SD-HHH-JK-4 5957 14 36 SD-HHH-JK-4 3167 15 37 SD-HHH-JK-5 2497 16 38 SD-HHH-JK-5 1930 17 39 SD-HHH-JK-5 3321 18 40 SD-HHH-JK-6 3052 19 41 SD-HHH-JK-6 5145 20 42 SD-HHH-JK-6 7034 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[pedie]

i tried this but sure didnt work out. table 2 items are getting duplicate amt
SELECT tb1.ID, tb1.[INV-TCK], Sum(tb1.AMT) AS SumOfAMT, Sum(tb2.Amt) AS SumOfAmt1, Sum(([tb2].Amt-[tb1].Amt)) AS VAR
FROM tb2 INNER JOIN tb1 ON tb2.[INV-TCK] = tb1.[INV-TCK]
GROUP BY tb1.ID, tb1.[INV-TCK];

 

[pedie]

I created 2 queries by grouping them as INV field level then created another query (which is the 3rd one) to combine both of them. Now it seems to give me the correct result.
Please validate it for me.

[/FONT][/SIZE][SIZE=4][FONT=courier new]SELECT Qry2.[INV-TCK], [Qry2].[SumOfAmt]-[Qry1].[SumOfAmt] AS Var[/FONT][/SIZE]
[SIZE=4][FONT=courier new]FROM ([/FONT][/SIZE]
[SIZE=4][FONT=courier new]SELECT tb1.[INV-TCK], Sum(tb1.AMT) AS SumOfAMT FROM tb1 GROUP BY tb1.[INV-TCK])  AS Qry1[/FONT][/SIZE]
[SIZE=4][FONT=courier new] INNER JOIN ([/FONT][/SIZE]
[SIZE=4][FONT=courier new]SELECT tb2.[INV-TCK], Sum(tb2.Amt) AS SumOfAmt, tb2.X1, tb2.[X2] FROM tb2 GROUP BY tb2.[INV-TCK], tb2.X1, tb2.[X2] HAVING (((tb2.X1)='Y') And ((tb2.[X2])='Y')))  AS Qry2 [/FONT][/SIZE]
[SIZE=4][FONT=courier new]ON Qry1.[INV-TCK] = Qry2.[INV-TCK];[/FONT][/SIZE]
[SIZE=4][FONT=courier new]