Slow query

[PayeDoc]

Hello All

I have a query based on two tables that takes over 3 minutes to run. There
are 3 lines of criteria. One of the tables has 120,000 records and the other
has 6,500 records. All the fields for which criteria have been specified are
indexed (duplicates OK - except obviously the key field in the join).

I cannot understand why it is so slow. I am running A97 under
W2Kprofessional on an AMD Sempron with 1GB ram.

The sql of the query is:

SELECT [x confirmed].practice, staffs.name, [x confirmed]![Tax code no] & [x
confirmed]![Tax code ltr] & [x confirmed]![mth 1 basis] AS Expr1, [x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x confirmed]![mth
1 basis] AS [prev tax code], [staffs]![Tax code no] & [staffs]![Tax code
ltr] & [staffs]![mth 1 basis] AS [new tax code], [x confirmed]![hourly rate]
AS [prev hourly rate], [staffs]![hourly rate] AS [new hourly rate], [x
confirmed]![ni code] AS [prev NI code], [staffs]![ni code] AS [new NI code]
FROM staffs INNER JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]) AND (([x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x confirmed]![mth
1 basis])<>[staffs]![Tax code no] & [staffs]![Tax code ltr] & [staffs]![mth
1 basis]) AND (([x confirmed].[month name])=[Forms]![frm x main]![prev
month])) OR ((([x confirmed].practice)=[Forms]![frm x main]![prac name]) AND
(([x confirmed]![hourly rate])<>[staffs]![hourly rate]) AND (([x
confirmed].[month name])=[Forms]![frm x main]![prev month])) OR ((([x
confirmed].practice)=[Forms]![frm x main]![prac name]) AND (([x
confirmed]![ni code])<>[staffs]![ni code]) AND (([x confirmed].[month
name])=[Forms]![frm x main]![prev month]));

Hope someone can help - this is driving me nuts!

Many thanks
Les
BTW
I realise that it was a bad idea to use the fieldname "name" in the tables
[staffs] and [x confirmed], but this predates me and we seem to have got
away with it - so far!

 

[Gary Walter]

"PayeDoc"wrote:

I have a query based on two tables that takes over 3 minutes to run. There
are 3 lines of criteria. One of the tables has 120,000 records and the
other
has 6,500 records. All the fields for which criteria have been specified
are
indexed (duplicates OK - except obviously the key field in the join).

I cannot understand why it is so slow. I am running A97 under
W2Kprofessional on an AMD Sempron with 1GB ram.

The sql of the query is:

SELECT [x confirmed].practice, staffs.name, [x confirmed]![Tax code no] &
[x
confirmed]![Tax code ltr] & [x confirmed]![mth 1 basis] AS Expr1, [x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x
confirmed]![mth
1 basis] AS [prev tax code], [staffs]![Tax code no] & [staffs]![Tax code
ltr] & [staffs]![mth 1 basis] AS [new tax code], [x confirmed]![hourly
rate]
AS [prev hourly rate], [staffs]![hourly rate] AS [new hourly rate], [x
confirmed]![ni code] AS [prev NI code], [staffs]![ni code] AS [new NI
code]
FROM staffs INNER JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].practice)=[Forms]![frm x main]![prac name]) AND
(([x
confirmed]![Tax code no] & [x confirmed]![Tax code ltr] & [x
confirmed]![mth
1 basis])<>[staffs]![Tax code no] & [staffs]![Tax code ltr] &
[staffs]![mth
1 basis]) AND (([x confirmed].[month name])=[Forms]![frm x main]![prev
month])) OR ((([x confirmed].practice)=[Forms]![frm x main]![prac name])
AND
(([x confirmed]![hourly rate])<>[staffs]![hourly rate]) AND (([x
confirmed].[month name])=[Forms]![frm x main]![prev month])) OR ((([x
confirmed].practice)=[Forms]![frm x main]![prac name]) AND (([x
confirmed]![ni code])<>[staffs]![ni code]) AND (([x confirmed].[month
name])=[Forms]![frm x main]![prev month]));

Hope someone can help - this is driving me nuts!

Many thanks
Les
BTW
I realise that it was a bad idea to use the fieldname "name" in the tables
[staffs] and [x confirmed], but this predates me and we seem to have got
away with it - so far!

Hi Les,

I gave your tables aliases so I could read things easier..
(I think the following is equivalent to your query)

SELECT
X.practice,
S.[name],
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS [prev tax code],
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS [new tax code],
X.[hourly rate] AS [prev hourly rate],
S.[hourly rate] AS [new hourly rate],
X.[ni code] AS [prev NI code],
S.[ni code] AS [new NI code]
FROM
staffs AS S
INNER JOIN
[x confirmed] AS X
ON
S.name = X.[name]
WHERE
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis])
<>
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

I don't believe your concatenation can use indexing
which "might" be what is slowing the query down,
i.e., the following piece of your WHERE clause:

AND
(X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis])
<>
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis])
AND

which, *if none of these fields can be NULL*,
maybe could be replaced with:

AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND

Also (doubtful that this would be the cause),
did you try the query replacing form parameters
with actual values to see if ran faster?

If it did improve run time, it may be worth it to
declare the type for the form parameters in a
PARAMETERS clause.

So...try:

PARAMETERS
Forms![frm x main]![prac name] Text,
Forms![frm x main]![prev month] Text;
SELECT
X.practice,
S.[name],
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS [prev tax code],
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS [new tax code],
X.[hourly rate] AS [prev hourly rate],
S.[hourly rate] AS [new hourly rate],
X.[ni code] AS [prev NI code],
S.[ni code] AS [new NI code]
FROM
staffs AS S
INNER JOIN
[x confirmed] AS X
ON
S.name = X.[name]
WHERE
(
(X.practice = [Forms]![frm x main]![prac name])

AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

good luck,

gary

 

[Gary Walter]

In fact, why don't we "cure" need for brackets
while we are at it (in case need to use further)?

PARAMETERS
Forms![frm x main]![prac name] Text,
Forms![frm x main]![prev month] Text;
SELECT
X.practice,
S.[name] AS StaffName,
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
X.[hourly rate] AS PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
X.[ni code] AS PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
[x confirmed] AS X
ON
S.[name] = X.[name]
WHERE
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

 

[Leslie Isaacs]

Gary

Many thanks for your reply.

Sorry to appear dim, but should I be able simply to copy and paste the code
you have given? I'm not sure how the aliases would work if I do this, and
also I'm not sure what you mean by "cure" need for brackets.

I'm not in the office now until Monday, so will not be able to try your
suggestions until then, but I will post back when I have had the chance.

Thanks again
Les

 

[Gary Walter]

Hi Leslie,

I'm not perfect so I could have done something wrong, but...

I think you should be able to start a new query,
go to the SQL Window,
and paste the final SQL that I gave you,
and test it.

I wrote it out "nested" so I could follow the logic,
but Access will not have any problem reading the
nesting (nor the table aliasing)
and will eventually "run it all together" again.

In fact, if you save it, my guess is that Access will probably
"rewrite" the WHERE clause.

Why?

It looks to me like you have 3 major "blocks" separated by ORs.

WHERE

Block 1
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)

end Block1
-----------

OR

Block 2
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)

end Block 2
------------

OR

Block 3
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

end Block 3
------------

In each one of those blocks you are doing the
same equitest on practice and [month name].

So...there's no reason why those 2 equitests
cannot exist "outside of the blocks" only one time.
My guess is Access will think that is more efficient
and take it upon itself to rewrite it like:

WHERE
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
AND
(
(X.[ni code] <> S.[ni code])
OR
(X.[hourly rate] <> S.[hourly rate])
OR
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
);

Do you see what I mean? Now you have 3 "blocks"
separated by AND's, and only the final block has the
3 OR "subblocks."

Does the logic of the above WHERE clause agree with
how you wanted to filter your records?

If X.practice and X.[month name] are indexed, the
query parsing will probably *start out* throwing away
a mess of records that you don't care about!

That is good (faster) -- right?

I hope that helps (and that I have not misunderstood)...

good luck,

gary

Gary

Many thanks for your reply.

Sorry to appear dim, but should I be able simply to copy and paste the
code you have given? I'm not sure how the aliases would work if I do this,
and also I'm not sure what you mean by "cure" need for brackets.

I'm not in the office now until Monday, so will not be able to try your
suggestions until then, but I will post back when I have had the chance.

Thanks again
Les

In fact, why don't we "cure" need for brackets
while we are at it (in case need to use further)?

PARAMETERS
Forms![frm x main]![prac name] Text,
Forms![frm x main]![prev month] Text;
SELECT
X.practice,
S.[name] AS StaffName,
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
X.[hourly rate] AS PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
X.[ni code] AS PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
[x confirmed] AS X
ON
S.[name] = X.[name]
WHERE
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

 

[Leslie Isaacs]

Hello Gary

Thanks for your further help with this.

I have pasted the code you gave into a new query ... but unfortunately that
took pretty much the same time to run as my earlier version!

The 3 'blocks' that you referred to no doubt correspond to the 3 lines of
criteria that I had entered in the query's design view (as opposed to sql
view): I notice that when I paste your code into the sql view of a new query
and then go to design view it all looks very like the design view of my
original query!

Is there anything else I can try?

Thanks again for your help.
Les

Hi Leslie,

I'm not perfect so I could have done something wrong, but...

I think you should be able to start a new query,
go to the SQL Window,
and paste the final SQL that I gave you,
and test it.

I wrote it out "nested" so I could follow the logic,
but Access will not have any problem reading the
nesting (nor the table aliasing)
and will eventually "run it all together" again.

In fact, if you save it, my guess is that Access will probably
"rewrite" the WHERE clause.

Why?

It looks to me like you have 3 major "blocks" separated by ORs.

WHERE

Block 1
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)

end Block1
-----------

OR

Block 2
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)

end Block 2
------------

OR

Block 3
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

end Block 3
------------

In each one of those blocks you are doing the
same equitest on practice and [month name].

So...there's no reason why those 2 equitests
cannot exist "outside of the blocks" only one time.
My guess is Access will think that is more efficient
and take it upon itself to rewrite it like:

WHERE
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
AND
(
(X.[ni code] <> S.[ni code])
OR
(X.[hourly rate] <> S.[hourly rate])
OR
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
);

Do you see what I mean? Now you have 3 "blocks"
separated by AND's, and only the final block has the
3 OR "subblocks."

Does the logic of the above WHERE clause agree with
how you wanted to filter your records?

If X.practice and X.[month name] are indexed, the
query parsing will probably *start out* throwing away
a mess of records that you don't care about!

That is good (faster) -- right?

I hope that helps (and that I have not misunderstood)...

good luck,

gary

Gary

Many thanks for your reply.

Sorry to appear dim, but should I be able simply to copy and paste the
code you have given? I'm not sure how the aliases would work if I do
this, and also I'm not sure what you mean by "cure" need for brackets.

I'm not in the office now until Monday, so will not be able to try your
suggestions until then, but I will post back when I have had the chance.

Thanks again
Les

In fact, why don't we "cure" need for brackets
while we are at it (in case need to use further)?

PARAMETERS
Forms![frm x main]![prac name] Text,
Forms![frm x main]![prev month] Text;
SELECT
X.practice,
S.[name] AS StaffName,
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
X.[hourly rate] AS PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
X.[ni code] AS PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
[x confirmed] AS X
ON
S.[name] = X.[name]
WHERE
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

 

[Gary Walter]

Hi Leslie,

First, did you try replacing WHERE clause in SQL View with:

WHERE
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
AND
(
(X.[ni code] <> S.[ni code])
OR
(X.[hourly rate] <> S.[hourly rate])
OR
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
);

don't save it, just try running it...

else...divide and conquer...

which table is the big table?

Is [x confirmed] the big table?

create a preliminary query (say "qryPreLim")

PARAMETERS
Forms![frm x main]![prac name] Text,
Forms![frm x main]![prev month] Text;
SELECT
X.[name] As ConfName,
X.practice,
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS PrevTaxCode,
X.[hourly rate] AS PrevHourlyRate,
X.[ni code] AS PrevNIcode
FROM
[x confirmed] AS X
WHERE
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[month name] = [Forms]![frm x main]![prev month]);

how fast does this run? I believe you said [practise]
and [month name] are indexed? (as well as [name]?)

how many records does this return? (I cannot see your data)

next...join "qryPreLim" to other table (initially w/o criteria)
again, I believe you said staffs.[name] is indexed?


SELECT
Q.practice,
S.[name] AS StaffName,
Q.PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
Q.PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
Q.PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
qryPreLim AS Q
ON
S.[name] = Q.ConfName;

how fast does this run?

how many records does this return?

If the performance of this query is suitable,
then start adding "one by one" your criteria.

for example, start with (your [ni code]'s are indexed?)

SELECT
Q.practice,
S.[name] AS StaffName,
Q.PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
Q.PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
Q.PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
qryPreLim AS Q
ON
S.[name] = Q.ConfName
WHERE
(S.[ni code] <> Q.[ni code]);

then try (your [hourly rate]'s are indexed?)

SELECT
Q.practice,
S.[name] AS StaffName,
Q.PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
Q.PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
Q.PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
qryPreLim AS Q
ON
S.[name] = Q.ConfName
WHERE
(S.[hourly rate] <> Q.[hourly rate]);


then try

SELECT
Q.practice,
S.[name] AS StaffName,
Q.PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
Q.PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
Q.PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
qryPreLim AS Q
ON
S.[name] = Q.ConfName
WHERE
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
);

Do either of the 3 queries above run demonstrably slower?

If its the third one, then try each one separately...

I guess we'll go from there (what you report back).

good luck,

gary

Thanks for your further help with this.

I have pasted the code you gave into a new query ... but unfortunately
that took pretty much the same time to run as my earlier version!

The 3 'blocks' that you referred to no doubt correspond to the 3 lines of
criteria that I had entered in the query's design view (as opposed to sql
view): I notice that when I paste your code into the sql view of a new
query and then go to design view it all looks very like the design view of
my original query!

Is there anything else I can try?

Thanks again for your help.
Les

Hi Leslie,

I'm not perfect so I could have done something wrong, but...

I think you should be able to start a new query,
go to the SQL Window,
and paste the final SQL that I gave you,
and test it.

I wrote it out "nested" so I could follow the logic,
but Access will not have any problem reading the
nesting (nor the table aliasing)
and will eventually "run it all together" again.

In fact, if you save it, my guess is that Access will probably
"rewrite" the WHERE clause.

Why?

It looks to me like you have 3 major "blocks" separated by ORs.

WHERE

Block 1
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)

end Block1
-----------

OR

Block 2
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)

end Block 2
------------

OR

Block 3
--------
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

end Block 3
------------

In each one of those blocks you are doing the
same equitest on practice and [month name].

So...there's no reason why those 2 equitests
cannot exist "outside of the blocks" only one time.
My guess is Access will think that is more efficient
and take it upon itself to rewrite it like:

WHERE
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
AND
(
(X.[ni code] <> S.[ni code])
OR
(X.[hourly rate] <> S.[hourly rate])
OR
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
);

Do you see what I mean? Now you have 3 "blocks"
separated by AND's, and only the final block has the
3 OR "subblocks."

Does the logic of the above WHERE clause agree with
how you wanted to filter your records?

If X.practice and X.[month name] are indexed, the
query parsing will probably *start out* throwing away
a mess of records that you don't care about!

That is good (faster) -- right?

I hope that helps (and that I have not misunderstood)...

good luck,

gary

Gary

Many thanks for your reply.

Sorry to appear dim, but should I be able simply to copy and paste the
code you have given? I'm not sure how the aliases would work if I do
this, and also I'm not sure what you mean by "cure" need for brackets.

I'm not in the office now until Monday, so will not be able to try your
suggestions until then, but I will post back when I have had the chance.

Thanks again
Les

In fact, why don't we "cure" need for brackets
while we are at it (in case need to use further)?

PARAMETERS
Forms![frm x main]![prac name] Text,
Forms![frm x main]![prev month] Text;
SELECT
X.practice,
S.[name] AS StaffName,
X.[Tax code no] & X.[Tax code ltr] & X.[mth 1 basis] AS PrevTaxCode,
S.[Tax code no] & S.[Tax code ltr] & S.[mth 1 basis] AS NewTaxCode,
X.[hourly rate] AS PrevHourlyRate,
S.[hourly rate] AS NewHourlyRate,
X.[ni code] AS PrevNIcode,
S.[ni code] AS NewNIcode
FROM
staffs AS S
INNER JOIN
[x confirmed] AS X
ON
S.[name] = X.[name]
WHERE
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(
(X.[Tax code no] <> S.[Tax code no])
AND
(X.[Tax code ltr] <> S.[Tax code ltr])
AND
(X.[mth 1 basis] <> S.[mth 1 basis])
)
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[hourly rate] <> S.[hourly rate])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
)
OR
(
(X.practice = [Forms]![frm x main]![prac name])
AND
(X.[ni code] <> S.[ni code])
AND
(X.[month name] = [Forms]![frm x main]![prev month])
);

 

[Gary Walter]

I also forgot to get context of query...

version of Access = 97

where are these tables?

are both in a local/remote mdb?

if either remote, across LAN or WAN, or on same hard drive?

I assume from initial post that neither is in SQL Server (or Oracle)?

Are any of the "join" fields type MEMO?

I always forget to ask this stuff...

thanks,

gary

 

[Leslie Isaacs]

Gary

I really appreciate your helping me with this.
My problem now is that I won't be able to try anying further until Wednesday
(UK time), but will report back then.

Thanks again
Les

 

[Gary Walter]

I also forgot to mention that when
you first paste in some SQL, the first
time you run it Access will take some
time creating a "plan."

so...at least test a second time...


"Leslie Isaacs"wrote: