hi All, I am currently dealing with a number of tables each with over 40,000 records. I have these tables in Access and i'm planning to do some SQL query on them.

The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.

I have something like:
there are three tables, T1 , T2 and T3.

T1: T2 T3

ID Value ID Value ID Value
1 100 2 5 3 1
2 200 3 5 4 1
3 300 4 5
4 400

My job is to add up all the corresponding values in the three tables and come out with something like this :

Results
ID Value
1 100
2 205
3 306
4 406

So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!

Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.

Thanks!

Your query would be:
Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.

thanks I just tried it and it works very well.

however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
but I ONLY want records that exist in T1.

What should I do?
Thanks!

In that case, perhaps an outer join is more appropriate?

select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
from t1
left outer join t2 on t2.id = t1.id
left outer join t3 on t3.id = t1.id;