Need to optimize query...

L

Lou

I have the following table and query (image URL follows) that
accomplishes what I need it to, i.e. return all records but the
preselected ones:  http://www.dslextreme.com/users/madforplaid/Inovant_Table_and_Query.jpg

However, with larger data sets how can I rewrite it in a simpler
manner to optimize the query further?  I have already tried NOT IN and
NOT EXISTS to no avail.

Thanks much,

Paul
Click to expand...

It's possible that the concatenation of [Account],
,
[Activity] and [Cost SUM], using all those intervening hyphens, is
slowing the result sets return.

Try:

SELECT FA_Inovant2.*
from FA_Inovant2
where FA_Inovant2.[Cost SUM] is not null
and not exists
( SELECT 'true'
FROM FA_Inovant2
WHERE [Account] = '731300'
and
= '88107'
and [Activity] = '804392'
and [Cost SUM] = '1430500' )​
 
M

madforplaid

I have the following table and query (image URL follows) that
accomplishes what I need it to, i.e. return all records but the
preselected ones:  http://www.dslextreme.com/users/madforplaid/Inovant_Table_and_Query.jpg
Click to expand...
However, with larger data sets how can I rewrite it in a simpler
manner to optimize the query further?  I have already tried NOT IN and
NOT EXISTS to no avail.
Click to expand...
Thanks much,
Click to expand...
Paul
Click to expand...

It's possible that the concatenation of [Account],
,
[Activity] and [Cost SUM], using all those intervening hyphens, is
slowing the result sets return.

Try:

SELECT FA_Inovant2.*
from FA_Inovant2
where FA_Inovant2.[Cost SUM] is not null
and not exists
( SELECT 'true'
FROM FA_Inovant2
WHERE [Account] = '731300'
and
= '88107'
and [Activity] = '804392'
and [Cost SUM] = '1430500' )​
Click to expand...



Lou,

My concatenation method does work, but after trying your SQL it
returns no records as a similarly written NOT EXISTS query had done
for me.

Any other ideas?

Paul​
 
J

John Spencer

Perhaps this is what you are looking for.

SELECT F.*
FROM FA_Inovant2
WHERE [Cost Sum] is not null
AND Not(Accounts = "731300"
AND Center = "88107"
AND Activity = "804392"
AND [Cost Sum] = 1430500 )

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
M

madforplaid

John,

That worked perfectly (once I adjusted the alias table name 'F');
thanks so very much!

Paul


Perhaps this is what you are looking for.

SELECT F.*
FROM FA_Inovant2
WHERE [Cost Sum] is not null
AND Not(Accounts = "731300"
     AND Center = "88107"
     AND Activity = "804392"
     AND [Cost Sum] = 1430500 )

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...

Lou wrote:
Dec 18, 9:11 am

Newsgroups: microsoft.public.access.queries
From: Lou <[email protected]>
Date: Thu, 18 Dec 2008 09:11:30 -0800 (PST)
Local: Thurs, Dec 18 2008 9:11 am
Subject: Re: Need to optimize query...
Reply | Reply to author | Forward | Print | Individual message | Show
original | Report this message | Find messages by this author
I have the following table and query (image URL follows) that
accomplishes what I need it to, i.e. return all records but the
preselected ones: http://www.dslextreme.com/users/madforplaid/Inovant_Table_and_Query.jpg
Click to expand...
However, with larger data sets how can I rewrite it in a simpler
manner to optimize the query further? I have already tried NOT IN and
NOT EXISTS to no avail.

Thanks much,

Paul
Click to expand...



It's possible that the concatenation of [Account],
,
[Activity] and [Cost SUM], using all those intervening hyphens, is
slowing the result sets return.

Try:


SELECT FA_Inovant2.*
from FA_Inovant2
where FA_Inovant2.[Cost SUM] is not null
and not exists
( SELECT 'true'
FROM FA_Inovant2
WHERE [Account] = '731300'
and
= '88107'
and [Activity] = '804392'
and [Cost SUM] = '1430500' )

On Dec 18, 8:52 am, [email protected] wrote:
I have the following table and query (image URL follows) that
accomplishes what I need it to, i.e. return all records but the
preselected ones: http://www.dslextreme.com/users/madforplaid/Inovant_Table_and_Query.jpg
Click to expand...
However, with larger data sets how can I rewrite it in a simpler
manner to optimize the query further? I have already tried NOT IN and
NOT EXISTS to no avail.
Click to expand...
Thanks much,
Click to expand...


It's possible that the concatenation of [Account],
,
[Activity] and [Cost SUM], using all those intervening hyphens, is
slowing the result sets return.
Try:
Click to expand...


SELECT FA_Inovant2.*
from FA_Inovant2
where FA_Inovant2.[Cost SUM] is not null
and not exists
( SELECT 'true'
FROM FA_Inovant2
WHERE [Account] = '731300'
and
= '88107'
and [Activity] = '804392'
and [Cost SUM] = '1430500' )​
Click to expand...


Lou,

My concatenation method does work, but after trying your SQL it
returns no records as a similarly written NOT EXISTS query had done
for me.

Any other ideas?

Paul​
 
Top