Where string takes forever when using dates only

[Tony Girgenti]

Hello.

I'd like to know how to make this run faster when it uses only dates. I'm
open for any database field format changes, coding changes or query changes.
The tables are linked. TC suggested using "EXISTS" in place of "IN", but i
don't know to modify existing query to use "EXISTS".

I can't be the only one with this kind of a problem. If that were the case,
Access would not be such a popular product.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want:

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT
JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#)"

Yet if i use this string, it takes forever to get a report.

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2-
RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And #01/01/2054#)"

Here is the query that is the Record Source for the report.

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO] ORDER BY TempStatus.TIMESTAMP;

Any help would be greatly appreciated. Answers to problems and questions on
these forums are very much appreciated. Not only answers to my questions,
but to others also. I have learned so much from reading questions and
answers by others.

Thanks,
Tony

 

[Wayne Morgan]

Subqueries in Access can be very slow. I usually find it faster to make the
subquery a separate query. Next, create your main query and instead of the
IN clause, add the former subquery to the query as another table and link
the two queries on that field. This will limit the main query to those
records that exist in the subquery without it being a true subquery.

 

[Tony Girgenti]

Hello Wayne.

Thanks for your reply.

I don't understand any of this. Can you explain this with more detail and
specifics ?

Any help would be greatly appreciated.

Thanks,
Tony

Subqueries in Access can be very slow. I usually find it faster to make
the subquery a separate query. Next, create your main query and instead of
the IN clause, add the former subquery to the query as another table and
link the two queries on that field. This will limit the main query to
those records that exist in the subquery without it being a true subquery.

--
Wayne Morgan
MS Access MVP

Hello.

I'd like to know how to make this run faster when it uses only dates.
I'm open for any database field format changes, coding changes or query
changes. The tables are linked. TC suggested using "EXISTS" in place of
"IN", but i don't know to modify existing query to use "EXISTS".

I can't be the only one with this kind of a problem. If that were the
case, Access would not be such a popular product.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want:

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT
JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#)"

Yet if i use this string, it takes forever to get a report.

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION=
'#2- RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And
#01/01/2054#)"

Here is the query that is the Record Source for the report.

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER, INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION, INSTALL.TIMESTAMP FROM ([PENDING ORDERS] LEFT JOIN
TempStatus ON [PENDING ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL
ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO] ORDER BY TempStatus.TIMESTAMP;

Any help would be greatly appreciated. Answers to problems and questions
on these forums are very much appreciated. Not only answers to my
questions, but to others also. I have learned so much from reading
questions and answers by others.

Thanks,
Tony

 

[Wayne Morgan]

Rereading your original post, I think I have a different idea.

The strWhere you're creating is filtering on data that is in the record
source of the report. You're not pulling from a different table to supply
the values in the IN clause. That being the case, you should be able to
apply the restrictions that you have in the IN clause directly to the record
source and get the same results.

Example:
"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.WORKSTATION= '#2-
RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#"

Since the JOBNO was being limited to JOBNO's that had the last two
restrictions, if you limit the record source (which is from the same table)
to only those records that have the last two restrictions then the only
JOBNO's you get should be the same as when you use the IN clause.

--
Wayne Morgan
MS Access MVP

Hello Wayne.

Thanks for your reply.

I don't understand any of this. Can you explain this with more detail and
specifics ?

Any help would be greatly appreciated.

Thanks,
Tony

Subqueries in Access can be very slow. I usually find it faster to make
the subquery a separate query. Next, create your main query and instead
of the IN clause, add the former subquery to the query as another table
and link the two queries on that field. This will limit the main query to
those records that exist in the subquery without it being a true
subquery.

--
Wayne Morgan
MS Access MVP

Hello.

I'd like to know how to make this run faster when it uses only dates.
I'm open for any database field format changes, coding changes or query
changes. The tables are linked. TC suggested using "EXISTS" in place of
"IN", but i don't know to modify existing query to use "EXISTS".

I can't be the only one with this kind of a problem. If that were the
case, Access would not be such a popular product.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want:

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In (SELECT
JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#)"

Yet if i use this string, it takes forever to get a report.

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION=
'#2- RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And
#01/01/2054#)"

Here is the query that is the Record Source for the report.

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS],
INSTALL.INSTALLER, INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES],
INSTALL.WORKSTATION, INSTALL.TIMESTAMP FROM ([PENDING ORDERS] LEFT JOIN
TempStatus ON [PENDING ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL
ON [PENDING ORDERS].JOBNO=INSTALL.[JOB NO] ORDER BY
TempStatus.TIMESTAMP;

Any help would be greatly appreciated. Answers to problems and
questions on these forums are very much appreciated. Not only answers
to my questions, but to others also. I have learned so much from
reading questions and answers by others.

Thanks,
Tony

 

[Tony Girgenti]

Thanks Wayne. That works beautifully.

It took me a while to figure what you are trying to tell me, but i finally
got it.

As always, thanks to you guys that help out on these forums. Answering
questions and finding solutions. It is very much appreciated.

Tony

Rereading your original post, I think I have a different idea.

The strWhere you're creating is filtering on data that is in the record
source of the report. You're not pulling from a different table to supply
the values in the IN clause. That being the case, you should be able to
apply the restrictions that you have in the IN clause directly to the
record source and get the same results.

Example:
"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.WORKSTATION= '#2-
RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#"

Since the JOBNO was being limited to JOBNO's that had the last two
restrictions, if you limit the record source (which is from the same
table) to only those records that have the last two restrictions then the
only JOBNO's you get should be the same as when you use the IN clause.

--
Wayne Morgan
MS Access MVP

Hello Wayne.

Thanks for your reply.

I don't understand any of this. Can you explain this with more detail
and specifics ?

Any help would be greatly appreciated.

Thanks,
Tony

Subqueries in Access can be very slow. I usually find it faster to make
the subquery a separate query. Next, create your main query and instead
of the IN clause, add the former subquery to the query as another table
and link the two queries on that field. This will limit the main query
to those records that exist in the subquery without it being a true
subquery.

--
Wayne Morgan
MS Access MVP


Hello.

I'd like to know how to make this run faster when it uses only dates.
I'm open for any database field format changes, coding changes or query
changes. The tables are linked. TC suggested using "EXISTS" in place
of "IN", but i don't know to modify existing query to use "EXISTS".

I can't be the only one with this kind of a problem. If that were the
case, Access would not be such a popular product.

When i use the following string in a "DoCmd.OpenReport stDocName,
acViewPreview, , strWhere", it gives me the report i want:

"[COMPANY NAME] = 'A V H INTERIORS 4061' AND TempStatus.JOBNO In
(SELECT JOBNO FROM TempStatus WHERE WORKSTATION= '#2- RECEIVED' AND
[TempStatus].TIMESTAMP Between #01/01/2004# And #1/01/2054#)"

Yet if i use this string, it takes forever to get a report.

" TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION=
'#2- RECEIVED' AND [TempStatus].TIMESTAMP Between #01/01/2004# And
#01/01/2054#)"

Here is the query that is the Record Source for the report.

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING
ORDERS].TARGET, [PENDING ORDERS].[COMPANY NAME], [PENDING
ORDERS].SIDEMARK, [PENDING ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER
OF FABRICS], INSTALL.INSTALLER, INSTALL.[INSTALL DATE],
INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION, INSTALL.TIMESTAMP FROM
([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON [PENDING
ORDERS].JOBNO=INSTALL.[JOB NO] ORDER BY TempStatus.TIMESTAMP;

Any help would be greatly appreciated. Answers to problems and
questions on these forums are very much appreciated. Not only answers
to my questions, but to others also. I have learned so much from
reading questions and answers by others.

Thanks,
Tony