matching in access

[kda]

Dear all,

Could you please help me in Ms access, I want matching two tables as bellow:
Table1
18001014316917 -1,599.25
18001017149617 -7,416.78
18001018972016 -175,250.12
18001027281717 -36,171.56
18001054046011 -439,390.92
18001071468818 -273,882.16

Table2
18001017149617 -7,416.78
18001018972016 -200,950.12
18001021756616 -18,064.85
18001027281717 -53,818.56
18001054046011 -449,390.92
18001071468818 -291,193.82


And my purpose want to match to find the different amount as result bellow:
Result
1 18001014316917 FALSE 1,599.25
2 18001017149617 TRUE 0.00
3 18001018972016 TRUE -25,700.00
4 18001021756616 FALSE -18,064.85
5 18001027281717 TRUE -17,647.00
6 18001054046011 TRUE -10,000.00
7 18001071468818 TRUE -17,311.66


In Ms access, Could you please guide me to solve it.

Thanks

[alansidman]

Here is the SQL statement for you. I have named the tables sheet 1 and sheet 2 and your fields as field1 and field2. You will have to rename them to your named fields.

SELECT Sheet1.Field1, Sheet1.Field2, IIf([Sheet1].[Field1]=[Sheet2].[Field1],"True","False") AS Expr1, Nz([Sheet2].[Field2],0)-Nz([Sheet1].[Field2],0) AS Expr2
FROM Sheet1 LEFT JOIN Sheet2 ON Sheet1.Field1 = Sheet2.Field1;

Alan

[kda]

HI,
Thank you very much for your helping
But ur result is:
ID24 amt24 Expr1 Expr2
18001018972016 -175250.12 True -25700
18001071468818 -273882.16 True -17311.66
18001054046011 -439390.92 True -10000
18001027281717 -36171.56 True -17647
18001017149617 -7416.78 True 0
18001014316917 -1599.25 False 1599.25

>>>>>>But ur result lost one ID:
N ID25 Amt25
3 18001021756616 -18064.85

Could you pls help me more to complete it?

Thanks

[alansidman]

I don't have time to work on it now, but some thoughts you might try. Build another query and join the tables with an outer join. Then take the results of the two queries and do a UNION ALL on the two queries. I have not tested this procedure, but you might be able to make it work.

Alan

[alansidman]

See attached. Needed four queries with an aggregate.

[kda]

Thank you very much

[kda]

Hi alansidman,

Thank you very much for ur helping

Regards,