There's got to be a more efficient way of running this??

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I want to activate a filter on my data from a form command button.
The filter will search date fields in several separate tables where
the primary key matches to that in my current data (that the form is
based on). These many separate date fields are compared to yet
another date field that is in yet another table. If any one of them
is greater than the one being compared to, then this is type data I
want to see, the rest filtered out.

I put together a query with many DLookup's, and then launch the query
using DoCmd.ApplyFilter on my forms command button. It is working,
but takes about 8 minutes to run against 6000 records (to come up with
about 6 when the filter is done). This is with everything on my local
machine. When in place, it will be pulling the data over T1's.

Here's my Query that is working: (queQuotations is the Query that my
main form is based on):

((((DLookUp("[NCCommentsUpdate]","tblNewConstruction","[Quote #] = '"
& [queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[NCStrengthUpdate]","tblNewConstruction","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[ESCommentsUpdate]","tblEngineering","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[ESStrengthUpdate]","tblEngineering","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[EUCommentsUpdate]","tblEndUser","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[EUStrengthUpdate]","tblEndUser","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))))


Thanks for any help!!
 
Tim said:
I want to activate a filter on my data from a form command button.
The filter will search date fields in several separate tables where
the primary key matches to that in my current data (that the form is
based on). These many separate date fields are compared to yet
another date field that is in yet another table. If any one of them
is greater than the one being compared to, then this is type data I
want to see, the rest filtered out.

I put together a query with many DLookup's, and then launch the query
using DoCmd.ApplyFilter on my forms command button. It is working,
but takes about 8 minutes to run against 6000 records (to come up with
about 6 when the filter is done). This is with everything on my local
machine. When in place, it will be pulling the data over T1's.

Here's my Query that is working: (queQuotations is the Query that my
main form is based on):

((((DLookUp("[NCCommentsUpdate]","tblNewConstruction","[Quote #] = '"
& [queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[NCStrengthUpdate]","tblNewConstruction","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[ESCommentsUpdate]","tblEngineering","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[ESStrengthUpdate]","tblEngineering","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[EUCommentsUpdate]","tblEndUser","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[EUStrengthUpdate]","tblEndUser","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))))
Click to expand...

Number one is that Domain Functions (like DLookup) are not well suited to
be used in queries or loops due to the overhead associated with them.

Secondly if you are thinking of running an Access app over a T1 you should
stop and rethink that. It will be unusably slow and will almost certainly
give you lots of corruption issues. The only way to consider using
Access/Jet over a WAN connection is with Terminal Server or similar remote
control software so that the actual processing is not run over the wire.

Remember that a T1 is many many times slower than even a really crappy LAN.
This is obviously compounded by the fact that you are starting out with a
process that is too slow when run completely locally.
 
Hi,

I'm not sure this will perform what you want without some changes but ..

I believe that you'll get better results by creating separate queries for
each of the dates you want to compare against

tblNewConstruction.NCCommentsUpdate,
tblNewConstruction.NCStrengthUpdate,
tblEngineering,ESCommentsUpdate,
tblEngineering.ESStrengthUpdate,
tblEndUser.EUCommentsUpdate,
tblEndUser.EUStrengthUpdate

with the output field names matching.
You can then use a union query as the record source for your form.
Your filter value would then simply be ([Date}>[User Date]) AND ([Quote #] =
[Query Quote #])

this is very probably not exactly what you need .. but may help.


MFK.


Tim said:
I want to activate a filter on my data from a form command button.
The filter will search date fields in several separate tables where
the primary key matches to that in my current data (that the form is
based on). These many separate date fields are compared to yet
another date field that is in yet another table. If any one of them
is greater than the one being compared to, then this is type data I
want to see, the rest filtered out.

I put together a query with many DLookup's, and then launch the query
using DoCmd.ApplyFilter on my forms command button. It is working,
but takes about 8 minutes to run against 6000 records (to come up with
about 6 when the filter is done). This is with everything on my local
machine. When in place, it will be pulling the data over T1's.

Here's my Query that is working: (queQuotations is the Query that my
main form is based on):

((((DLookUp("[NCCommentsUpdate]","tblNewConstruction","[Quote #] = '"
& [queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[NCStrengthUpdate]","tblNewConstruction","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[ESCommentsUpdate]","tblEngineering","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[ESStrengthUpdate]","tblEngineering","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[EUCommentsUpdate]","tblEndUser","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))) OR
(((DLookUp("[EUStrengthUpdate]","tblEndUser","[Quote #] = '" &
[queQuotations].[Quote #] &
"'"))>DLookUp("[UserDate]","tblUserViewed","[Quote #] = '" &
[queQuotations].[Quote #] & "'"))))


Thanks for any help!!
Click to expand...
 
Back
Top