Excel Database Query String Too Long

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
K

Karl Burrows

Hi!

I am trying to update a workbook (2002, 2003 versions) to remove many of the
values from an Access database query. The database is old and contains many
old records, so I am trying to make the workbooks a little faster by not
updating those records. The problem is 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.

Any help would be appreciated! Thanks!
 
wouldn't it be easier to either:
either add a boolean field to the DB named : Archived? or somthing.

or create a table of Ärchived Projects and then create an query like
SELECT Customers.*
FROM Customers LEFT JOIN Archived ON
Customers.CompanyName = Archived.CompanyName
WHERE Archived.CompanyName Is Null;

.... that would make maintenance a whole lot easier than
changing your queries all the time...



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Karl Burrows wrote :
 
Karl said:
I can not get all the excludes in
the field before it runs out of characters. Is there a better way to
Click to expand...
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.
Click to expand...

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 said:
Karl said:
I can not get all the excludes in
the field before it runs out of characters. Is there a better way to
Click to expand...
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.
Click to expand...

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.
Click to expand...
 
Yes, at some point, that would be a good idea!


wouldn't it be easier to either:
either add a boolean field to the DB named : Archived? or somthing.

or create a table of Ärchived Projects and then create an query like
SELECT Customers.*
FROM Customers LEFT JOIN Archived ON
Customers.CompanyName = Archived.CompanyName
WHERE Archived.CompanyName Is Null;

.... that would make maintenance a whole lot easier than
changing your queries all the time...



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Karl Burrows wrote :
 
Back
Top