How to write a fast counting query for a large table?

I have two tables, Table1 with 100,000 rows and Table2 with 400,000 rows. Both tables have a field called Email. I need to insert a new field into Table1 which will indicate the number of times the Email from each row in Table1 appears in Table2.

I wrote a binary count function for Excel which perfo开发者_运维知识库rms this in a few seconds on this data sample. Is it possible to perform it this fast in Access?

Thank you.

Does this query express what you want to find from Table2?

SELECT Email, Count(*) AS number_matches
FROM Table2
GROUP BY Email;

If that is what you want, I don't understand why you would store number_matches in another table. Just use this query wherever/whenever you need number_matches.

You should have an index on Email for Table2.

Update: I offer this example to illustrate how fast Count() with GROUP BY can be for an indexed field.

SELECT really_big_table.just_some_text, Count(*) AS CountOfMatches
FROM really_big_table
GROUP BY really_big_table.just_some_text;

really_big_table contains 10,776,000 rows. That size is way beyond what you would ordinarily expect to find in a real-word Access (Jet/ACE) database. I keep it around for extreme stress testing of different database operations.

The field, just_some_text, is indexed. With that index, the query completes in well under a minute. I didn't bother to time it more precisely because I was only interested in a rough comparison with the several minutes the OP's similar query took for a table which includes less than 5% of the number of rows as mine.

I don't understand why the OP's query is so much slower by comparison. My intention here is to warn other readers not to dismiss this method. In my experience, the speed of operations like this ranges from acceptable to blazingly fast ... as long as the database engine has an appropriate index to work with. At least give it a try before you resort to copying values redundantly between tables.