SIMPLE QUERY IS HANGING UP ????? 1

[PrgrmsAll]

I am running what would seem to me as a relatively simple query. But Access hangs up (showing two-squares on the progress bar) and sits there. I have left it all night. No change. Here is the SQL:

SELECT RHMF.ID_1, MEMBER.ID_2 INTO RHMF_Out
FROM RHMF LEFT JOIN MEMBER ON trim(RHMF.ID_1) = trim(MEMBER.ID_1);

RHMF table has 125,000 records
MEMBER table has 250,000 records

Is the 'trim' causing heavy overhead?

Any advice would be greatly appreciated. Thanks in advance.

 

[dhookom]

What happens if you try this without the Trim()? I'm not sure why you need to use Trim() if these are Access tables since Access generally doesn't store trailing spaces. You might also see some improvement if the join fields are indexed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PrgrmsAll]

actually, that seems to have worked (the indexing and removing the trim). i import the RHMF table from a flat ascii file, do you know, if there is a way, in code, to programmatically set a field to Indexed (Duplicates OK)?

 

[dhookom]

I would probably link to the ascii file and append to an existing table that had indexes. I would also trim() in the append query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]