Not sure I understand the WHERE clause.
All phrases have
tblJobs.Confirmed <"no"
It will therefore be more effient to break that out and use:
WHERE (tblJobs.Confirmed <"no")
AND ((tblJobs.SetCall Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall])
OR (tblJobs.fldRTSCall Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall])
OR ((tblJobs.SetCall < [Forms]![frmJobs]![SetCall])
AND (tblJobs.fldRTSCall [Forms]![frmJobs]![fldRTSCall])))
What I don't understand is that the criteria accepts SetCall between the 2
values in the form, or beyond those values. The effect of that would be to
accept all non-null values for this field? Could you use:
WHERE (tblJobs.Confirmed <"no")
AND ((tblJobs.SetCall Is Not Null)
OR (tblJobs.fldRTSCall Between [Forms]![frmJobs]![SetCall]
And [Forms]![frmJobs]![fldRTSCall]))
Other suggestions:
- Make sure you have indexes on Confirmed, SetCall, fldRTSCall, and [Item
Number].
- Make sure you have a relation with Referential Integrity between the 2
tables (so the foreign key is indexed.)
- Any chance of using a yes/no field instead of a Text field for Confirmed?
- Unless SetCall and fldRTSCall are Text fields, declare the parameters.
Choose Parameters in the Query menu, and enter 2 rows into the dialog for:
[Forms]![frmJobs]![SetCall]
[Forms]![frmJobs]![fldRTSCall]
Alternatively, concatenate the values into the string, and execute it.
If it is still slow, see Tony Toews' Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<pm********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
>I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.
The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.
INSERT INTO tmpRaItemsInUseTotals ( [Item Number], SumOfQuantity )
SELECT DISTINCTROW tblJobsLineItems.[Item Number],
Sum(tblJobsLineItems.Quantity) AS SumOfQuantity
FROM tblJobs INNER JOIN tblJobsLineItems ON tblJobs.Job_Num =
tblJobsLineItems.Job_Num
WHERE (((tblJobs.Confirmed)<>"no") AND ((tblJobs.SetCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND ((tblJobs.fldRTSCall) Between
[Forms]![frmJobs]![SetCall] And [Forms]![frmJobs]![fldRTSCall])) OR
(((tblJobs.Confirmed)<>"no") AND
((tblJobs.SetCall)<[Forms]![frmJobs]![SetCall]) AND
((tblJobs.fldRTSCall)>[Forms]![frmJobs]![fldRTSCall]))
GROUP BY tblJobsLineItems.[Item Number];
Any ideas?