complex query

[mikeva]

Hi there! I've got a complex query and am not sure how to accomplish the task. Here's the basic rundown.

I have three tables, Old, New, and MergedTableResults. The tables all have the same data structure, and the fields are A, B, C, D, E, F , G, H, and I. Fields G, H, and I do not contain data in Table New.
Here’s what I want to do. I want to compare records using the fields ( A, B, C, D, E ) in tables Old and New, when a match is found I need a new record created in MergedResultsTable that contains New.A, New.B, New.C, New.D, New.E,New.F, Old.G, Old.H, and Old.I from the two records that matched.
Any help or direction is appreciated – Thanks!

 

[JHB]

This should do it.

INSERT INTO MergedTableResults ( A, B, C, D, E, F, G, H, I )
SELECT New.A, New.B, New.C, New.D, New.E, New.F, Old.G, Old.H, Old.I
FROM New INNER JOIN Old ON (New.F = Old.F) AND (New.E = Old.E) AND (New.D = Old.D) AND (New.C = Old.C) AND (New.B = Old.B) AND (New.A = Old.A);

 

[mikeva]

Thanks so much. However, I find this query is causing Access to crash. I get a "cannot open database" error when doing this. When performing this query, my db grows from 1/2 gb to the Access limit of 2gb and then gives me the aforementioned error message. While I think I can get my hands on a sql server to get past the 2gb limit, I'm wondering why this happened. Is is because Access has to load each table once in memory, for each of the 5 inner joins that I have listed?

Once again, thanks so much for your help!

 

[JHB]

By me it is not crashing or other problem.
Instead of linking the fields you can use the below.
I've attached a small example database.

INSERT INTO MergedTableResults ( A, B, C, D, E, F, G, H, I )
SELECT New.A, New.B, New.C, New.D, New.E, New.F, Old.G, Old.H, Old.I
FROM New, Old
WHERE (((New.A)=Old.A) And ((New.B)=Old.B) And ((New.C)=Old.C) And ((New.D)=Old.D) And ((New.E)=Old.E) And ((New.F)=Old.F));

 

[mikeva]

Well, your database is 1/2 Mb in size. Mine is 1/2 Gb. My tables only have to be loaded in memory 3 times before I exceed the 2 Gb limit.
I appreciate the time you've taken to help out.
Thank you!

 

[way2bord]

There's no getting around the 2GB limit.

Try moving the tables into separate databases with table links controlling them to your primary database - which will contain the mergedTable.

 

[JHB]

Well, your database is 1/2 Mb in size. Mine is 1/2 Gb. My tables only have to be loaded in memory 3 times before I exceed the 2 Gb limit.
I appreciate the time you've taken to help out.
Thank you!

I don't think a query could be really much more simple as the last one, so you may have problem all the time you are running a query in that database, haven't you?
I would move the data to a SQL server, (you can download MS-SQL server 2008/2012 for free, so far I remember the data limit is 10GB).