Form taking two minutes to load

[jason_nevin]

I have a "search form" which uses combo boxes to select records and display them in a list box at the bottom of the form. There are five combo boxes all accessing one field from a table using a query. The list box is based on a cross table query of the five tables and uses the values in the combo boxes in an OR statement to ascertain which records are displayed.

My front-end resides on my local drive and the back-end on a networked drive. I have indexed the relevant fields in the queries.

The tables being accessed have approx 4,000 , 300, 1500, 100 and 2000 records. The cross table query produces 5,500 rows.

This form takes two minutes to load. Can anyone suggest a way of improving this?

 

[Pat Hartman]

Do you mean CrossTab query? Or, are you really doing a cross table join? A cross join would create a huge recordset if you joined those 5 tables. A cross join would produce 4,000 * 300 * 1500 * 100 * 2000 = a HUGE number of records. Post your query. It is the likely culpret.

 

[jason_nevin]

SELECT tblCustomerEnquiry.EnquiryNumber, tblSiteDetails.SiteName, tblContact.CompanyName, tblSurvey.ContractNumber, tblBulkID.ContractNumber, tblProject.ContractNumber, tblCustomerEnquiry.ContactID, tblCustomerEnquiry.DateReceived, tblCustomerEnquiry.ContactSourceID, tblCustomerEnquiry.Quoted, tblCustomerEnquiry.QuoteDate, tblCustomerEnquiry.QuoteValue, tblCustomerEnquiry.CustomerRequirements, tblCustomerEnquiry.DateLastChased, tblCustomerEnquiry.[TICK IF WON], tblCustomerEnquiry.[AMS MANAGEMENT], tblCustomerEnquiry.[AMS EUROPE]
FROM ((((tblContact RIGHT JOIN tblCustomerEnquiry ON tblContact.ContactID = tblCustomerEnquiry.ContactID) INNER JOIN tblSiteDetails ON tblCustomerEnquiry.EnquiryNumber = tblSiteDetails.CustomerEnquiryID) LEFT JOIN tblBulkID ON tblSiteDetails.SiteID = tblBulkID.SiteID) LEFT JOIN tblProject ON tblSiteDetails.SiteID = tblProject.SiteID) LEFT JOIN tblSurvey ON tblSiteDetails.SiteID = tblSurvey.SiteID
WHERE (((tblSiteDetails.SiteName)=[Forms]![frmEnquirySearch]![SiteNameCombo])) OR (((tblSurvey.ContractNumber)=[Forms]![frmEnquirySearch]![SurveyContractNumberCombo])) OR (((tblBulkID.ContractNumber)=[Forms]![frmEnquirySearch]![BulkIDContractNumberCombo])) OR (((tblProject.ContractNumber)=[Forms]![frmEnquirySearch]![ProjectContractNumberCombo])) OR (((tblCustomerEnquiry.EnquiryNumber)=[Forms]![frmEnquirySearch]![EnquiryNumberCombo])) OR (((tblCustomerEnquiry.ContactID)=[Forms]![frmEnquirySearch]![CompanyNameCombo]));


Thanks Pat.

 

[Pat Hartman]

You could try filtering the recordsets before the join. To do this, create a separate query for each table that has selection criteria. Then join the filtered queries rather than the tables. Also, make sure that you have indexes defined on the fields used in the selection criteria.