I think using LEFT(Subdivision,3) NOT IN will work perfectly!

Thanks!

"Jamie Collins" <jamiecollins@xsmail.com> wrote in message
news:1106734476.750116.73770@z14g2000cwz.googlegroups.com...
>
> Karl Burrows wrote:
>> I can not get all the excludes in
>> the field before it runs out of characters. Is there a better way to
>
>> exclude records? Here is the SQL query:
>>
>> <>'BLH ~ Blakeney Heath' And <>'BRK ~ Brookmere' And <>'LDP ~ Lake
> Davidson
>> Park' And <>'MDW ~ Meadowmont at Highland Creek' And <>'WGR ~ Withers
> Grove'
>> And <>'WGV ~ Withers Grove V' And <>'GLB ~ Glyndebourne'
>>
>> I need to add several more and will be adding as the years progress.
> I am
>> working on getting them to "archive" older projects, so we don't have
> to
>> exclude so many.
>
> Using the SQL IN keyword uses less characters:
>
> client_name NOT IN ('BLH ~ Blakeney Heath','BRK ~ Brookmere','LDP ~
> Lake Davidson
> Park','MDW ~ Meadowmont at Highland Creek',...)
>
> However, AFAIK this will not prevent your query from become 'too
> complex' when it gets to the parser because the statements are
> logically equivalent.
>
> Does your data have a more efficient key than this column (VARCHAR(50)
> is it?) If not, are the first three characters unique e.g. could you
> use:
>
> LEFT(client_name, 3) NOT IN ('BLH','BRK','LDP','MDW', ...)
>
> Whatever you key, it would be more efficient to maintain a list in a
> ToBeArchived table and use this in a JOIN e.g.
>
> SELECT T1.client_name
> FROM MyTable AS T1
> LEFT JOIN ToBeArchived AS T2
> ON T1.client_name = T2.client_name
> WHERE T2.client_name IS NULL;
>
> If preferred, you may even be able to maintain this table in Excel and
> create the JOIN across databases e.g.
>
> SELECT T1.client_name
> FROM
> [MS Access;Database=C:\MyJetDB.mdb;].MyTable AS T1
> LEFT JOIN
> [Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].[ToBeArchived$] AS
> T2
> ON T1.client_name = T2.client_name
> WHERE T2.client_name IS NULL;
>
> Jamie.
>
> --
>