I have an Access 2003 database with 2 tables.
Summary is a very large table with 1 summary record for all the transactions per account and sub-account per day. So if 1 account has 3 sub-accounts on April 4, 2009, it will have 3 records that sum up all the transactions for each account/sub-account/date combination. It currently has approximately 500,000 records.
I receive a transactions table on a periodic basis. It is also a very large table, with up to 500,000 records. There is unpredictable overlap between the transactions table I receive and the current summary table. If there are any records in the transactions table for which there is already an account & date record in the summary table, then I don't want to import those (note that this ignores the sub-accounts at this point). I then want to summarize all the other transaction records by account/sub-account/date and add them to the summary table.
I'm looking for a sql query or another simple process that I can use to do this process as quickly as possible. I would prefer that this process not change the data in the transactions table or create temporary tables, but am willing to do either if that is the only solution. My initial attempt was to delete all the records from the transactions table using a subquery but that takes far too long.
I'm going to put $30 in escrow for this project. I'm looking for a query or process that will run quickly, along with a clear explanation of how the query works.