Query speed that changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2007 and trying to get a query to work well. It picks up a
date from a form but does not do it quickly. If on the other hand I put the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is necessary
to get the query to use the date from the form and run quickly? I am looking
at 2 seconds versus 53 seconds for the query to run. It is accessing an ODBC
database either way. I have tried it as a select or make table query and it
doesn’t make any difference in the time. The Text13 box is unbound and is
formatted as a short date field and shows ‘10/4/2007’. Thank you for any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD, QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING (((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13]) AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
 
The correct syntax for referring to a control on a form is
[Forms]![Form1]![Text13]
not
[Forms].[Form1].[Text13]

Make this change and see if it improves your query speed.
 
Thank you for confirming what I thought was right. I substituted the periods
to get it to run at all. If I use the exclamation point (which I also
thought was proper and is what the build command uses) the query will run
about 3.5 minutes and return nothing as if it was not picking up the date at
all.

Ken Snell (MVP) said:
The correct syntax for referring to a control on a form is
[Forms]![Form1]![Text13]
not
[Forms].[Form1].[Text13]

Make this change and see if it improves your query speed.

--

Ken Snell
<MS ACCESS MVP>



vtj said:
I am using Access 2007 and trying to get a query to work well. It picks up
a
date from a form but does not do it quickly. If on the other hand I put
the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is
necessary
to get the query to use the date from the form and run quickly? I am
looking
at 2 seconds versus 53 seconds for the query to run. It is accessing an
ODBC
database either way. I have tried it as a select or make table query and
it
doesn't make any difference in the time. The Text13 box is unbound and is
formatted as a short date field and shows '10/4/2007'. Thank you for any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT
RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING (((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13])
AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
Click to expand...
Click to expand...
 
You can also try to change the HAVING clause to a WHERE clause (but doing
so, place it after the FROM clause and before the GROUP BY clause):

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT RUN
TEMP]

FROM QWHAP_BAL_SHEET_TRANS

WHERE ACCEPTANCE_DT=[Forms]![Form1]![Text13]) AND
ACCTG_TRANS_CD IN("CR" ,"NF") AND
BAL_SHEET_ACCT_NBR ="AA01"


GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR





also, if

BAL_SHEET_ACCT_NBR

is indexed, place it first, instead of last, in the condition.



Hoping it may help,
Vanderghast, Access MVP



Ken Snell (MVP) said:
The correct syntax for referring to a control on a form is
[Forms]![Form1]![Text13]
not
[Forms].[Form1].[Text13]

Make this change and see if it improves your query speed.

--

Ken Snell
<MS ACCESS MVP>



vtj said:
I am using Access 2007 and trying to get a query to work well. It picks
up a
date from a form but does not do it quickly. If on the other hand I put
the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is
necessary
to get the query to use the date from the form and run quickly? I am
looking
at 2 seconds versus 53 seconds for the query to run. It is accessing an
ODBC
database either way. I have tried it as a select or make table query and
it
doesn't make any difference in the time. The Text13 box is unbound and
is
formatted as a short date field and shows '10/4/2007'. Thank you for any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT
RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING (((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13])
AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
Click to expand...
Click to expand...
 
I'm going to guess that the speed difference is due to a type coercion going
on (or not going on at all...).

Maybe
= Cdate([Forms]![Form1]![Text13])
or
= # & [Forms]![Form1]![Text13] & #

would be faster? I'd be curious to know if that works.

HTH,

vtj said:
Thank you for confirming what I thought was right. I substituted the
periods
to get it to run at all. If I use the exclamation point (which I also
thought was proper and is what the build command uses) the query will run
about 3.5 minutes and return nothing as if it was not picking up the date
at
all.

Ken Snell (MVP) said:
The correct syntax for referring to a control on a form is
[Forms]![Form1]![Text13]
not
[Forms].[Form1].[Text13]

Make this change and see if it improves your query speed.

--

Ken Snell
<MS ACCESS MVP>



vtj said:
I am using Access 2007 and trying to get a query to work well. It picks
up
a
date from a form but does not do it quickly. If on the other hand I
put
the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is
necessary
to get the query to use the date from the form and run quickly? I am
looking
at 2 seconds versus 53 seconds for the query to run. It is accessing
an
ODBC
database either way. I have tried it as a select or make table query
and
it
doesn't make any difference in the time. The Text13 box is unbound and
is
formatted as a short date field and shows '10/4/2007'. Thank you for
any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER
RECEIPT
RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING
(((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13])
AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
Click to expand...
Click to expand...
Click to expand...
 
I would try the following.

First - define the parameter
Second - use a where clause instead of a Having clause
Third use "!' instead of "." as the separators in the parameter

PARAMETERS [Forms]![Form1]![Text13] DateTime;

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT

INTO [PER RECEIPT RUN TEMP]

FROM QWHAP_BAL_SHEET_TRANS

WHERE QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT=[Forms]![Form1]![Text13]) AND
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD="CR" Or
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD="NF") AND
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR="AA01"

GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT
, QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD
, QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks for the information! The changes you suggest get the time down to 51
seconds. Not close to the hard coding of the date but I do like the 'where'
concept verses 'having' and this is a query that will work that way.

Michel Walsh said:
You can also try to change the HAVING clause to a WHERE clause (but doing
so, place it after the FROM clause and before the GROUP BY clause):

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT RUN
TEMP]

FROM QWHAP_BAL_SHEET_TRANS

WHERE ACCEPTANCE_DT=[Forms]![Form1]![Text13]) AND
ACCTG_TRANS_CD IN("CR" ,"NF") AND
BAL_SHEET_ACCT_NBR ="AA01"


GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR





also, if

BAL_SHEET_ACCT_NBR

is indexed, place it first, instead of last, in the condition.



Hoping it may help,
Vanderghast, Access MVP



Ken Snell (MVP) said:
The correct syntax for referring to a control on a form is
[Forms]![Form1]![Text13]
not
[Forms].[Form1].[Text13]

Make this change and see if it improves your query speed.

--

Ken Snell
<MS ACCESS MVP>



vtj said:
I am using Access 2007 and trying to get a query to work well. It picks
up a
date from a form but does not do it quickly. If on the other hand I put
the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is
necessary
to get the query to use the date from the form and run quickly? I am
looking
at 2 seconds versus 53 seconds for the query to run. It is accessing an
ODBC
database either way. I have tried it as a select or make table query and
it
doesn't make any difference in the time. The Text13 box is unbound and
is
formatted as a short date field and shows '10/4/2007'. Thank you for any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT
RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING (((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13])
AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
Click to expand...
Click to expand...
Click to expand...
 
You can probably get even better result with (untested):


SELECT [Forms]![Form1]![Text13],
ACCTG_TRANS_CD,
"AA01",
Sum(TRANS_AMT) AS SumOfTRANS_AMT

INTO [PER RECEIPT RUN TEMP]

FROM QWHAP_BAL_SHEET_TRANS

WHERE ACCEPTANCE_DT=[Forms]![Form1]![Text13] AND
BAL_SHEET_ACCT_NBR ="AA01" AND
ACCTG_TRANS_CD IN("CR" ,"NF")


GROUP BY ACCTG_TRANS_CD



The idea being to remove useless work in the GROUP BY (since there is just
one value for the other fields you were using to group, given the WHERE
clause).



Vanderghast, Access MVP



vtj said:
Thanks for the information! The changes you suggest get the time down to
51
seconds. Not close to the hard coding of the date but I do like the
'where'
concept verses 'having' and this is a query that will work that way.

Michel Walsh said:
You can also try to change the HAVING clause to a WHERE clause (but doing
so, place it after the FROM clause and before the GROUP BY clause):

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT
RUN
TEMP]

FROM QWHAP_BAL_SHEET_TRANS

WHERE ACCEPTANCE_DT=[Forms]![Form1]![Text13]) AND
ACCTG_TRANS_CD IN("CR" ,"NF") AND
BAL_SHEET_ACCT_NBR ="AA01"


GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR





also, if

BAL_SHEET_ACCT_NBR

is indexed, place it first, instead of last, in the condition.



Hoping it may help,
Vanderghast, Access MVP



Ken Snell (MVP) said:
The correct syntax for referring to a control on a form is
[Forms]![Form1]![Text13]
not
[Forms].[Form1].[Text13]

Make this change and see if it improves your query speed.

--

Ken Snell
<MS ACCESS MVP>



I am using Access 2007 and trying to get a query to work well. It
picks
up a
date from a form but does not do it quickly. If on the other hand I
put
the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is
necessary
to get the query to use the date from the form and run quickly? I am
looking
at 2 seconds versus 53 seconds for the query to run. It is accessing
an
ODBC
database either way. I have tried it as a select or make table query
and
it
doesn't make any difference in the time. The Text13 box is unbound
and
is
formatted as a short date field and shows '10/4/2007'. Thank you for
any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER
RECEIPT
RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING
(((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13])
AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
Click to expand...
Click to expand...
Click to expand...
 
Problem solved!!! Using this and Michel Walsh's idea of cutting down the
grouping combined to make it work. THANK YOU!!!

John Spencer said:
I would try the following.

First - define the parameter
Second - use a where clause instead of a Having clause
Third use "!' instead of "." as the separators in the parameter

PARAMETERS [Forms]![Form1]![Text13] DateTime;

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT

INTO [PER RECEIPT RUN TEMP]

FROM QWHAP_BAL_SHEET_TRANS

WHERE QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT=[Forms]![Form1]![Text13]) AND
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD="CR" Or
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD="NF") AND
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR="AA01"

GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT
, QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD
, QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

vtj said:
I am using Access 2007 and trying to get a query to work well. It picks up
a
date from a form but does not do it quickly. If on the other hand I put
the
date (#10/4/2007#) in the criteria field instead of the
=[Forms].[Form1].[Text13], the query works fantastically. What is
necessary
to get the query to use the date from the form and run quickly? I am
looking
at 2 seconds versus 53 seconds for the query to run. It is accessing an
ODBC
database either way. I have tried it as a select or make table query and
it
doesn't make any difference in the time. The Text13 box is unbound and is
formatted as a short date field and shows '10/4/2007'. Thank you for any
help!!

SELECT QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR,
Sum(QWHAP_BAL_SHEET_TRANS.TRANS_AMT) AS SumOfTRANS_AMT INTO [PER RECEIPT
RUN
TEMP]
FROM QWHAP_BAL_SHEET_TRANS
GROUP BY QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT,
QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD,
QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR
HAVING (((QWHAP_BAL_SHEET_TRANS.ACCEPTANCE_DT)=[Forms].[Form1].[Text13])
AND
((QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="CR" Or
(QWHAP_BAL_SHEET_TRANS.ACCTG_TRANS_CD)="NF") AND
((QWHAP_BAL_SHEET_TRANS.BAL_SHEET_ACCT_NBR)="AA01"));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do you pass parameters between queries in Microso 2
Update query 3
dynamically choose columns in query 1
Query Criteria from Form Problem 7
How do you ignore entire tables in an Inner Join Query? 13
Parameterizing in a Cross Tab Query 2
Date query parameters entered from form not working 1
Changing reference cell in query 2

Back
Top