asked on

Query subform freezing

Hi

I am having an issue with an MS Access db I have designed.

Apologises for areas where I am not specific but its because the DB is frozen along with my TeamViewer connection.

So I have a subform based on an SQL query with around 10 where clauses. The user is searching for candidates in a recruitment firm. There is a text or combo boxes for each criteria in the where clauses. The problem arises when they tried to search using a postcode and distance. When not using postcode it runs no problem. There is an or isnull at the end of each where clause to satisfy if a field is blank

I have all UK postcodes in a table along with their latitude and longitudes. I then have a sub query that runs the latitudes of longitudes of each postcode through a formula called the great circle formula comparing them to the postcode supplied in search criteria. The output is distance in miles and the query filters for any distances less then the radius supplied by the user.

I ran this subquery and it seems to run quick enough, maybe 5 -10 secs.

Within the main query my where clause say ... AND (Candidates.Postcode in (SELECT QryPostcodesMatch.Candidates.Postcode
FROM QryPostcodesMatch)or [Forms]![Search]![cboPostcode] Is Null )

The database is freezing everytime I run the query and it is making it very hard to test to see what is wrong. Can you see any areas just from this I could improve this query.
There are about 6000 candidates in the DB but not all have postcodes. Before it froze I got 1500 returned in the sub query pretty quick so this bit doesn't seem to bad.

It was about 6 months ago I wrote this but I think I did try a join but I needed a check box to editable and it would not allow this with a join so I did not proceed with this approach
They can select the checkboxes to select which candidate to send a text message too.

I have included screenshots of the subquery and some of the form that controls the query from an older version but this doesn't work great on my machine so the data is not displayed.

Please see queries below.

Main query (QrySearch)

SELECT Candidates.Check, Candidates.ID, Candidates.First_Name, Candidates.Surname, Candidates.Mobile_No, Candidates.Address, Candidates.Town, Candidates.County, Candidates.Postcode, Candidates.Trade, Candidates.Certificates, Candidates.Available, Candidates.Consultant, Candidates.Date_Reg, Candidates.CSCS_Expiry, Candidates.Type, Candidates.Email
FROM Candidates
WHERE (Candidates.Type = [Forms]![Search]![cboType] OR [Forms]![Search]![cboType] Is Null)
AND (Candidates.First_Name Like [Forms]![Search]![txtFirstName] & "*" OR [Forms]![Search]![txtFirstName] Is Null)
AND (Candidates.Surname Like "*" & [Forms]![Search]![txtLastName] or [Forms]![Search]![txtLastName] is Null)
AND (Candidates.Town =[Forms]![Search]![cboTown] or [Forms]![Search]![cboTown] is Null)
AND (Candidates.County =[Forms]![Search]![cboCounty] or [Forms]![Search]![cboCounty] Is Null)
AND (Candidates.Postcode in (SELECT QryPostcodesMatch.Candidates.Postcode
FROM QryPostcodesMatch)or [Forms]![Search]![cboPostcode] Is Null )
AND (Candidates.Available =[Forms]![Search]![chkAvailable] or Candidates.Available = True)
AND (Candidates.Trade Like "*" & [Forms]![Search]![cboTrade] & "*" or [Forms]![Search]![cboTrade] is Null)
AND (Candidates.Certificates Like "*" & [Forms]![Search]![cboCertificates] & "*" OR [Forms]![Search]![cboCertificates] Is Null)
AND (Candidates.Consultant =[Forms]![Search]![cboConsultant] or [Forms]![Search]![cboConsultant] is Null)
AND (Candidates.Date_Reg >=[Forms]![Search]![txtDate_Reg] or [Forms]![Search]![txtDate_Reg] is Null);

Sub query (QryPostcodesMatch)

SELECT DISTINCT Candidates.Postcode, PC1.latitude, PC1.longitude, PC2.postcode, PC2.latitude, PC2.longitude, GreatCircleDistance([PC1].[latitude],[PC1].[longitude],[PC2].[latitude],[PC2].[longitude],True,True) AS Distance
FROM QrySearchPC AS PC2, Candidates INNER JOIN Ukpostcodes AS PC1 ON Candidates.Postcode = PC1.postcode
WHERE (((PC2.postcode)=Forms!Search!cboPostcode) And ((GreatCircleDistance(PC1.latitude,PC1.longitude,PC2.latitude,PC2.longitude,True,True))<Forms!Search!TxtRadius));

any help would be greatly appreciated

Regards
Gerard
Gerard
Qrypostcodematch.PNG
Search-form.PNG

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G McG

ASKER

Hi als315

Ok i will give this a go this evening when i am back at my PC.

From what you are saying I take it that its better to join to a temporary table rather than the query?

Just for my understanding and incase this is not the issue. When i tried to copy and paste the query object so i could make amendments and test the DB froze also, does copying run the query some way or couod you think if something else would cause this?

Regards
Gerard

als315

Usually freese means too many positions in subqueries. With temp table you will be able to debug issue. Check also your indexes

G McG

ASKER

Sorry not sure what you mean by positions?

Yeah I think you may be right on the indexes, I am planning on adding more indexes.
Problem being tonight that some peoples DBs are left open and I am unable to make changes, this may be a silly question, but is there any harm in deleting a lock file and if not will this allow me to edit the backend if I remove?

Going Back to the original question. Your solution was to use a temporary table. I have multiple users, would I need a temp table for each user or would the query hold the info in memory even if someone else ran the query and cleared the table?

The subquery seems to run fine, the problem seems to be with the main query when the postcode is not null and the where clause is used. Would you recommend a temp table for both queries?

Regards
Gerard

SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G McG

ASKER

Hi als315

Thanks for all your help on this matter. I was able to append the results to a temporary table and then replace the query name in the subquery with the table name. Still a slow enough process but I added a counter show the user can see the progress of the query.

Gerard