I have two tables (Current and Prior), each with close to 2 million records, in two separate databases. With just the one table in each database the two databases are sized around 1.5 GB each. That's after compacting. I use a 3rd database that links to them among other terribly large databases. Yes, I know, we need SQL Server and are trying to get corporate to allow us to have tools like that besides only Access 2003. Until then we have to try and make do.
I'm trying to run an unmatched query to find the records in the "Current" table that aren't in the "Prior" table so I know which records are new this month. In order to match the records properly I need to match two fields, "Account" and "System".
The unmatched query wizard of course only lets you set up 1 field match, so I manually changed the query to include the 2nd field match as well. Just running a select query at this point, not an update, to see how many matches we have takes a LOOOOOONG time. And we aren't getting the results we expect at all. Granted our expectations might be incorrect, but I'm wondering if I'm doing this wrong.
What I have currently is:
Is that correct? Is there a better way of doing this considering how huge the tables are? Obviously using recordsets with dLookups is out of the question here.
I thought maybe having 2 fields to match was causing confusion so I attempted a query that tried to match one concatenated field in each table and that failed miserably. As did running queries with nested SQL queries.
Thanks in advance if anyone can shed any light on this for me. I have a feeling I'm just bumping our heads on a technological wall though. Here's hoping the SQL Server fairy makes a visit soon!
I'm trying to run an unmatched query to find the records in the "Current" table that aren't in the "Prior" table so I know which records are new this month. In order to match the records properly I need to match two fields, "Account" and "System".
The unmatched query wizard of course only lets you set up 1 field match, so I manually changed the query to include the 2nd field match as well. Just running a select query at this point, not an update, to see how many matches we have takes a LOOOOOONG time. And we aren't getting the results we expect at all. Granted our expectations might be incorrect, but I'm wondering if I'm doing this wrong.
What I have currently is:
Code:
SELECT Current_Details.AcctSchd, Current_Details.System
FROM Current_Details LEFT JOIN Prior_Details ON (Current_Details.System = Prior_Details.System) AND (Current_Details.AcctSchd = Prior_Details.AcctSchd)
WHERE (((Prior_Details.AcctSchd) Is Null) AND ((Prior_Details.System) Is Null));
Is that correct? Is there a better way of doing this considering how huge the tables are? Obviously using recordsets with dLookups is out of the question here.
I thought maybe having 2 fields to match was causing confusion so I attempted a query that tried to match one concatenated field in each table and that failed miserably. As did running queries with nested SQL queries.
Thanks in advance if anyone can shed any light on this for me. I have a feeling I'm just bumping our heads on a technological wall though. Here's hoping the SQL Server fairy makes a visit soon!