Query issues due to a bad relationship!

[ajetrumpet]

Yes! I would love to have your email so i can send the file...

sent to you via a private message here

[ajetrumpet]

the subquery I told you to write takes too long. I waited a minute for the thing to run on my laptop and stopped it because I got sick of waiting. For ''IN'' subqueries, you have to wait for the engine to perform <tableCount1> * <tableCount2> tasks before you get the result. So you would be asking for 68934 * 115641 = 7971596694 tasks to complete. Yeah, that's too long.

So the alternative, which is always better than a subquery anyway, is to write an unequal Join. In your case, this will be fine:

SELECT DISTINCT [Inhouse Imagery].Pathrow
FROM [Inhouse Imagery] LEFT JOIN 
[EDC Archive] ON [EDC Archive].Pathrow = [Inhouse Imagery].Pathrow

As a last note, use the compact and repair feature in Access. It's not practical to send someone a 500MB file when it's real size is only 10% of that. Gmail's servers probably weren't too happy with you either.

That should be enough for you to mark this thread as solved, my dear.

[annemrosenberg]

Hi Adam,
Sorry for the delay, I've been away and without a computer with access (thought it had it) Anyway, I was able to run the sql, and the result is a single column of pathrows. I'm assuming that these are all the values that are a match between the two tables. Am I correct? If so, this was amazing and I can't believe you were able to get it to work! Is there a way to show the accompanying columns from only one of the tables? For example, for all the matches, I need the columns labeled cloud code and cloud cover from the EDC archive table.

Does this make any sense? :-)

Thanks so much!

Anne

[annemrosenberg]

Hi Adam,

Sorry for the email and the posting. I wanted to make sure you saw it.

Let me know when you have a chance to answer the question about the one to many relationship.

Anne