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
G McGBusiness Analyst / Access developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Try to change subquery to append query and use temporary table (with postcode ID as primary key) for storing results of your query (clear it before requery). Now you will be able to use join with this table in main query. Temporary tables are the usual way to solve problem of huge queries. May be better to use different queries for search with and without distance.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G McGBusiness Analyst / Access developerAuthor Commented:
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
als315Commented:
Usually freese means too many positions in subqueries. With temp table you will be able to debug issue. Check also your indexes
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

G McGBusiness Analyst / Access developerAuthor Commented:
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
als315Commented:
You can open lock file (it is text file) and there will be users. Deleting will not help you, because file system will not allow you to open file exclusively.
If you have frontend and backend, temporary table should be in frontend.
May be there is something wrong in criteria and join could resolve it. Subquery could run in all records and it also could be the reason for freezing
G McGBusiness Analyst / Access developerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.