CODEWITHSUNDEEP
sqlms-accessvba
arcotenterprises
arcotenterprises

Reputation: 131

Access - Match Column Range / Complex Join

How do we match columns based on condition of closeness to value. This requires Complex Query / Range Comparison / Multiple Joins conditions. Getting Query size exceeded 2GB error.

Tables : 

InvDetails1 / InvDetails2 / INVDL / ExpectedResult

Field Relation :

InvDetails1.F1 = InDetails2.F3

InvDetails2.F5 = INVDL.F1

INVDL.DLID = ExpectedResult.DLID

ExpectedResult.Total - 1 <  InvDetails1.F6 < ExpectedResult.Total + 1

left(InvDetails1.F21,10) = '2013-03-07'

Return Results where Number of records from ExpectedResult is only 1.

Group by InvDetails1.F1 , count(ExpectedResult.DLID) works.

From this result.

Final Result :

InvDetails1.F1 , InvDetails1.F16 , ExpectedResult.DLID , ExpectedResult.NMR

ExpectedResult - has millions of rows.
InvDetails - few hundred thousands

Upvotes: 1

Views: 172

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

If I was in that situation and was finding that my query was "hitting a wall" at 2GB then one thing I would try would be to create a separate saved Select Query to isolate the InvDetails1 records just for the specific date in question. Then I would use that query instead of the full InvDetails1 table when joining to the other tables.

My reasoning is that the query optimizer may not be able to use your left(InvDetails1.F21,10) = '2013-03-07' condition to exclude InvDetails1 records early in the execution plan, possibly causing the query to grow much larger than it really needs to (internally, while it is being processed). Forcing the date selection to the beginning of the process by putting it in a separate (prerequisite) query may keep the size of the "main" query down to a more feasible size.

Also, if I found myself in the situation where my queries were getting that big I would also keep a watchful eye on the size of my .accdb (or .mdb) file to ensure that it does not get too close to 2GB. I've never had it happen myself, but I've heard that database files that hit the 2GB barrier can result in some nasty errors and be rather "challenging" to recover.

Upvotes: 2

Related Questions