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 :

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!

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" wrote in message
ups.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.

--

Yes, at some point, that would be a good idea!

"keepITcool" wrote in message
ft.com...

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 :

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!