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.
Hi there,
No problem then im an excel and access vba expert with over 11 years experience.
Please can you provide more details in PMB then.
THanks
Nash
So long as you don't mind me taking it out of access and using mysql or another db platform, I think I can help you out, and provide an ANSI-SQL compliant, commented, readable query to work from. I have three solid years in data analysis, reporting and integration.
Hi there.
actually wat u need is not possible by a single or multiple lines of query in MSACCESs.
either u need one tool which does these queries in batch.
or u will need some other pure sql compliant database.
like mysql or sql server or oracle.
what i wud like to suggest though, is i can build a small app which will first query all summary Records
in memory, and check against those in current transaction table. all being grouped with account and subacc and date. and insert accordingly(if they are new to summary table). what u do manually by urself.
bidding amount is negotiable.
Please feel free to contact for more details or explanation.