Excel Database Query String Too Long

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.

--

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.
>
> --
>