CODEWITHSUNDEEP
sqldatabasems-accessms-access-2010
Wolfish
Wolfish

Reputation: 970

This database is apparently really big?

First of all, I must apologise for the onslaught I've given the Access tag over the past few days. It's nothing like MS-SQL :(

OK, I have a small working table that contains 3,500 records. I'm trying to prune them down to a specific set that needs modification to fit our migration app.

I started with

SELECT tempMainTab.*
FROM tempMainTab INNER JOIN tempCritTab 
ON tempMainTab.[LINK] <> tempCritTab.[LINK]

Which is ok, right? (right?)

But of course, nothing ever works first time:

The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2GB) or there is not enough temporary storage space on the disk to store the query result.

So, I closed the existing window of tempCritTab that I had open, and tried again.

Nope:

Access cannot read the table. The table may be corrupt or may not exist.

(Or something along those lines)

So, I tried the Compact/Repair tool. That appeared to work... kinda.
Now, instead of the expected 1,200 rows, I get 7,125,920 rows!?
Now I've just tried to run the query again. No dice - same error as the first.

Where have I gone wrong?

Upvotes: 0

Views: 223

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

My guess is that you really want:

SELECT tempMainTab.*
FROM tempMainTab LEFT JOIN
     tempCritTab 
     ON tempMainTab.[LINK] = tempCritTab.[LINK]
WHERE tempCritTab.[LINK] IS NULL;

This gets the rows in the main table that are not in the crit table -- reverse the order if you want the logic the other way around.

Your version essentially produces a cartesian product of the two tables.

Upvotes: 5

Related Questions