Query freezing/crashing access

[DanWallace]

I have the following query:

SELECT NPAP001.C_CUSTOMER, NPAP001.C_ACCOUNT, NPAP001.C_PLANTYPE 
FROM NPAP001 LEFT JOIN NBIF951 ON NPAP001.[C_PLANTYPE] = NBIF951.[C_PLANTYPE] 
WHERE (((NPAP001.C_PLANTYPE) Is Not Null And Not (NPAP001.C_PLANTYPE)=" ") AND ((NBIF951.C_PLANTYPE) Is Null));

And every time I run it, the progress bar hits about 75% or so and then just stops. I've left it for upwards of 30 minutes to no avail.

npap001 has about 10,000 records and nbif951 has about 700,000 records. They are both linked tables from another access database.

I tried using the query wizard to make an "unmatched" query and that query does the same thing.

I'm confused. Could my tables be corrupt? I can open both tables and go to the last record.

 

[David Eagar]

I would suggest that as these are both big files & that they are linked rather than in the database might be the main cause of your problem.

 

[DanWallace]

I would suggest that as these are both big files & that they are linked rather than in the database might be the main cause of your problem.

You think? I just started working here and linked data tables is how they do it here.

 

[David Eagar]

Linked tables is usually the best way to go (split db's) however I have found, especially working over a network, it does run slower and especially with such a large amount of data

 

[DanWallace]

Linked tables is usually the best way to go (split db's) however I have found, especially working over a network, it does run slower and especially with such a large amount of data

Hmm. Well makes sense I guess. Something tells me the problem might be something else here though. I just tested it on another database with the same tables and it (eventually) returned results.

 

[DanWallace]

Try removing the first two conditions in the where clause. If you really have records in NPAP001 where PlanType is null or ZLS, create a query that selects rows with not null and not zls values and then join that query to the other table to get your unmatched results. keep in mind that null values from table a will not join with null values from table b but ZLS' will join to each other.

ZLS?

The thing is I can't have any queries attached to the project. It needs to be portable between databases without loading in queries and extra tables and all that.