The best option should be the query I offered but since that isn't working with your 1,000,000 records I am at a loss. Not sure any of the options I listed will perform any faster. Some of the performance issue might be your network.
I think I have contributed all I can to this discussion. You just might have to find a professional VB/VBA programmer who also has an understanding of SQL database and split design running on network.